Re: Join Query Perfomance Issue

> 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/