From: Alessandro Baretta <a.baretta@barettadeit.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Matteo Beccati <php@beccati.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 500x speed-down: Wrong query plan?
Date: Mon, 09 Jan 2006 19:50:19 +0100
Message-ID: <43C2B06B.4050506@barettadeit.com> (raw)
In-Reply-To: <6719.1136832065@sss.pgh.pa.us>
References: <43C26A5F.7040702@barettadeit.com>
<43C27AFA.7060604@beccati.com>
<43C28DEA.60801@barettadeit.com>
<6719.1136832065@sss.pgh.pa.us>
Tom Lane wrote:
> Alessandro Baretta <a.baretta@barettadeit.com> 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)
<http://www.asxcaml.org/;
The FreerP Project
<http://www.freerp.org/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a.baretta@barettadeit.com, tgl@sss.pgh.pa.us, php@beccati.com
Subject: Re: 500x speed-down: Wrong query plan?
In-Reply-To: <43C2B06B.4050506@barettadeit.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox