From: Thomas Zaksek <zaksek@ptt.uni-due.de>
To: pgsql-performance@postgresql.org
Subject: Join Query Perfomance Issue
Date: Mon, 11 Feb 2008 19:08:25 +0100
Message-ID: <47B08F19.8000500@ptt.uni-due.de> (raw)
I have serious performance problems with the following type of queries:
/
/explain analyse SELECT '12.11.2007 18:04:00 UTC' AS zeit,
'M' AS datatyp,
p.zs_nr AS zs_de,
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 messpunkt p, messungen_v_dat_2007_11_12 m, de_mw w
WHERE m.ganglinientyp = 'M'
AND 381 = m.minute_tag
AND (p.nr, p.mw_nr) = (m.messpunkt, w.nr);
Explain analze returns
Nested Loop (cost=0.00..50389.39 rows=3009 width=10) (actual
time=0.503..320.872 rows=2189 loops=1)
-> Nested Loop (cost=0.00..30668.61 rows=3009 width=8) (actual
time=0.254..94.116 rows=2189 loops=1)
-> Index Scan using
messungen_v_dat_2007_11_12_messpunkt_minute_tag_idx on
messungen_v_dat_2007_11_12 m (cost=0.00..5063.38 rows=3009 width=4)
(actual time=0.131..9.262 rows=2189 loops=1)
Index Cond: ((ganglinientyp = 'M'::bpchar) AND (381 =
minute_tag))
-> Index Scan using messpunkt_nr_idx on messpunkt p
(cost=0.00..8.50 rows=1 width=12) (actual time=0.019..0.023 rows=1
loops=2189)
Index Cond: (p.nr = m.messpunkt)
-> Index Scan using de_nw_nr_idx on de_mw w (cost=0.00..6.53 rows=1
width=10) (actual time=0.019..0.023 rows=1 loops=2189)
Index Cond: (p.mw_nr = w.nr)
Total runtime: 329.134 ms
(9 rows)
Doesnt looks too bad to me, but i'm not that deep into sql query
optimization. However, these type of query is used in a function to
access a normalized, partitioned database, so better performance in this
queries would speed up the whole database system big times.
Any suggestions here would be great. I allready tested some things,
using inner join, rearranging the order of the tables, but but only
minor changes in the runtime, the version above seemed to get us the
best performance.
/
/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zaksek@ptt.uni-due.de
Subject: Re: Join Query Perfomance Issue
In-Reply-To: <47B08F19.8000500@ptt.uni-due.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox