pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRE : RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ?
2+ messages / 2 participants
[nested] [flat]
* RE : RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ?
@ 2004-02-08 00:26 Bruno BAGUETTE <pgsql-ml@baguette.net>
0 siblings, 1 reply; 2+ messages in thread
From: Bruno BAGUETTE @ 2004-02-08 00:26 UTC (permalink / raw)
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; 'Bruno BAGUETTE' <pgsql-ml@baguette.net>; +Cc: 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@baguette.net
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RE : RE : Increase performance of a UNION query that
@ 2004-02-08 05:36 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Bruno BAGUETTE <pgsql-ml@baguette.net>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2004-02-08 05:36 UTC (permalink / raw)
To: Bruno BAGUETTE <pgsql-ml@baguette.net>; +Cc: pgsql-performance
On Sun, 8 Feb 2004, Bruno BAGUETTE wrote:
> > 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 ?
No, I was saying it's wierd that it'd be misestimating to the default
values after a vacuum full analyze.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-08 05:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-08 00:26 RE : RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ? Bruno BAGUETTE <pgsql-ml@baguette.net>
2004-02-08 05:36 ` Re: RE : RE : Increase performance of a UNION query that Stephan Szabo <sszabo@megazone.bigpanda.com>
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