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
orVACUUM 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
.