I've simplified the test case to the following:
CREATE TABLE foo (
id integer NOT NULL,
value integer NOT NULL
);
INSERT INTO foo (id, value)
SELECT random() * 1000, random() * 1000
FROM generate_series(1, 100000);
CREATE INDEX foo_id_idx ON foo (id);
CREATE INDEX foo_value_idx ON foo (value);
VACUUM ANALYZE foo;
EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Sort (cost=186.46..186.71 rows=99 width=8) (actual time=0.101..0.101 rows=0
loops=1)
Sort Key: value
-> Index Scan using foo_id_idx on foo (cost=0.00..183.18 rows=99 width=8)
(actual time=0.067..0.067 rows=0 loops=1)
Index Cond: (id = -1)
Total runtime: 0.259 ms
(5 rows)
EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..25.79 rows=1 width=8) (actual time=631.964..631.964 rows=0
loops=1)
-> Index Scan using foo_value_idx on foo (cost=0.00..2552.75 rows=99
width=8) (actual time=631.942..631.942 rows=0 loops=1)
Filter: (id = -1)
Total runtime: 632.135 ms
(4 rows)
Maybe I don't understand something about what EXPLAIN is showing,
but why does Limit have an estimated cost of 0.00..25.79 when the
thing it's limiting has a cost of 0.00..2552.75? Is that the cost
of just the limit operation? Is it supposed to be the cumulative
cost of everything up to that point? Is the planner preferring
this plan because of the 25.79 cost?
A workaround appears to be:
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM foo WHERE id = -1 ORDER BY value)
AS s LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=186.46..186.48 rows=1 width=8) (actual time=0.124..0.124 rows=0
loops=1)
-> Subquery Scan s (cost=186.46..187.70 rows=99 width=8) (actual
time=0.110..0.110 rows=0 loops=1)
-> Sort (cost=186.46..186.71 rows=99 width=8) (actual
time=0.099..0.099 rows=0 loops=1)
Sort Key: value
-> Index Scan using foo_id_idx on foo (cost=0.00..183.18
rows=99 width=8) (actual time=0.064..0.064 rows=0 loops=1)
Index Cond: (id = -1)
Total runtime: 0.313 ms
(7 rows)
I see that the Limit in this query has an estimated cost of
186.46..186.48, so I'm still wondering why the Limit in the previous
query had a cost of 0.00..25.79. Is that my ignorance about how
the planner works, or is it a bug?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
