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.