pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RE : 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