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

slow query

From: Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)org>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: slow query
Date: 2003-02-21 16:30:53
Message-ID: 993DBE5B4D02194382EC8DF8554A5273033583@postoffice.waterford.org (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,
I am having problems with my master database now. It used to work
extremely good just two days ago, but then I started playing with
adding/dropping schemas and added another database and performance went
down.

I also have a replica database on the same server and when I run the
same query on it, it runs good.
Interestingly the planner statistics for this query are the same on the
master and replica databases.
However, query execution on the master database is about 4min. and on
the replica database is 6 sec.!

I VACUUM ANALYZED both databases and made sure they have same indexes on
the tables.
I don't know where else to look, but here are the schemas I have on the
master and replica database. The temp schemas must be the ones that I
created and then dropped.
master=# select * from pg_namespace;
nspname | nspowner | nspacl
------------+----------+--------
pg_catalog | 1 | {=U}
pg_toast | 1 | {=}
public | 1 | {=UC}
pg_temp_1 | 1 | 
pg_temp_3 | 1 | 
pg_temp_10 | 1 | 
pg_temp_28 | 1 | 
replica=> select * from pg_namespace;
nspname | nspowner | nspacl 
------------+----------+--------
pg_catalog | 1 | {=U}
pg_toast | 1 | {=}
public | 1 | {=UC}
pg_temp_1 | 1 | 
pg_temp_39 | 1 | 
india | 105 | 

Here is the query:
SELECT * FROM media m, speccharacter c 
WHERE m.mediatype IN (SELECT objectid FROM mediatype WHERE
medianame='Audio') 
AND m.mediachar = c.objectid 
AND (m.activity='178746' 
	OR 
		(EXISTS (SELECT ism.objectid 
		FROM intsetmedia ism, set s 
		WHERE ism.set = s.objectid 
		AND ism.media = m.objectid AND s.activity='178746' )
		) 
	OR 
		(EXISTS (SELECT dtrm.objectid 
		FROM dtrowmedia dtrm, dtrow dtr, dtcol dtc, datatable dt

		WHERE dtrm.dtrow = dtr.objectid 
		AND dtrm.media = m.objectid 
		AND dtr.dtcol = dtc.objectid 
		AND dtc.datatable = dt.objectid 
		AND dt.activity = '178746')
		)
	) 
ORDER BY medianame ASC, status DESC;

*************************************

This email may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.  
Any review, copying, printing, disclosure or other use is prohibited.
We reserve the right to monitor email sent through our network.

*************************************


Responses

pgsql-performance by date

Next:From: Andrew SullivanDate: 2003-02-21 16:59:17
Subject: Re: Really bad insert performance: what did I do wrong?
Previous:From: Shridhar Daithankar<shridhar_daithankar@persistent.co.in>Date: 2003-02-21 15:51:55
Subject: Re: Really bad insert performance: what did I do wrong?

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