Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D0A1B2E00B8 for ; Wed, 13 Feb 2008 11:48:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 58301-09 for ; Wed, 13 Feb 2008 11:48:35 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 254032E0090 for ; Wed, 13 Feb 2008 11:48:44 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1DFmgpZ015213; Wed, 13 Feb 2008 10:48:42 -0500 (EST) To: Thomas Zaksek cc: pgsql-performance@postgresql.org Subject: Re: Join Query Perfomance Issue In-reply-to: <47B2D840.6060802@ptt.uni-due.de> References: <47B08F19.8000500@ptt.uni-due.de> <4544e0330802121234l33be13a9g5dec39ef032f6268@mail.gmail.com> <47B2D840.6060802@ptt.uni-due.de> Comments: In-reply-to Thomas Zaksek message dated "Wed, 13 Feb 2008 12:45:04 +0100" Date: Wed, 13 Feb 2008 10:48:42 -0500 Message-ID: <15212.1202917722@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/138 X-Sequence-Number: 28680 Thomas Zaksek writes: > Nested Loop Left Join (cost=0.00..32604.48 rows=3204 width=14) (actual > time=11.991..2223.227 rows=2950 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..5371.09 rows=3204 width=4) > (actual time=0.152..12.385 rows=2950 loops=1) > Index Cond: ((ganglinientyp = 'M'::bpchar) AND (992 = minute_tag)) > -> Index Scan using messwerte_mv_nr_idx on messwerte_mv w > (cost=0.00..8.49 rows=1 width=18) (actual time=0.730..0.734 rows=1 > loops=2950) > Index Cond: (w.nr = m.messpunkt) > Total runtime: 2234.143 ms > (6 rows) > To me this plan looks very clean and nearly optimal, For so many rows I'm surprised it's not using a bitmap indexscan. What PG version is this? How big are these tables? regards, tom lane