pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy does not perform index combination
3+ messages / 2 participants
[nested] [flat]
* Why does not perform index combination
@ 2006-02-16 16:56 Adnan DURSUN <a_dursun@hotmail.com>
2006-02-16 17:27 ` Re: Why does not perform index combination Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Adnan DURSUN @ 2006-02-16 16:56 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why does not perform index combination
2006-02-16 16:56 Why does not perform index combination Adnan DURSUN <a_dursun@hotmail.com>
@ 2006-02-16 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-02-16 17:27 UTC (permalink / raw)
To: Adnan DURSUN <a_dursun@hotmail.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why does not perform index combination
@ 2006-02-16 22:14 Adnan DURSUN <a_dursun@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Adnan DURSUN @ 2006-02-16 22:14 UTC (permalink / raw)
To: pgsql-performance
>From: Tom Lane
>Date: 02/16/06 19:29:21
>To: Adnan DURSUN
>Cc: pgsql-performance@postgresql.org
>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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-02-16 22:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-16 16:56 Why does not perform index combination Adnan DURSUN <a_dursun@hotmail.com>
2006-02-16 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-16 22:14 Re: Why does not perform index combination Adnan DURSUN <a_dursun@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox