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 <kobrule...@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<http://www.postgresql.org/mailpref/pgsql-general> >
