Query Optimizer makes a poor choice

Hi,

We’ve got a strange situation where two queries get dramatically
different performance because of how the Query Optimizer handles LIMIT.

# explain analyze select * from cards where card_set_id=2850 order by
card_id;
QUERY
PLAN
------------------------------------------------------------------------
----------------------------------------------------------------------
Sort (cost=86686.36..86755.40 rows=27616 width=40) (actual
time=22.504..22.852 rows=5000 loops=1)
Sort Key: card_id
Sort Method: quicksort Memory: 583kB
-> Bitmap Heap Scan on cards (cost=755.41..84649.24 rows=27616
width=40) (actual time=0.416..1.051 rows=5000 loops=1)
Recheck Cond: (card_set_id = 2850)
-> Bitmap Index Scan on cards_card_set_id_indx
(cost=0.00..748.50 rows=27616 width=0) (actual time=0.399..0.399
rows=5000 loops=1)
Index Cond: (card_set_id = 2850)
Total runtime: 23.233 ms
(8 rows)

# explain analyze select * from cards where card_set_id=2850 order by
card_id limit 1;
QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
Limit (cost=0.00..105.19 rows=1 width=40) (actual
time=6026.947..6026.948 rows=1 loops=1)
-> Index Scan using cards_pkey on cards (cost=0.00..2904875.38
rows=27616 width=40) (actual time=6026.945..6026.945 rows=1 loops=1)
Filter: (card_set_id = 2850)
Total runtime: 6026.985 ms
(4 rows)

The only way we’ve found to get around the use of the PK index in the
second query is by invalidating it -- sorting it on a cast version of
the PK. This doesn’t work terribly well with our dataset. Is there a
better way around this?

Tyler Hains

IT Director

ProfitPoint, Inc.

www.profitpointinc.com