X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 359EF32A00B for ; Tue, 19 Oct 2004 18:49:59 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82656-04 for ; Tue, 19 Oct 2004 17:49:44 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 4D5CF329FA4 for ; Tue, 19 Oct 2004 18:49:42 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 7BD5020491; Tue, 19 Oct 2004 20:53:32 +0300 (EEST) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30470-07; Tue, 19 Oct 2004 20:53:31 +0300 (EEST) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 4E26D203AA; Tue, 19 Oct 2004 20:53:31 +0300 (EEST) Message-ID: <0c4501c4b604$06f15460$0b00a8c0@forge> From: "Contact AR-SD.NET" To: "Tom Lane" Cc: References: <0bef01c4b5f8$70026ae0$0b00a8c0@forge> <16865.1098204769@sss.pgh.pa.us> Subject: Re: Index not used in query. Why? Date: Tue, 19 Oct 2004 20:49:45 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/318 X-Sequence-Number: 8794 Is there a solution to make it faster? At the end I need only in the query the id_status =4 and 6, but if I write in the sql query (where condition) where id_status in (4,6), the explain says the same(the slow version). For example: SELECT count(o.id) FROM orders o INNER JOIN report r ON o.id=r.id_order INNER JOIN status s ON o.id_status=s.id INNER JOIN contact c ON o.id_ag=c.id INNER JOIN endkunde e ON o.id_endkunde=e.id INNER JOIN zufriden z ON r.id_zufriden=z.id INNER JOIN plannung v ON v.id=o.id_plannung INNER JOIN mpsworker w ON v.id_worker=w.id INNER JOIN person p ON p.id = w.id_person WHERE o.id_status in (4,6); The result for this query is also without index searches. I really have to make this query a little more faster. Suggestions? Regards, Andy. ----- Original Message ----- From: "Tom Lane" To: "Andrei Bintintan" Cc: Sent: Tuesday, October 19, 2004 7:52 PM Subject: Re: [PERFORM] Index not used in query. Why? > "Andrei Bintintan" writes: > > Hi to all! I have the following query. The execution time is very big, it > > doesn't use the indexes and I don't understand why... > > Indexes are not necessarily the best way to do a large join. > > > If I use the following query the indexes are used: > > The key reason this wins seems to be that the id_status = 4 condition > is far more selective than id_status > 3 (the estimates are 52 and 36967 > rows respectively ... is that accurate?) which means that the second > query is inherently about 1/700th as much work. This, and not the use > of indexes, is the fundamental reason why it's faster. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >