Skip to main content
1 of 6
Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620

The row size is much bigger for your second try: width=157 vs. width=46. Postgres will even more readily use an index for wider rows. Possible reasons for the unexpected sequential scan include:

  • You have substantially fewer rows in your tables for the second test as indicated by planner estimates: rows=143 vs. rows=357. It does not pay to look up an index for only few rows to sort.

  • Or statistics are outdated leading to misguided planner estimates (Postgres only thinks there would be fewer rows).

  • The index size may have been bloated as a side-effect of rewriting the tables. REINDEX or VACUUM FULL would repair that.

Run VACUUM FULL ANALYZE on all involved tables and try again - with the same number of rows in all tables. You should see bitmap index scans again. If the phenomenon persists, provide the output of EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN.

Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620