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 A52A532A1BB for ; Tue, 12 Oct 2004 15:29:45 +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 35685-01 for ; Tue, 12 Oct 2004 14:29:39 +0000 (GMT) Received: from vsmtp4.tin.it (unknown [212.216.176.150]) by svr1.postgresql.org (Postfix) with ESMTP id E6A1732A0AC for ; Tue, 12 Oct 2004 15:29:42 +0100 (BST) Received: from ims3d.cp.tin.it (192.168.70.103) by vsmtp4.tin.it (7.0.027) id 41568C9D00709332; Tue, 12 Oct 2004 16:29:42 +0200 Received: from [192.168.70.227] by ims3d.cp.tin.it with HTTP; Tue, 12 Oct 2004 16:29:36 +0200 Date: Tue, 12 Oct 2004 16:29:36 +0200 Message-ID: <41536BE0000113F4@ims3d.cp.tin.it> In-Reply-To: From: "Gabriele Bartolini" Subject: Re: Normal case or bad query plan? To: "Kris Jurka" Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-15" Content-Transfer-Encoding: quoted-printable 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/164 X-Sequence-Number: 8640 Hi Kris, >I believe the problem is that pg's lack of cross-column statistics is >producing the poor number of rows estimate. The number of rows mataching I got your point now. I had not understood it last night but it makes really sense. >which is roughtly 10% of the table. I imagine the query planner >believes that the other condition alone will match the other 90% of the >table. The problem is that it doesn't know that these two ranges' >intersection is actually tiny. The planner assumes a complete or nearly >complete overlap so it thinks it will need to fetch 10% of the rows from Yep, because it performs those checks separately and it gets 10% for one check and 90% for the other. As Tom says, I should somehow make PostgreSQL see my data as a single entity in order to perform a real range check. I will study some way to obtain it. However, I got better results by specifying the grane of the statistics through "ALTER TABLE ... SET STATISTICS". FYI I set it to 1000 (the maximum) and I reduced the query's estimated time by the 90% (from 40000ms to 4000ms) although much slower than the index scan (200ms). I will play a bit with data types as Tom suggested. For now, thanks anyone who tried and helped me. Ciao, -Gabriele