Postgres Indexes for Newbies

Re:Postgres Indexes for Newbies by solresol on Wednesday January 19, 2022 @07:45PM (#30002705)

If there's an index and the query planner is choosing sequential scan, it's doing so because it thinks that the random access pattern for the indexed data is going to be slower than the sequential access for the whole dataset. It's almost definitely right.

So why generate the data? It will sort itself out when there's enough real data in there.

You can also: - disable "enable_seqscan" - increase "seq_page_cost" - decrease "random_page_cost"

[ Parent | on HN ]

Re:Postgres Indexes for Newbies by barrkel on Wednesday January 19, 2022 @09:04PM (#30003397)

IME almost every query that takes way longer than expected in PG has been because it elected to do a sequential scan instead of using an index. I've set both random_page_cost and seq_page_cost to 1 for a database on an SSD (where sequential is still faster than random) but PG still chooses sequential scans. I selectively disable sequential scans on the connection ahead of badly planned queries now.

[ Parent | on HN ]

Re:Postgres Indexes for Newbies by bingo-bongo on Thursday January 20, 2022 @03:07AM (#30006130)

If your data changes a lot, try REINDEX and VACUUM ANALYZE on the tables involved and then test and check if the query planner still wants to do seq scan.

We have a couple of rather large tables with a lot of changes, where is’s needed regularly to ensure the query planner makes the best possible plans.

[ Parent | on HN ]