From: Tomas Vondra <tv@fuzzy.cz>
To: Jayadevan <Jayadevan.Maymala@ibsplc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance issue
Date: Sun, 4 Sep 2011 22:18:15 +0200
Message-ID: <0b5904ed666013250682b895d8266324.squirrel@sq.gransy.com> (raw)
In-Reply-To: <1315159591749-4768047.post@n5.nabble.com>
References: <OF6B813C80.12B50253-ON652578FD.00310FC3-652578FD.00317C74@ibsplc.com>
<4E6345520200002500040BD6@gw.wicourts.gov>
<12017.1315149499@sss.pgh.pa.us>
<1315159591749-4768047.post@n5.nabble.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, Jayadevan.Maymala@ibsplc.com
Subject: Re: Query performance issue
In-Reply-To: <0b5904ed666013250682b895d8266324.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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