Sfrgttk

Does capillary rise violate hydrostatic paradox?

1 John in Luther’s Bibel

Are there any specific minhagim to celebrate Purim as a family?

What is the tangent at a sharp point on a curve?

Why is "la Gestapo" feminine?

Highest stage count that are used one right after the other?

Why does the frost depth increase when the surface temperature warms up?

Relations between homogeneous polynomials

Did I make a mistake by ccing email to boss to others?

Why didn’t Eve recognize the little cockroach as a living organism?

How do you say "Trust your struggle." in French?

How to evaluate the research level of a paper before any publication?

C++ lambda syntax

What (if any) is the reason to buy in small local stores?

What is this high flying aircraft over Pennsylvania?

Has the laser at Magurele, Romania reached the tenth of the Sun power?

Independent drivetrains on tandem bicycle

How do I lift the insulation blower into the attic?

Comic-book: Kids find a dead female superhero in the woods

Can a Knock spell open the door to Mordenkainen's Magnificent Mansion?

Put the phone down / Put down the phone

What is it called when someone votes for an option that's not their first choice?

Extract substring according to regexp with sed or grep

Started in 1987 vs. Starting in 1987

Query top and specific ratings for table in postgresql

Optimizing table and choosing storage engineVery slow simple JOIN querySELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order (“desc”)How to index two tables for JOINed query optimisationPostgreSQL 9.5 query performance depends on JOINed column in SELECT clausePostgres 9.5 Materialized View from a table with jsonb columnsHow to get aggregate data from a dynamic number of related rows in adjacent tableCreating materialized view is very slowPostgreSQL Materialized View not refreshingOptimizing query performance with aggregation tables and partitioning

0

Say, for example, we have players with their current level and timestamp when this level was got. Table looks like:

CREATE TABLE tst_ratings (

    player varchar PRIMARY KEY, 

    level integer, 

    last_updated timestamp

);

Now we want to get top 100 ratings:

SELECT * 

FROM tst_ratings 

ORDER BY level desc, last_updated asc 

LIMIT 100;

To get rating for player with level 164 that was get in '2018-02-25 02:40:04.028757':

SELECT count(*) 

FROM tst_ratings 

WHERE level > 164 or (level = 164 and last_updated < '2018-02-25 02:40:04.028757';

All seems fine but when we have many players (> million), select queries start to consume more time and ofc number of this queries also increased.

We can add multicolumn Indexes, but as this table updates when player up their level, we lose performance on update queries...

We tried to create materialized view to solve this problem like this:

CREATE MATERIALIZED VIEW v_tst_ratings AS 

SELECT player, level, last_updated, ROW_NUMBER() OVER(ORDER BY level desc, last_updated asc) AS rnk 

FROM tst_ratings;



CREATE UNIQUE INDEX v_tst_ratings_idx ON v_tst_ratings(player);

And now for top 100 we can just use:

SELECT player, rnk 

FROM  v_tst_ratings 

LIMIT 100;

And for rating of concret player:

SELECT rnk 

FROM v_tst_ratings 

WHERE player = 'some_id';

For 3 million rows it is take 1.5 minute to refresh this materialized view, but select queries much faster than via table tst_ratings. It is ok as we don`t need to have "live" ratings and delay < hour is ok.

But are there any suggestion/best practices to this situation that can be even more faster and accurate?

postgresql optimization top

share

asked 3 mins ago

Alexandr RebrikAlexandr Rebrik

1

New contributor

Alexandr Rebrik is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

Say, for example, we have players with their current level and timestamp when this level was got. Table looks like:

CREATE TABLE tst_ratings (

    player varchar PRIMARY KEY, 

    level integer, 

    last_updated timestamp

);

Now we want to get top 100 ratings:

SELECT * 

FROM tst_ratings 

ORDER BY level desc, last_updated asc 

LIMIT 100;

To get rating for player with level 164 that was get in '2018-02-25 02:40:04.028757':

SELECT count(*) 

FROM tst_ratings 

WHERE level > 164 or (level = 164 and last_updated < '2018-02-25 02:40:04.028757';

All seems fine but when we have many players (> million), select queries start to consume more time and ofc number of this queries also increased.

We can add multicolumn Indexes, but as this table updates when player up their level, we lose performance on update queries...

We tried to create materialized view to solve this problem like this:

CREATE MATERIALIZED VIEW v_tst_ratings AS 

SELECT player, level, last_updated, ROW_NUMBER() OVER(ORDER BY level desc, last_updated asc) AS rnk 

FROM tst_ratings;



CREATE UNIQUE INDEX v_tst_ratings_idx ON v_tst_ratings(player);

And now for top 100 we can just use:

SELECT player, rnk 

FROM  v_tst_ratings 

LIMIT 100;

And for rating of concret player:

SELECT rnk 

FROM v_tst_ratings 

WHERE player = 'some_id';

For 3 million rows it is take 1.5 minute to refresh this materialized view, but select queries much faster than via table tst_ratings. It is ok as we don`t need to have "live" ratings and delay < hour is ok.

But are there any suggestion/best practices to this situation that can be even more faster and accurate?

postgresql optimization top

share

asked 3 mins ago

Alexandr RebrikAlexandr Rebrik

1

New contributor

Alexandr Rebrik is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Say, for example, we have players with their current level and timestamp when this level was got. Table looks like:

CREATE TABLE tst_ratings (

    player varchar PRIMARY KEY, 

    level integer, 

    last_updated timestamp

);

Now we want to get top 100 ratings:

SELECT * 

FROM tst_ratings 

ORDER BY level desc, last_updated asc 

LIMIT 100;

To get rating for player with level 164 that was get in '2018-02-25 02:40:04.028757':

SELECT count(*) 

FROM tst_ratings 

WHERE level > 164 or (level = 164 and last_updated < '2018-02-25 02:40:04.028757';

All seems fine but when we have many players (> million), select queries start to consume more time and ofc number of this queries also increased.

We can add multicolumn Indexes, but as this table updates when player up their level, we lose performance on update queries...

We tried to create materialized view to solve this problem like this:

CREATE MATERIALIZED VIEW v_tst_ratings AS 

SELECT player, level, last_updated, ROW_NUMBER() OVER(ORDER BY level desc, last_updated asc) AS rnk 

FROM tst_ratings;



CREATE UNIQUE INDEX v_tst_ratings_idx ON v_tst_ratings(player);

And now for top 100 we can just use:

SELECT player, rnk 

FROM  v_tst_ratings 

LIMIT 100;

And for rating of concret player:

SELECT rnk 

FROM v_tst_ratings 

WHERE player = 'some_id';

For 3 million rows it is take 1.5 minute to refresh this materialized view, but select queries much faster than via table tst_ratings. It is ok as we don`t need to have "live" ratings and delay < hour is ok.

But are there any suggestion/best practices to this situation that can be even more faster and accurate?

postgresql optimization top

share

asked 3 mins ago

Alexandr RebrikAlexandr Rebrik

1

New contributor

Alexandr Rebrik is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

CREATE TABLE tst_ratings (

    player varchar PRIMARY KEY, 

    level integer, 

    last_updated timestamp

);

SELECT * 

FROM tst_ratings 

ORDER BY level desc, last_updated asc 

LIMIT 100;

SELECT count(*) 

FROM tst_ratings 

WHERE level > 164 or (level = 164 and last_updated < '2018-02-25 02:40:04.028757';

CREATE MATERIALIZED VIEW v_tst_ratings AS 

SELECT player, level, last_updated, ROW_NUMBER() OVER(ORDER BY level desc, last_updated asc) AS rnk 

FROM tst_ratings;



CREATE UNIQUE INDEX v_tst_ratings_idx ON v_tst_ratings(player);

SELECT player, rnk 

FROM  v_tst_ratings 

LIMIT 100;

SELECT rnk 

FROM v_tst_ratings 

WHERE player = 'some_id';

share

asked 3 mins ago

Alexandr RebrikAlexandr Rebrik

1

New contributor

Alexandr Rebrik is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 3 mins ago

Alexandr RebrikAlexandr Rebrik

1

New contributor

Alexandr Rebrik is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

Alexandr RebrikAlexandr Rebrik

1

New contributor

Alexandr Rebrik is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

Alexandr RebrikAlexandr Rebrik

1