Message-ID: <47B41E83.6060509@ptt.uni-due.de>
Date: Thu, 14 Feb 2008 11:57:07 +0100
From: Thomas Zaksek <zaksek@ptt.uni-due.de>
User-Agent: Thunderbird 2.0.0.9 (X11/20071115)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Join Query Perfomance Issue
References: <47B08F19.8000500@ptt.uni-due.de>	
	<dcc563d10802111337x2e19d2e4n74979077125079f1@mail.gmail.com>	
	<47B170BF.5000206@ptt.uni-due.de>
	<dcc563d10802131336s366d66c6kcb9f1fd23818552b@mail.gmail.com>
In-Reply-To: <dcc563d10802131336s366d66c6kcb9f1fd23818552b@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.