Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 25EBB2E0064 for ; Wed, 13 Feb 2008 07:17:54 -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 89692-04 for ; Wed, 13 Feb 2008 07:17:42 -0400 (AST) Received: from mail.ptt.uni-duisburg.de (trottoir.ptt.uni-duisburg.de [134.91.151.36]) by postgresql.org (Postfix) with ESMTP id 2E6212E0103 for ; Wed, 13 Feb 2008 07:17:48 -0400 (AST) Received: (qmail 46193 invoked by uid 0); 13 Feb 2008 11:18:07 -0000 Received: from 134.91.151.30 by trottoir.ptt.uni-duisburg.de (envelope-from , uid 89) with qmail-scanner-1.25 (sweep: 2.44.1/4.16.0. /636764. spamassassin: 3.2.1. Clear:RC:1(134.91.151.30):. Processed in 7.036051 secs); 13 Feb 2008 11:18:07 -0000 X-Qmail-Scanner-Mail-From: zaksek@ptt.uni-due.de via trottoir.ptt.uni-duisburg.de X-Qmail-Scanner: 1.25 (Clear:RC:1(134.91.151.30):. Processed in 7.036051 secs) Received: from unknown (HELO finkenstrasse.ptt.uni-duisburg.de) (zaksek@ptt.uni-due.de@134.91.151.30) by mail.ptt.uni-duisburg.de with ESMTPA; 13 Feb 2008 11:17:59 -0000 Message-ID: <47B2D1C9.2080609@ptt.uni-due.de> Date: Wed, 13 Feb 2008 12:17:29 +0100 From: Thomas Zaksek User-Agent: Thunderbird 2.0.0.9 (X11/20071115) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Join Query Perfomance Issue References: <47B08F19.8000500@ptt.uni-due.de> <4544e0330802121234l33be13a9g5dec39ef032f6268@mail.gmail.com> In-Reply-To: <4544e0330802121234l33be13a9g5dec39ef032f6268@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200802/130 X-Sequence-Number: 28672 > Can you send the table definitions of the tables involved in the > query, including index information? Might be if we look hard enough we > can find something. > > Peter Table "messungen_v_dat_2007_11_12" Column | Type | Modifiers | Description ---------------+--------------+-----------+------------- ganglinientyp | character(1) | | minute_tag | smallint | | messpunkt | integer | | Indexes: "messungen_v_dat_2007_11_12_ganglinientyp_key" UNIQUE, btree (ganglinientyp, minute_tag, messpunkt) "messungen_v_dat_2007_11_12_messpunkt_idx" btree (messpunkt) "messungen_v_dat_2007_11_12_messpunkt_minute_tag_idx" btree (ganglinientyp, minute_tag) Foreign-key constraints: "messungen_v_dat_2007_11_12_messpunkt_fkey" FOREIGN KEY (messpunkt) REFERENCES messpunkt(nr) Inherits: messungen_v_dat Has OIDs: no Table "messpunkt" Column | Type | Modifiers | Description --------+---------+--------------------------------------------------------+------------- nr | integer | not null default nextval('messpunkt_nr_seq'::regclass) | zs_nr | integer | | mw_nr | integer | | Indexes: "messpunkt_pkey" PRIMARY KEY, btree (nr) "messpunkt_zs_nr_key" UNIQUE, btree (zs_nr, mw_nr) "messpunkt_mw_idx" btree (mw_nr) "messpunkt_nr_idx" btree (nr) "messpunkt_zs_idx" btree (zs_nr) Foreign-key constraints: "messpunkt_mw_nr_fkey" FOREIGN KEY (mw_nr) REFERENCES de_mw(nr) "messpunkt_zs_nr_fkey" FOREIGN KEY (zs_nr) REFERENCES de_zs(zs) Has OIDs: no Table "de_mw" Column | Type | Modifiers | Description --------+----------+----------------------------------------------------+------------- nr | integer | not null default nextval('de_mw_nr_seq'::regclass) | j_ges | smallint | | mw_abh | integer | | Indexes: "de_mw_pkey" PRIMARY KEY, btree (nr) "de_mw_j_ges_key" UNIQUE, btree (j_ges, mw_abh) "de_nw_nr_idx" btree (nr) Check constraints: "de_mw_check" CHECK (j_ges IS NOT NULL AND (j_ges = 0 AND (mw_abh = 0 OR mw_abh = 255 OR mw_abh IS NULL) OR j_ges > 0 AND j_ges <= 80 AND mw_abh <> 0)) Has OIDs: no