Re: Query runs forever after upgrading to 9.3

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(at)hot(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>