4 ms·
> This one is just weird, but I've seen (and was never able to figure out why): The LIMIT has me suspicious it has to do with the "correlation" statistic - I k
by Izkata 5y ago
> This one is just weird, but I've seen (and was never able to figure out why):
The LIMIT has me suspicious it has to do with the "correlation" statistic - I know it applies when ORDER BY is involved, but dunno about the IN.
This statistic exists for every column in a table, and measures the correlation between the order of the column's data and the table's order on disk. If the correlation is "bad" and you're getting most/all of the table's data, then the query planner will do a full table scan and sort, to avoid lots of random access on disk. If instead the correlation is "good", it'll do an index scan because it won't have to do much jumping around to different parts of the disk.
CLUSTER can change the table data order on disk to match one of the indexes on the table. It would have to be run regularly though, since there's no way to insert new rows in the middle, and it locks the table for its whole runtime.