From: | Chris <dmagick(at)gmail(dot)com> |
---|---|
To: | Thomas Zaksek <zaksek(at)ptt(dot)uni-due(dot)de> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Join Query Perfomance Issue |
Date: | 2008-02-14 23:48:08 |
Message-ID: | 47B4D338.6020904@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2008-02-14 23:54:34 | Re: Query slows after offset of 100K |
Previous Message | Tom Lane | 2008-02-14 21:55:03 | Re: Query slows after offset of 100K |