X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A3F2E9DC84E for ; Mon, 9 Jan 2006 14:50:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83092-06 for ; Mon, 9 Jan 2006 14:50:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.barettadeit.com (unknown [213.255.109.130]) by postgresql.org (Postfix) with ESMTP id 59FC69DC814 for ; Mon, 9 Jan 2006 14:50:09 -0400 (AST) Received: from [10.0.0.10] (alex.barettalocal.com [10.0.0.10]) by mail.barettadeit.com (Postfix) with ESMTP id 36B247D258A; Mon, 9 Jan 2006 19:53:57 +0100 (CET) Message-ID: <43C2B06B.4050506@barettadeit.com> Date: Mon, 09 Jan 2006 19:50:19 +0100 From: Alessandro Baretta User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Matteo Beccati , pgsql-performance@postgresql.org Subject: Re: 500x speed-down: Wrong query plan? References: <43C26A5F.7040702@barettadeit.com> <43C27AFA.7060604@beccati.com> <43C28DEA.60801@barettadeit.com> <6719.1136832065@sss.pgh.pa.us> In-Reply-To: <6719.1136832065@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/64 X-Sequence-Number: 16542 Tom Lane wrote: > Alessandro Baretta writes: > >>Matteo Beccati wrote: >> >>>Are you sure that you recentrly ANALYZED the table "ubicazione"? If so, >>>try to increase statistics for the id_ente column. > > >>No, this is not the problem. I increased the amount of statistics with ALTER >>TABLE ... SET STATISTICS 1000, which is as much as I can have. > > > What Matteo wanted to know is if you'd done an ANALYZE afterward. ALTER > TABLE SET STATISTICS doesn't in itself update the statistics. I probably forgot to mention that I have vacuum-analyze the after this operation, and, since I did not manage to get the index to work, I vacuum-analyzed several times more, just to be on the safe side. > What do you get from > > EXPLAIN SELECT * FROM articolo WHERE articolo.xdbs_modified > '2006-01-08 18:25:00+01'; > > I'm curious to see how many rows the planner thinks this will produce, > and whether it will use the index or not. dmd-freerp-1-alex=# EXPLAIN ANALYZE SELECT * FROM articolo WHERE articolo.xdbs_modified > '2006-01-08 18:25:00+01'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using articolo_modified_index on articolo (cost=0.00..3914.91 rows=17697 width=653) (actual time=0.032..0.032 rows=0 loops=1) Index Cond: (xdbs_modified > '2006-01-08 18:25:00'::timestamp without time zone) Total runtime: 0.150 ms (3 rows) The planner gets tricked only by *SOME* join queries. > Also, I gather from the plan choices that the condition id_ente = 'dmd' > isn't very selective ... what fraction of the rows in each table > satisfy that? In most situation, this condition selects all the tuples. "id_ente" selects the "owner of the data". Since, in most situations, companies do not share a database between them--although the application allows it--filtering according to 'id_ente' is like to filtering at all. Yet, this field is used in the indexes, so the condition ought to be specified in the queries anyhow. -- ********************************************************************* http://www.barettadeit.com/ Baretta DE&IT A division of Baretta SRL tel. +39 02 370 111 55 fax. +39 02 370 111 54 Our technology: The Application System/Xcaml (AS/Xcaml) The FreerP Project