Re: Query runs forever after upgrading to 9.3 - Mailing list pgsql-general

From Andrus
Subject Re: Query runs forever after upgrading to 9.3
Date
Msg-id 5C159DB297194492B417CED98F808325@dell2
Whole thread Raw
In response to Query runs forever after upgrading to 9.3  ("Andrus" <kobruleht2@hot.ee>)
List pgsql-general
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
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@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:

Previous
From: bricklen
Date:
Subject: Re: Query runs forever after upgrading to 9.3
Next
From: John DeSoi
Date:
Subject: streaming replication not working