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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
