Postgres query optimization (forcing an index scan)

by

For testing purposes you can force the use of the index by “disabling” sequential scans – best in your current session only:

SET enable_seqscan = OFF;

Do not use this on a productive server. Details in the manual here.

I quoted “disabling”, because you cannot actually disable sequential table scans. But any other available option is now preferable for Postgres. This will prove that the multicolumn index on (metric_id, t) can be used – just not as effective as an index on the leading column.

You probably get better results by switching the order of columns in your PRIMARY KEY (and the index used to implement it behind the curtains with it) to (t, metric_id). Or create an additional index with reversed columns like that.

You do not normally have to force better query plans by manual intervention. If setting enable_seqscan = OFF leads to a much better plan, something is probably not right in your database. Consider this related answer:

More Related Contents:

  1. Optimize Postgres query on timestamp range
  2. PostgreSQL LIKE query performance variations
  3. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  4. Multicolumn index on 3 fields with heterogenous data types
  5. How to exit from PostgreSQL command line utility: psql
  6. Optimize GROUP BY query to retrieve latest row per user
  7. Generating time series between two dates in PostgreSQL
  8. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  9. Why does PostgreSQL perform sequential scan on indexed column?
  10. I forgot the password I entered during postgres installation
  11. Do nullable columns occupy additional space in PostgreSQL?
  12. Running PostgreSQL in memory only
  13. Generate series of dates – using date type as input
  14. PostgreSQL query very slow with limit 1
  15. Does a Postgres UNIQUE constraint imply an index?
  16. Difference between GiST and GIN index
  17. Test for null in function with varying parameters
  18. Dynamically generate columns for crosstab in PostgreSQL
  19. I forgot the password I entered during PostgreSQL installation
  20. PostgreSQL ERROR: canceling statement due to conflict with recovery
  21. Change type of varchar field to integer: “cannot be cast automatically to type integer”
  22. Postgresql insert trigger to set value
  23. How to add column if not exists on PostgreSQL?
  24. Configuration parameter work_mem in PostgreSQL on Linux
  25. How to add a conditional unique index on PostgreSQL
  26. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  27. now() default values are all showing same timestamp
  28. Convert escaped Unicode character back to actual character in PostgreSQL
  29. PostgreSQL composite primary key
  30. SQL Sub queries in check constraint

Leave a Comment