Re: RE : Increase performance of a UNION query that thakes

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?

> > Also, what do you have sort_mem set to?
>
> [root(at)levure data]# cat postgresql.conf | grep sort_mem
> sort_mem = 6144 # min 64, size in KB
>
> Do you think I should increase that value ?

Hmm, I'd expect that the sort would fit in that space in general. If you
want to try different values, you can set sort_mem from psql rather than
changing the configuration file.

----

On my machine the index does actually help, although I needed to lower
random_page_cost a little from its default of 4 to get it to use it
preferentially, but I'm also getting times about 1/3 of yours (and my
machine is pretty poor) so I think I may not have data that matches yours
very well.