Incorrect indexed query performance in PostgreSQL

Question

Need help regarding query performance in PostgreSQL. This seems to apply to indices.

This request:

• Filters according to type
• Orders timestamp , ascending:

SELECT * FROM the_table WHERE type = 'some_type' ORDER BY timestamp LIMIT 20

Indices:

  CREATE INDEX the_table_timestamp_index ON the_table(timestamp); CREATE INDEX the_table_type_index ON the_table(type); 

type field values ​​are just one of about 11 different lines.
The problem is that the query seems to run in O (log n) time, but only takes a few milliseconds more, except for some type values ​​that run in a few minutes.

In these sample queries, only a few milliseconds are first executed, and the second takes more than 30 minutes:

 SELECT * FROM the_table WHERE type = 'goq' ORDER BY timestamp LIMIT 20 SELECT * FROM the_table WHERE type = 'csp' ORDER BY timestamp LIMIT 20 

I suspect, with approximately 90% certainty, that our indices are incorrect. I think, after reading this similar question about index performance , that most likely we need a composite index, over type and timestamp .

The query plans I completed are here:

• Expected performance, type index (i.e. new index with type = 'csq' in the WHERE ).
• The slowest, problematic case, indexes, as described above.
• A quick case, the same indices as above.

Many thanks for your help! Any pointers would be really appreciated!

Answer 1

Indexes can be used either for the where clause or for the order by clause. With the index on thetable(type, timestamp) , the same index can be used for both.

I assume Postgres decides which index to use based on the statistics it collects. When it uses an index for where and then tries to sort, you get very poor performance.

This is just an assumption, but it's worth creating the index above to see if this fixes performance issues.

Answer 2

All explanation outputs use a timestamp index. This is probably due to the fact that the power of the type column is too small, so scanning the index in this column is as expensive as scanning the table.

The composite index that must be created must be:

 create index comp_index on the_table ("timestamp", type) 

In that order.