=?UTF-8?B?VG9yc3RlbiBGw7ZydHNjaA==?= <torsten.foertsch@xxxxxxx> writes: > Using the index: > Limit (cost=0.57..2.95 rows=1 width=0) > (actual time=0.095..0.095 rows=1 loops=1) > -> Index Scan ... (cost=0.57..14857285.83 rows=6240539 width=0) > (actual time=0.095..0.095 rows=1 loops=1) > Index Cond:... > Filter: ... > Rows Removed by Filter: 4 > Total runtime: 0.147 ms > seq scan: > Limit (cost=0.00..1.12 rows=1 width=0) > (actual time=0.943..0.944 rows=1 loops=1) > -> Seq Scan ... (cost=0.00..6967622.77 rows=6240580 width=0) > (actual time=0.940..0.940 rows=1 loops=1) > Filter: ... > Rows Removed by Filter: 215 > Total runtime: 0.997 ms So the real question here is whether 215 rows skipped to find the first matching row is more or less than the statistical expectation. You said the table has 80M rows, so the planner evidently thinks the filter has selectivity 6240580/80M or about 1/13, so it would have been expecting the scan to find a match after about 13 rows on average. Having to scan 215 rows is thus considerably more than it was guessing. If we had statistics that would allow a better guess at where the first matching row is, then indeed this would be a planner bug --- but it's not a bug, it's just a limitation of the available statistical data. What's more interesting is that the index scan only had to skip 4 rows to get a match. Is it getting unduly lucky rather than unduly unlucky? There have been some discussions of intentionally penalizing the estimates for small-LIMIT plans, so that we assume worse-than-random placement of the first few matching rows. That would kick up the estimate for this seqscan all right, but I'm unsure that it wouldn't kick up the estimate for the indexscan just as much. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
