From: | "Bruno BAGUETTE" <pgsql-ml(at)baguette(dot)net> |
---|---|
To: | "'Stephan Szabo'" <sszabo(at)megazone(dot)bigpanda(dot)com>, "'Bruno BAGUETTE'" <pgsql-ml(at)baguette(dot)net> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | RE : RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ? |
Date: | 2004-02-08 00:26:40 |
Message-ID: | !~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAATBuXKOMvlkWzD3KJN6FWLMKAAAAQAAAAeVfx9WlOXEyeDyg88U7qaAEAAAAA@baguette.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> On Fri, 6 Feb 2004, Bruno BAGUETTE wrote:
>
> > > In addition to what Tom said, the row estimates look suspiciously
> > > default. You mention vacuuming, but do you ever analyze
> > > the tables?
> >
> > I run VACUUM FULL ANALYZE with the postgres user on all the
> > PostgreSQL
> > databases on the server, twice a day, sometimes more.
>
> Wierd, because you're getting 1000 estimated on both people
> and organizations. What does pg_class have to say about
> those two tables?
I'm sorry but I think that I misunderstand you. Are you telling me that
running VACUUM FULL ANALYZE is weird ? Or do you mean another thing ?
Finally, I've found another way : I've build a MATERIALIZED VIEW that
stores the initial (CHAR(1) of both people and organizations, with an
index on that column. I get excellent results :
Unique (cost=0.00..290.34 rows=1117 width=5) (actual time=0.52..267.38
rows=39 loops=1)
-> Index Scan using idx_mview_initials on mview_contacts
(cost=0.00..262.42 rows=11167 width=5) (actual time=0.51..172.15
rows=11167 loops=1)
Total runtime: 267.81 msec
(3 rows)
So, that's a better runtime :-)
Thanks for your help :-)
-------------------------------------
Bruno BAGUETTE - pgsql-ml(at)baguette(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2004-02-08 05:36:20 | Re: RE : RE : Increase performance of a UNION query that |
Previous Message | Karl Denninger | 2004-02-07 16:10:52 | Re: Why is query selecting sequential? |