Skip to content
Advertisement

PostgreSQL get all records with most recent date

I have this query that will run on very large data sets and it is too slow.

table A looks something like this

pk columnA columnB columnC
1 5/6/2022 1234 1
2 5/6/2022 1234 2
3 5/5/2022 0000 3
4 5/3/2022 0000 4

There are about 1000 distinct entries in columnB and many orders of magnitude more in the table. Is there a better way to structure the query? Or columns I can add to the table that will make it faster?

Advertisement

Answer

I suspect that it will be the last line which is taking the most time because the list must be parsed.

It would be better to put these values in a table with an index (PRIMARY KEY) so that the query only consults the index.

We can also create indexes on columns A and B.
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=6223777b7cbfa986d1eb852ac08aeaaf

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement