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/ ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
