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.
*************************************