Why does not perform index combination

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux