select query performance question

Поиск
Список
Период
Сортировка
От Thomas Zaksek
Тема select query performance question
Дата
Msg-id 4A6DB508.6070907@ptt.uni-due.de
обсуждение исходный текст
Ответы Re: select query performance question  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: select query performance question  (Matthew Wakeling <matthew@flymine.org>)
Re: select query performance question  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Re: select query performance question  (Mike Ivanov <mikei@activestate.com>)
Список pgsql-performance
Hi,
subject is the following type of query needed in a function to select data:

SELECT ' 13.04.2009 12:00:00 ' AS zeit,

                                     
'M' AS ganglinientyp,

                                      
m.zs_nr AS zs,

                                   
j_ges,

                                 
de_mw_abh_j_lkw(mw_abh) AS j_lkw,

                                   
de_mw_abh_v_pkw(mw_abh) AS v_pkw,

                                    
de_mw_abh_v_lkw(mw_abh) AS v_lkw,

                                     
de_mw_abh_p_bel(mw_abh) AS p_bel

                               
FROM messungen_v_dat_2009_04_13 m

                                
INNER JOIN de_mw w ON w.nr = m.mw_nr

                               
WHERE  m.ganglinientyp = 'M'

                              
AND ' 890 ' = m.minute_tag;
explain analyse brings up

Nested Loop  (cost=0.00..66344.47 rows=4750 width=10) (actual
time=134.160..19574.228 rows=4148 loops=1)
   ->  Index Scan using messungen_v_dat_2009_04_13_gtyp_minute_tag_idx
on messungen_v_dat_2009_04_13 m  (cost=0.00..10749.14 rows=4750 width=8)
(actual time=64.681..284.732 rows=4148 loops=1)
         Index Cond: ((ganglinientyp = 'M'::bpchar) AND (891::smallint =
minute_tag))
   ->  Index Scan using de_nw_nr_idx on de_mw w  (cost=0.00..10.69
rows=1 width=10) (actual time=4.545..4.549 rows=1 loops=4148)
         Index Cond: (w.nr = m.mw_nr)
 Total runtime: 19590.078 ms

Seems quite slow to me.
Is this query plan near to optimal or are their any serious flaws?

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: More speed counting rows
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Can Postgres use an INDEX over an OR?