Re: Query runs forever after upgrading to 9.3

I fixed the issue by creating indexes for temporary tables before running query:

create index on tempalgsemu(toode);
create index on temphetkes(toode);
SELECT * FROM toode o WHERE exists (SELECT toode FROM tempkaive i where o.toode = i.toode ) OR
EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode = i.toode);

Is this best fix ?

Andrus.

From: Andrus
Sent: Monday, September 23, 2013 6:06 PM
To: Jayadevan M
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Query runs forever after upgrading to 9.3

Hi,

thank you.
>Could you please post EXPLAIN for that query?
As recommend I changed query to use exists :

SELECT * FROM toode o WHERE exists (SELECT toode FROM tempkaive i where o.toode = i.toode ) OR
EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode = i.toode)

It still hangs in same way. This query explain is:

Seq Scan on toode o (cost=0.00..172913763.23 rows=382319 width=1681)
Filter: ((SubPlan 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3))
SubPlan 1
-> Seq Scan on tempkaive i (cost=0.00..4566.52 rows=14 width=0)
Filter: (o.toode = toode)
SubPlan 2
-> Seq Scan on tempalgsemu i_1 (cost=0.00..348.98 rows=27 width=0)
Filter: (o.toode = toode)
SubPlan 3
-> Seq Scan on tempalgsemu i_2 (cost=0.00..335.58 rows=5358 width=84)

> How 'fat' are the temporary tables - just a couple of columns or really wide?

tempalgsemu has 14 columns
tempkaive has 31 columns

structures are below. Too structure was posted in separate letter.

Andrus.

tempalgsemu :

Field Field Name Type Width Dec Index Collate Nulls Next Step
1 ID Integer 4 Yes
2 LAONR Numeric 4 Yes
3 KUUPAEV Date 8 Yes
4 KELLAAEG Character 5 Yes
5 OSAK Character 10 Yes
6 TOODE Character 20 Yes
7 PARTII Character 15 Yes
8 KOGUS Numeric 14 4 Yes
9 HIND Numeric 17 5 Yes
10 KULUM Numeric 17 5 Yes
11 TEGKOGUS Numeric 14 4 Yes
12 STKUUPAEV Date 8 Yes
13 KLIENT Character 12 Yes
14 MASIN Character 5 Yes
** Total ** 156

tempkaive

Field Field Name Type Width Dec Index Collate Nulls Next Step
1 DOKTYYP Character 1 Yes
2 DOKUMNR Integer 4 Yes
3 KUUPAEV Date 8 Yes
4 KELLAAEG Character 5 Yes
5 RAHA Character 3 Yes
6 EXCHRATE Numeric 16 8 Yes
7 KLIENT Character 12 Yes
8 ID Integer 4 Yes
9 TOODE Character 20 Yes
10 PARTII Character 15 Yes
11 KULUPARTII Character 15 Yes
12 KOGPAK Numeric 11 4 Yes
13 KOGUS Numeric 14 4 Yes
14 HIND Numeric 17 5 Yes
15 MYYGIKOOD Character 4 Yes
16 YHIK Character 6 Yes
17 NIMETUS Character 50 Yes
18 HINNAK Character 5 Yes
19 TKOGUS Numeric 20 6 Yes
20 UKOGUS Numeric 20 6 Yes
21 KUSTPARTII Character 15 Yes
22 KAUBASUMMA Numeric 17 5 Yes
23 KULUOBJEKT Character 10 Yes
24 FIFOEXPENS Logical 1 Yes
25 KULUM Numeric 17 5 Yes
26 SKAUBASUMM Numeric 17 5 Yes
27 ST Numeric 3 Yes
28 VM Numeric 3 Yes
29 VKAUBASUMM Numeric 20 6 Yes
30 YKSUS Character 10 Yes
31 SIHTYKSUS Character 10 Yes
** Total ** 378

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