planer don't use index. bad plan for where id = x or id in (select ...) - Mailing list pgsql-general

Hello,

Pg make query 1. and 2. very fast (use index), but for query 3. dont use
index. I can solve its using select union, but I readed so pg 7.5 don't
problem with OR operator. I use cvs pg. I used vacuum analyze first.

table sp_op_product has 15K rows, sp_op_uct 37K rows;

regards
Pavel Stehule

query 1.

intra=# explain analyse select * from sp_op_uct where sp_op_id in (select
sp_op_id from sp_op_produkt where bal_zak = 34123);
                                                                    QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=7.81..74.59 rows=17 width=371) (actual
time=0.241..0.249 rows=1 loops=1)
   ->  HashAggregate  (cost=7.81..7.81 rows=12 width=4) (actual
time=0.118..0.120 rows=1 loops=1)
         ->  Index Scan using sp_op_product_bal_zak on sp_op_produkt
(cost=0.00..7.78 rows=12 width=4) (actual time=0.077..0.082 rows=1
loops=1)
               Index Cond: (bal_zak = 34123)
   ->  Index Scan using sp_op_uct_sp_op_id on sp_op_uct  (cost=0.00..5.54
rows=2 width=371) (actual time=0.068..0.072 rows=1 loops=1)
         Index Cond: (sp_op_uct.sp_op_id = "outer".sp_op_id)
 Total runtime: 1.846 ms

query 2.

intra=# explain ANALYZE select * from sp_op_uct where sp_op_id = 34123;
                                                           QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------
 Index Scan using sp_op_uct_sp_op_id on sp_op_uct  (cost=0.00..5.54 rows=2
width=371) (actual time=0.066..0.072 rows=1 loops=1)
   Index Cond: (sp_op_id = 34123)
 Total runtime: 0.287 ms

slow query 3.
intra=# EXPLAIN ANALYZE select * from sp_op_uct where sp_op_id = 34123 or
sp_op_id in (select sp_op_id from sp_op_produkt where bal_zak = 34123) ;
                                                                  QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on sp_op_uct  (cost=7.81..1283.83 rows=18602 width=371) (actual
time=3.176..240.379 rows=2 loops=1)
   Filter: ((sp_op_id = 34123) OR (hashed subplan))
   SubPlan
     ->  Index Scan using sp_op_product_bal_zak on sp_op_produkt
(cost=0.00..7.78 rows=12 width=4) (actual time=0.441..0.449 rows=1
loops=1)
           Index Cond: (bal_zak = 34123)
 Total runtime: 240.868 ms

Fast query 4.

intra=# explain ANALYZE select * from sp_op_uct where sp_op_id in (select
sp_op_id from sp_op_produkt where bal_zak = 34123) union select * from
sp_op_uct where sp_op_id = 34123;

                                          
QUERY PLAN
                  

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=80.72..82.39 rows=19 width=371) (actual time=0.857..0.868
rows=2 loops=1)
   ->  Sort  (cost=80.72..80.77 rows=19 width=371) (actual
time=0.852..0.854 rows=2 loops=1)
         Sort Key: sp_op_id, perioda, uozac, uokon, setup, timely,
uamount, "LIMIT", krok, kdy, uctuj, tarif, jednotka, merfunc, tarif_id,
hl_cinnost, merene, typ_fakturace, region, prod_group, rychlost, sdilene,
produkt, vl_sluzba, per_jedn, vpn, prov_inst, pevne_komutovane, spravnost,
fakt_text, pres_rychlost, pm, pm2, sp_kvalita
         ->  Append  (cost=7.81..80.32 rows=19 width=371) (actual
time=0.247..0.317 rows=2 loops=1)
               ->  Subquery Scan "*SELECT* 1"  (cost=7.81..74.76 rows=17
width=371) (actual time=0.244..0.256 rows=1 loops=1)
                     ->  Nested Loop  (cost=7.81..74.59 rows=17 width=371)
(actual time=0.197..0.205 rows=1 loops=1)
                           ->  HashAggregate  (cost=7.81..7.81 rows=12
width=4) (actual time=0.096..0.098 rows=1 loops=1)
                                 ->  Index Scan using
sp_op_product_bal_zak on sp_op_produkt  (cost=0.00..7.78 rows=12 width=4)
(actual time=0.040..0.044 rows=1 loops=1)
                                       Index Cond: (bal_zak = 34123)
                           ->  Index Scan using sp_op_uct_sp_op_id on
sp_op_uct  (cost=0.00..5.54 rows=2 width=371) (actual time=0.045..0.050
rows=1 loops=1)
                                 Index Cond: (sp_op_uct.sp_op_id =
"outer".sp_op_id)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..5.56 rows=2
width=371) (actual time=0.048..0.053 rows=1 loops=1)
                     ->  Index Scan using sp_op_uct_sp_op_id on sp_op_uct
(cost=0.00..5.54 rows=2 width=371) (actual time=0.019..0.022 rows=1
loops=1)
                           Index Cond: (sp_op_id = 34123)
 Total runtime: 2.413 ms