Re: 500x speed-down: Wrong query plan?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tom Lane wrote:
Alessandro Baretta <a.baretta@xxxxxxxxxxxxxxx> 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/>


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux