Postgresql – Does PostgreSQL consider how evenly data is distributed on disk when choosing a seq scan

postgresql

I have a table items with an indexed column type_id. The table contains about 80 million rows. At the moment the first 5 million (in order of insertion) and the last 3 million have type_id = 8, and all others have type_id = 12.

I run a query with where type_id = 8 limit 10. Sometimes it takes a fraction of a second, and sometimes it takes minutes. Here is explain output:

inventory=# explain select id from items where type_id = 8 limit 10;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5.07 rows=10 width=118)
   ->  Seq Scan on items  (cost=0.00..4014534.00 rows=7915149 width=118)
         Filter: (type_id = 8)
(3 rows)



inventory=# explain select id from items where type_id = 8;
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on items  (cost=182418.97..3251192.33 rows=7915149 width=118)
   Recheck Cond: (type_id = 8)
   ->  Bitmap Index Scan on items_db97e9bb  (cost=0.00..180440.18 rows=7915149 width=0)
         Index Cond: (type_id = 8)
(4 rows)

Looking at the answers to other questions here, I conclude that the query planner chooses a seq scan because of the low limit, because it expects to find 10 matches from the first ~100 records it scans. But in my case, the matching records are not evenly spread on disk – they are at the very beginning and at the very end of the table. Does the query planner take that into account?

Edit: here is my postgresql.conf (only listing changed values):

max_connections = 2000
shared_buffers = 3GB
effective_cache_size = 9GB
min_wal_size = 10GB
max_wal_size = 10GB
checkpoint_completion_target = 0.9
default_statistics_target = 10000
work_mem = 100MB
maintenance_work_mem = 2GB

The server has 16GB memory, uses Amazon EBS "General Purpose SSD" storage, and this database is the only resource-intensive thing it runs.

Edit2: I have just run analyze verbose items;, and the query planner still chooses the same plans.

Best Answer

In short, No.

One of the statistics which PostgreSQL collects is the correlation between the values of a column (actually the ranks of those values) versus the location of that row in the table. You can find that result in pg_stats.correlation for your specific tablename and colname.

However, the particular query you give is not one of the situations where this information is used. The primary use is to estimate how much of the IO done for the table-lookup step of a large index range scan (e.g. "WHERE state='Ca'", or "WHERE age is between 50 and 65") will be sequential-like IO (either really sequential, or close enough to sequential so that read-ahead still triggers, or falling into the same table-block as the previous row so requiring no IO at all) rather than random IO.

Also, since your relation is not monotonic but rather more like a parabola, the estimated correlation will undervalue the strength of the correlation in your case, as it only looks at linear correlations.

Related Question