Why does not perform index combination
| От | Adnan DURSUN |
|---|---|
| Тема | Why does not perform index combination |
| Дата | |
| Msg-id | BAY106-DAV1906100D639F6AE4BBA692FAFB0@phx.gbl обсуждение исходный текст |
| Ответы |
Re: Why does not perform index combination
|
| Список | 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"
--------------------------------------------------------------------------------------------------------------------------
"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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Martijn van OosterhoutДата:
Сообщение: Re: [HACKERS] qsort again (was Re: Strange Create