Re: select query performance question

Hi Thomas,

How is 'messungen_v_dat_2009_04_13_gtyp_minute_tag_idx' defined? What is
the row count for the table?

Mike

> 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?
>