Thread: Join Query Perfomance Issue
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.
/
/
On Feb 11, 2008 12:08 PM, Thomas Zaksek <zaksek@ptt.uni-due.de> wrote: > 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) This nested loop is using us most of your time. Try increasing work_mem and see if it chooses a better join plan, and / or turn off nested loops for a moment and see if that helps. set enable_nestloop = off Note that set enable_xxx = off Is kind of a hammer to the forebrain setting. It's not subtle, and the planner can't work around it. So use them with caution. That said, I had one reporting query that simply wouldn't run fast without turning off nested loops for that one. But don't turn off nested queries universally, they are still a good choice for smaller amounts of data.
Correction: > turning off nested loops for that one. But don't turn off nested > queries universally, they are still a good choice for smaller amounts > of data. queries should be loops up there...
Scott Marlowe schrieb:
> On Feb 11, 2008 12:08 PM, Thomas Zaksek <zaksek@ptt.uni-due.de> wrote:
>
>> 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)
>>
>
> This nested loop is using us most of your time. Try increasing
> work_mem and see if it chooses a better join plan, and / or turn off
> nested loops for a moment and see if that helps.
>
> set enable_nestloop = off
>
> Note that set enable_xxx = off
>
> Is kind of a hammer to the forebrain setting. It's not subtle, and
> the planner can't work around it. So use them with caution. That
> said, I had one reporting query that simply wouldn't run fast without
> turning off nested loops for that one. But don't turn off nested
> queries universally, they are still a good choice for smaller amounts
> of data.
>
I tried turning off nestloop, but with terrible results:
Hash Join (cost=208328.61..228555.14 rows=3050 width=10) (actual
time=33421.071..40362.136 rows=2920 loops=1)
Hash Cond: (w.nr = p.mw_nr)
-> Seq Scan on de_mw w (cost=0.00..14593.79 rows=891479 width=10)
(actual time=0.012..3379.971 rows=891479 loops=1)
-> Hash (cost=208290.49..208290.49 rows=3050 width=8) (actual
time=33420.877..33420.877 rows=2920 loops=1)
-> Merge Join (cost=5303.71..208290.49 rows=3050 width=8)
(actual time=31.550..33407.688 rows=2920 loops=1)
Merge Cond: (p.nr = m.messpunkt)
-> Index Scan using messpunkt_nr_idx on messpunkt p
(cost=0.00..238879.39 rows=6306026 width=12) (actual
time=0.056..17209.317 rows=4339470 loops=1)
-> Sort (cost=5303.71..5311.34 rows=3050 width=4)
(actual time=25.973..36.858 rows=2920 loops=1)
Sort Key: m.messpunkt
-> Index Scan using
messungen_v_dat_2007_11_12_messpunkt_minute_tag_idx on
messungen_v_dat_2007_11_12 m (cost=0.00..5127.20 rows=3050 width=4)
(actual time=0.124..12.822 rows=2920 loops=1)
Index Cond: ((ganglinientyp = 'M'::bpchar)
AND (651 = minute_tag))
Total runtime: 40373.512 ms
(12 rows)
Looks crappy, isn't it?
I also tried to increase work_men, now the config is
work_mem = 4MB
maintenance_work_mem = 128MB,
in regard to performance, it wasnt effective at all.
The postgresql runs on a HP Server with dual Opteron, 3GB of Ram, what
are good settings here? The database will have to work with tables of
several 10Millions of Lines, but only a few columns each. No more than
maybe ~5 clients accessing the database at the same time.
> I have serious performance problems with the following type of queries: > > 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. Can you send the table definitions of the tables involved in the query, including index information? Might be if we look hard enough we can find something. Peter
> Can you send the table definitions of the tables involved in the
> query, including index information? Might be if we look hard enough we
> can find something.
>
> Peter
Table "messungen_v_dat_2007_11_12"
Column | Type | Modifiers | Description
---------------+--------------+-----------+-------------
ganglinientyp | character(1) | |
minute_tag | smallint | |
messpunkt | integer | |
Indexes:
"messungen_v_dat_2007_11_12_ganglinientyp_key" UNIQUE, btree
(ganglinientyp, minute_tag, messpunkt)
"messungen_v_dat_2007_11_12_messpunkt_idx" btree (messpunkt)
"messungen_v_dat_2007_11_12_messpunkt_minute_tag_idx" btree
(ganglinientyp, minute_tag)
Foreign-key constraints:
"messungen_v_dat_2007_11_12_messpunkt_fkey" FOREIGN KEY (messpunkt)
REFERENCES messpunkt(nr)
Inherits: messungen_v_dat
Has OIDs: no
Table "messpunkt"
Column | Type |
Modifiers | Description
--------+---------+--------------------------------------------------------+-------------
nr | integer | not null default
nextval('messpunkt_nr_seq'::regclass) |
zs_nr | integer
| |
mw_nr | integer
| |
Indexes:
"messpunkt_pkey" PRIMARY KEY, btree (nr)
"messpunkt_zs_nr_key" UNIQUE, btree (zs_nr, mw_nr)
"messpunkt_mw_idx" btree (mw_nr)
"messpunkt_nr_idx" btree (nr)
"messpunkt_zs_idx" btree (zs_nr)
Foreign-key constraints:
"messpunkt_mw_nr_fkey" FOREIGN KEY (mw_nr) REFERENCES de_mw(nr)
"messpunkt_zs_nr_fkey" FOREIGN KEY (zs_nr) REFERENCES de_zs(zs)
Has OIDs: no
Table "de_mw"
Column | Type | Modifiers
| Description
--------+----------+----------------------------------------------------+-------------
nr | integer | not null default nextval('de_mw_nr_seq'::regclass) |
j_ges | smallint | |
mw_abh | integer | |
Indexes:
"de_mw_pkey" PRIMARY KEY, btree (nr)
"de_mw_j_ges_key" UNIQUE, btree (j_ges, mw_abh)
"de_nw_nr_idx" btree (nr)
Check constraints:
"de_mw_check" CHECK (j_ges IS NOT NULL AND (j_ges = 0 AND (mw_abh =
0 OR mw_abh = 255 OR mw_abh IS NULL) OR j_ges > 0 AND j_ges <= 80 AND
mw_abh <> 0))
Has OIDs: no
We have tried some recoding now, using a materialized view we could
reduce the query to a join over too tables without any functions inside
the query, for example:
explain analyse SELECT '12.11.2007 18:04:00 UTC' AS zeit,
'M' AS ganglinientyp,
zs_de,
j_ges,
j_lkw,
v_pkw,
v_lkw,
p_bel
FROM messungen_v_dat_2007_11_12 m
LEFT JOIN messwerte_mv w on w.nr = m.messpunkt
WHERE m.ganglinientyp = 'M'
AND 992 = m.minute_tag;
Nested Loop Left Join (cost=0.00..32604.48 rows=3204 width=14) (actual
time=11.991..2223.227 rows=2950 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..5371.09 rows=3204 width=4)
(actual time=0.152..12.385 rows=2950 loops=1)
Index Cond: ((ganglinientyp = 'M'::bpchar) AND (992 = minute_tag))
-> Index Scan using messwerte_mv_nr_idx on messwerte_mv w
(cost=0.00..8.49 rows=1 width=18) (actual time=0.730..0.734 rows=1
loops=2950)
Index Cond: (w.nr = m.messpunkt)
Total runtime: 2234.143 ms
(6 rows)
To me this plan looks very clean and nearly optimal, BUT ~2seconds for
the nested loop can't be that good, isn't it?
The behavior of this query and the database is quite a mystery for me,
yesterday i had it running in about 100ms, today i started testing with
the same query and 2000-3000ms :(
Could this be some kind of a postgresql server/configuration problem?
This queries are very perfomance dependend, they are called a lot of
times in a comlex physical real time simulation of traffic systems.
200ms would be ok here, but >1sec is perhaps not functional.
The old version just used one big (partitioned) table without any joins,
performing this query in 10-300ms, depended on the server load.
Thomas Zaksek <zaksek@ptt.uni-due.de> writes:
> Nested Loop Left Join (cost=0.00..32604.48 rows=3204 width=14) (actual
> time=11.991..2223.227 rows=2950 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..5371.09 rows=3204 width=4)
> (actual time=0.152..12.385 rows=2950 loops=1)
> Index Cond: ((ganglinientyp = 'M'::bpchar) AND (992 = minute_tag))
> -> Index Scan using messwerte_mv_nr_idx on messwerte_mv w
> (cost=0.00..8.49 rows=1 width=18) (actual time=0.730..0.734 rows=1
> loops=2950)
> Index Cond: (w.nr = m.messpunkt)
> Total runtime: 2234.143 ms
> (6 rows)
> To me this plan looks very clean and nearly optimal,
For so many rows I'm surprised it's not using a bitmap indexscan.
What PG version is this? How big are these tables?
regards, tom lane
> For so many rows I'm surprised it's not using a bitmap indexscan. > What PG version is this? How big are these tables? > > regards, tom lane Its PG 8.2.6 on Freebsd. messungen_v_dat_2007_11_12 ist about 4 million rows and messwerte is about 10 million rows.
On Feb 12, 2008 4:11 AM, Thomas Zaksek <zaksek@ptt.uni-due.de> wrote: > I tried turning off nestloop, but with terrible results: Yeah, it didn't help. I was expecting the query planner to switch to a more efficient join plan. > I also tried to increase work_men, now the config is > work_mem = 4MB Try setting it higher for JUST THIS query. i.e. set work_mem=128M; explain analyze select .... and see how that runs. Then play with it til you've got it down to what helps. Note that work_mem in postgresql.conf being too large can be dangerous, so it might be something you set for just this query for safety reasons.
Scott Marlowe schrieb:
>
> Yeah, it didn't help. I was expecting the query planner to switch to
> a more efficient join plan.
>
>
> Try setting it higher for JUST THIS query. i.e.
>
> set work_mem=128M;
> explain analyze select ....
>
> and see how that runs. Then play with it til you've got it down to
> what helps. Note that work_mem in postgresql.conf being too large can
> be dangerous, so it might be something you set for just this query for
> safety reasons.
>
>
Tried some values for work_mem like 32M, 128M, 256M, not much of a
difference to 4M, so i think work_mem is high enough here in basic
configuration.
I have now kind of optimized the query to a join of to tables(using
materialized views), basically like this:
SELECT foo
FROM messungen_v_dat_2007_11_12 m
INNER JOIN messwerte_mv p ON p.nr = m.messpunkt
WHERE m.ganglinientyp = 'M'
AND xxx = m.minute_tag;
Are there any major flaws in this construction? Is there a better way to
join two tables this way?
Best i get here is a runtime of about 100ms, what seems ok to me.
The plan is like
nested loop
index scan
index scan
Nested Loop (cost=0.00..31157.91 rows=3054 width=14) (actual
time=0.252..149.557 rows=2769 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..5134.28 rows=3054 width=4)
(actual time=0.085..11.562 rows=2769 loops=1)
Index Cond: ((ganglinientyp = 'M'::bpchar) AND (799 = minute_tag))
-> Index Scan using messwerte_mv_nr_idx on messwerte_mv p
(cost=0.00..8.51 rows=1 width=18) (actual time=0.031..0.035 rows=1
loops=2769)
Index Cond: (p.nr = m.messpunkt)
Total runtime: 159.703 ms
(6 rows)
Nested Loop is not the best regarding to performance, but there isn't a
way to avoid it here?
Another strange problem occurs when i retry the query after about 12
hours break without akivity on the database (starting work in the
morning) :
The query runs incredible slow (~3sec), analyse on the tables doesn't
change much. But when i switch enable_netloop to false, retry the query
(very bad result, > 30sec), then set enable_nestloop back to true, the
query works amazingly fast again (100ms). Note that explain analyse
provides the exactly similar plan for the 3sec at the beginning and the
fast 100ms later. I have absolutly no idea what causes this behavior.
> Nested Loop (cost=0.00..31157.91 rows=3054 width=14) (actual > time=0.252..149.557 rows=2769 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..5134.28 rows=3054 width=4) > (actual time=0.085..11.562 rows=2769 loops=1) > Index Cond: ((ganglinientyp = 'M'::bpchar) AND (799 = minute_tag)) > -> Index Scan using messwerte_mv_nr_idx on messwerte_mv p > (cost=0.00..8.51 rows=1 width=18) (actual time=0.031..0.035 rows=1 > loops=2769) > Index Cond: (p.nr = m.messpunkt) > Total runtime: 159.703 ms > (6 rows) > > Nested Loop is not the best regarding to performance, but there isn't a > way to avoid it here? Your own tests have proven it's the right approach for this particular query. > Another strange problem occurs when i retry the query after about 12 > hours break without akivity on the database (starting work in the > morning) : > The query runs incredible slow (~3sec), analyse on the tables doesn't > change much. But when i switch enable_netloop to false, retry the query > (very bad result, > 30sec), then set enable_nestloop back to true, the > query works amazingly fast again (100ms). The o/s has cached some of the data so instead of actually hitting the disk, it's getting it from the o/s cache. -- Postgresql & php tutorials http://www.designmagick.com/