5 ms·

One thing I struggle with when figuring out the best indexe is creating enough junk in the database to force it to use an index vs seq scan. Anyone have tips o

One thing I struggle with when figuring out the best indexe is creating enough junk in the database to force it to use an index vs seq scan. Anyone have tips on good data generation tools that works with foreign keys and creates a good range of data? I've looked into using queries and generate_series but it doesn't work well with UUID keys (I think?) Right now I have a bash script that creates a bunch of entries through my app API...

solresol 5y ago
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"
- barrkel 5y ago
  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.
  - bingo-bongo 5y ago
    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.
jandrewrogers 5y ago
A sequential scan can be faster than an indexed search in many cases. There is a good reason optimizers often choose to do sequential scans even when an index is available. Remember, the index is displacing data in the cache. Even for data that is purely in memory, sequential scans are often friendly for CPU performance. On modern server hardware you usually need megabytes of data in a table before sequential scans start to become suboptimal. Most database engines are designed under this assumption.

solresol 5y ago

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"

barrkel 5y ago
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.
- bingo-bongo 5y ago
  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.

jandrewrogers 5y ago

A sequential scan can be faster than an indexed search in many cases. There is a good reason optimizers often choose to do sequential scans even when an index is available. Remember, the index is displacing data in the cache. Even for data that is purely in memory, sequential scans are often friendly for CPU performance. On modern server hardware you usually need megabytes of data in a table before sequential scans start to become suboptimal. Most database engines are designed under this assumption.