pankaj naug <pankajnaug@yahoo.com> writes:
> Both servers have same version with same postgresql.conf. But the time taken by both the servers with subselect are
waydifferent. the loops and the query time are completely different. both servers have same indexes.
Evidently one has been analyzed much more recently than the other,
because the estimated row counts are wildly different.
You didn't say which PG version this is, but I gather that it's pre-7.4,
which means that the performance of IN (SELECT ...) is generally going
to be awful. Either rewrite as a join or update to 7.4 or later.
regards, tom lane