Обсуждение: Why does not perform index combination
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"
--------------------------------------------------------------------------------------------------------------------------
"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
"Adnan DURSUN" <a_dursun@hotmail.com> writes:
> 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 ?
Probably thinks that the extra index doesn't add enough selectivity to
be worth scanning. It's probably right, too --- maybe with a narrower
date range the answer would be different.
I think the main problem in this plan is the poor estimation of the size
of the d1/s join. Are your stats up to date on those tables? Maybe
boosting the statistics target for one or both would help.
regards, tom lane
>From: Tom Lane
>Date: 02/16/06 19:29:21
>To: Adnan DURSUN
>Subject: Re: [PERFORM] Why does not perform index combination
>"Adnan DURSUN" <a_dursun@hotmail.com> writes:
>> 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 ?
>Probably thinks that the extra index doesn't add enough selectivity to
>be worth scanning. It's probably right, too --- maybe with a narrower
>date range the answer would be different.
Yes, a narrower date range solves that.. Thanks for your suggestions...
>I think the main problem in this plan is the poor estimation of the size
>of the d1/s join. Are your stats up to date on those tables? Maybe
>boosting the statistics target for one or both would help.
Database was vacuumed and analyzed before got take the plan..
Regards
Adnan DURSUN