Re: Query Optimizer makes a poor choice

2011/11/29 Tyler Hains <thains(at)profitpointinc(dot)com>:
> # 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

do you have autovacum enabled?

does the plan change when you push stats target for this column?
ALTER TABLE cards ALTER card_set_id SET STATISTICS 500;
ANALYZE cards ( card_set_id );

what happens when you do:
select * from ( select * from cards where card_set_id=2850 ) order by
card_id limit 1
?
------------------------------------------------------------------------
--

Yes, I'm pretty sure autovacuum is enabled. Changing the query as shown
there uses the sub-optimal index.

I haven't had a chance to experiment with the SET STATISTICS, but that
got me going on something interesting...

Do these statistics look right?

# SELECT attname, n_distinct, most_common_vals, histogram_bounds FROM
pg_stats WHERE tablename = 'cards';

"initial_set_sequence" 31224
"{291,169,334,380,488,599,1752,2293,12584,4}"
"{5,806,2485,5394,9106,14071,18566,26521,41407,89905,534617}"
"initial_card_set_id" 901
"{5201,3203,3169,5679,5143,5204,5231,5655,4322,5236}"
"{4,3079,3896,4349,4677,5149,5445,5707,6003,6361,6784}"
"status" 5 "{Inventory,Activated}"
"{Closed,Expired,Suspended}"
"demo" 1 "{f}" ""
"card_set_id" 905
"{5201,3203,3169,5679,5143,5204,5655,4322,5236,4513}"
"{4,3080,3896,4349,4701,5179,5445,5706,6003,6361,6784}"
"external_id" 1 "{""}" ""
"card_id" -1 ""
"{0267xxxxxxxxxx46,1000xxxxxxxxxx86,1000xxxxxxxxxx85,1000xxxxxxxxxx69,10
00xxxxxxxxxx04,1000xxxxxxxxxx11,1000xxxxxxxxxx84,1000xxxxxxxxxx65,600xxx
xxxxxxx4,6006xxxxxxxxxxxx279,998xxxxxxxxxx40}"
"pin" 9654 "{1234,1643,2392,6577,0085,0515,0729,1125,1801,1960}"
"{0000,1029,2012,2983,3965,4903,5878,6828,7821,8920,9992}"