Skip site navigation (1) Skip section navigation (2)

Re: select query performance question

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Thomas Zaksek <zaksek(at)ptt(dot)uni-due(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: select query performance question
Date: 2009-07-27 14:22:08
Message-ID: 162867790907270722r4b7842afy1525a1e3a2e7b588@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello

maybe is wrong tip, but your function like de* should be slow. What is
time of query without calling these functions?

Pavel Stehule

2009/7/27 Thomas Zaksek <zaksek(at)ptt(dot)uni-due(dot)de>:
> 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?
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In response to

pgsql-performance by date

Next:From: Віталій ТимчишинDate: 2009-07-27 14:33:32
Subject: Re: Can Postgres use an INDEX over an OR?
Previous:From: Tom LaneDate: 2009-07-27 14:18:28
Subject: Re: Can Postgres use an INDEX over an OR?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group