Re: Query performance issue

On 4 Září 2011, 20:06, Jayadevan wrote:
> I don't think I understood all that. Anyway, is there a way to fix this -
> either by rewriting the query or by creating an index? The output does
> match
> what I am expecting. It does take more than 10 times the time taken by
> Oracle for the same result, with PostgreSQL taking more than 20 minutes. I
> am sort of stuck on this since this query does get executed often. By the
> way, changing the filter from FAMNAM to GIVENNAME fetches results in 90
> seconds. Probably there is a difference in the cardinality of values in
> these 2 columns.

Tom Lane explained why sort produces more rows (2673340321) than it gets
on the input (1121), or why it seems like that - it's a bit complicated
because of the merge join.

I'd try to increase statistics target - it's probably 100, change it to
1000, run ANALYZE and try the query (it may improve the plan without the
need to mess with the query).

If that does not help, you'll have to change the query probably. The
problem is the explain analyze you've provided
(http://explain.depesz.com/s/MY1) does not match the query from your
yesterday's post so we can't really help with it. I do have some ideas of
how to change the query, but it's really wild guessing without the query
plan.

Tomas