Re: Query runs forever after upgrading to 9.3 - Mailing list pgsql-general
| From | Jayadevan M |
|---|---|
| Subject | Re: Query runs forever after upgrading to 9.3 |
| Date | |
| Msg-id | CAFS1N4it5Af7+RC+u7o1nph3wyD1N22zOSdi0R4R14tfPmFfFw@mail.gmail.com Whole thread Raw |
| In response to | Query runs forever after upgrading to 9.3 ("Andrus" <kobruleht2@hot.ee>) |
| Responses |
Re: Query runs forever after upgrading to 9.3
("Andrus" <kobruleht2@hot.ee>)
|
| List | pgsql-general |
Could you please post EXPLAIN for that query?
How 'fat' are the temporary tables - just a couple of columns or really wide? On Mon, Sep 23, 2013 at 7:08 PM, Andrus <kobruleht2@hot.ee> wrote:
Query
SELECT * FROM toode
WHERE toode in (SELECT toode FROM tempkaive)
OR toode in (SELECT toode FROM tempalgsemu)
stops working after upgrading to 9.3 RTM in Windows from earlier version.
Task Manager shows that postgres.exe process has constantly 13% CPU usage (this is 8 core computer) and private working set memory is 16 MB
PgAdmin shows that this query is running .
toode field type is char(20) and it is toode table primary key.
tempkaive and tempalgsemu are temporary tables created eralier this transaction. They do not have indexes.
toode is real table which has 509873 records .
Probably tempkaive temp table size is bigger that toode table and templalgemu temp table size is smaller than in toode.
How to fix this or find the reason ?
How to rewrite the query so that it works ?
analyze command was executed but problem persists.
I tested it running Postgres 9.3 RTM in 32 bin Windows 7 and 64 bit Windows 2008 R2 servers.
In both cases same problem occurs.
Only single user is using database and only this query is running.
Locks window shows:
7840 toy 53749 admin 7/13375 AccessShareLock Yes 2013-09-23 15:57:08+03 SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840 toy 53652 admin 7/13375 AccessShareLock Yes 2013-09-23 15:57:08+03 SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840 toy 54605 admin 7/13375 AccessShareLock Yes 2013-09-23 15:57:08+03 SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840 toy 54608 admin 7/13375 AccessShareLock Yes 2013-09-23 15:57:08+03 SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840 toy 49799 admin 7/13375 AccessShareLock Yes 2013-09-23 15:57:08+03 SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840 admin 7/13375 7/13375 ExclusiveLock Yes 2013-09-23 15:57:08+03 SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840 toy 53750 admin 7/13375 AccessShareLock Yes 2013-09-23 15:57:08+03 SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: