|
HI ALL,
I have query for a report. Explain analyze
result is below. The execution plan tells that it would use
"t_koltuk_islem_pkey" index on table "t_koltuk_islem" to scan. However,
there is another index on table "t_koltuk_islem" on column "islem_tarihi"
that can be combined on plan. Why doesn't optimizer choice that ? It prefer
to perform a filter on column "islem_tarihi" ...
Why ?
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------- "Nested Loop (cost=0.00..2411.48 rows=14 width=797) (actual time=117.427..4059.351 rows=55885 loops=1)" " -> Nested Loop (cost=0.00..35.69 rows=1 width=168) (actual time=0.124..8.714 rows=94 loops=1)" " Join Filter: ((""outer"".sefer_tip_kod = ""inner"".kod) AND ((""outer"".firma_no)::text = (""inner"".firma_no)::text))" " -> Nested Loop (cost=0.00..34.64 rows=1 width=154) (actual time=0.114..7.555 rows=94 loops=1)" " -> Nested Loop (cost=0.00..30.18 rows=1 width=144) (actual time=0.106..6.654 rows=94 loops=1)" " -> Nested Loop (cost=0.00..25.71 rows=1 width=134) (actual time=0.089..5.445 rows=94 loops=1)" " Join Filter: (((""inner"".""no"")::text = (""outer"".hat_no)::text) AND ((""inner"".firma_no)::text = (""outer"".firma_no)::text))" " -> Nested Loop (cost=0.00..24.21 rows=1 width=116) (actual time=0.063..1.632 rows=94 loops=1)" " Join Filter: ((""outer"".kod)::text = (""inner"".durumu)::text)" " -> Seq Scan on t_domains d2 (cost=0.00..2.21 rows=2 width=18) (actual time=0.029..0.056 rows=2 loops=1)" " Filter: ((name)::text = 'SFR_DURUMU'::text)" " -> Nested Loop (cost=0.00..10.91 rows=7 width=103) (actual time=0.028..0.649 rows=94 loops=2)" " Join Filter: ((""outer"".kod)::text = (""inner"".ek_dev)::text)" " -> Seq Scan on t_domains d1 (cost=0.00..2.21 rows=2 width=18) (actual time=0.017..0.046 rows=2 loops=2)" " Filter: ((name)::text = 'EKDEV'::text)" " -> Seq Scan on t_seferler s (cost=0.00..3.17 rows=94 width=90) (actual time=0.003..0.160 rows=94 loops=4)" " Filter: ((iptal)::text = 'H'::text)" " -> Seq Scan on t_hatlar h (cost=0.00..1.20 rows=20 width=18) (actual time=0.002..0.020 rows=20 loops=94)" " -> Index Scan using t_yer_pkey on t_yer y2 (cost=0.00..4.45 rows=1 width=14) (actual time=0.008..0.009 rows=1 loops=94)" " Index Cond: (""outer"".varis_yer_kod = y2.kod)" " Filter: ((iptal)::text = 'H'::text)" " -> Index Scan using t_yer_pkey on t_yer y1 (cost=0.00..4.45 rows=1 width=14) (actual time=0.004..0.006 rows=1 loops=94)" " Index Cond: (""outer"".kalkis_yer_kod = y1.kod)" " Filter: ((iptal)::text = 'H'::text)" " -> Seq Scan on t_sefer_tip t (cost=0.00..1.02 rows=2 width=18) (actual time=0.002..0.006 rows=2 loops=94)" " Filter: ((iptal)::text = 'H'::text)" " -> Index Scan using t_koltuk_islem_pkey on t_koltuk_islem i (cost=0.00..2375.10 rows=39 width=644) (actual time=38.151..41.881 rows=595 loops=94)" " Index Cond: (((""outer"".firma_no)::text = (i.firma_no)::text) AND ((""outer"".hat_no)::text = (i.hat_no)::text) AND (""outer"".kod = i.sefer_kod))" " Filter: ((islem_tarihi >= '2006-01-17'::date) AND (islem_tarihi <= '2006-02-16'::date))" "Total runtime: 4091.242 ms" Best Regards
Adnan DURSUN
ASRIN Bilişim Ltd.Şti
Ankara / TURKEY |
