From: Chris <dmagick@gmail.com>
To: Thomas Zaksek <zaksek@ptt.uni-due.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join Query Perfomance Issue
Date: Fri, 15 Feb 2008 10:48:08 +1100
Message-ID: <47B4D338.6020904@gmail.com> (raw)
In-Reply-To: <47B41E83.6060509@ptt.uni-due.de>
References: <47B08F19.8000500@ptt.uni-due.de>
<dcc563d10802111337x2e19d2e4n74979077125079f1@mail.gmail.com>
<47B170BF.5000206@ptt.uni-due.de>
<dcc563d10802131336s366d66c6kcb9f1fd23818552b@mail.gmail.com>
<47B41E83.6060509@ptt.uni-due.de>
> 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/
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: dmagick@gmail.com, zaksek@ptt.uni-due.de
Subject: Re: Join Query Perfomance Issue
In-Reply-To: <47B4D338.6020904@gmail.com>
* 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