Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] poor performance in migrated database

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Carlos Lopez <chlopezl(at)yahoo(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>,pgsql-performance(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [PERFORM] poor performance in migrated database
Date: 2004-11-08 23:57:58
Message-ID: 12047.1099958278@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
Carlos Lopez <chlopezl(at)yahoo(dot)com> writes:
> This is one of the queries that work,and is the first
> in a 4 level nested query....

Do you really need UNION (as opposed to UNION ALL) in this query?
The EXPLAIN shows that almost half the runtime is going into the
sort/uniq to eliminate duplicates ... and according to the row
counts, there are no duplicates, so it's wasted effort.

I looked at your schema and saw an awful lot of SELECT DISTINCTs
that looked like they might not be necessary, too.  But I'm not
willing to crawl through 144 views with no information about
which ones are causing you problems.  What's a typical query
that you are unsatisfied with the performance of?

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: John MeinelDate: 2004-11-09 00:01:30
Subject: Re: vacuum analyze slows sql query
Previous:From: patrick ~Date: 2004-11-08 23:19:51
Subject: Re: vacuum analyze slows sql query

pgsql-admin by date

Next:From: Tom LaneDate: 2004-11-09 00:01:33
Subject: Re: using psql copy command
Previous:From: David BearDate: 2004-11-08 23:47:43
Subject: using psql \copy command

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group