X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7E19ED1B54E for ; Mon, 8 Mar 2004 06:57:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05857-09 for ; Mon, 8 Mar 2004 02:57:15 -0400 (AST) Received: from dnscache2.syd.nexon.com.au (dnscache2.syd.nexon.com.au [210.215.48.100]) by svr1.postgresql.org (Postfix) with ESMTP id D3490D1B562 for ; Mon, 8 Mar 2004 02:57:12 -0400 (AST) Received: from chris ([210.215.130.98]) by dnscache2.syd.nexon.com.au (8.12.8p1/8.12.8) with ESMTP id i283tt9g020952; Mon, 8 Mar 2004 14:55:55 +1100 (EST) (envelope-from chris@interspire.com) From: "Chris Smith" To: Subject: simple query join Date: Mon, 8 Mar 2004 17:57:09 +1100 Message-ID: <002201c404da$93d50790$0d00a8c0@chris> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0023_01C40536.C7457F90" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/94 X-Sequence-Number: 5950 This is a multi-part message in MIME format. ------=_NextPart_000_0023_01C40536.C7457F90 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi all, I've got what should be a relatively simple join between two tables that is taking forever and I can't work out why. Version 7.3.4RH. It can't be upgraded because the system is kept in sync with RedHat Enterprise (using up2date). Not my system otherwise I'd do that :( Database has been 'vacuum analyze'd. blah=> \d sq_asset; Table "public.sq_asset" Column | Type | Modifiers ----------------+-----------------------------+------------------------- - type_code | character varying(100) | not null version | character varying(20) | not null default '0.0.0' name | character varying(255) | not null default '' short_name | character varying(255) | not null default '' status | integer | not null default 1 languages | character varying(50) | not null default '' charset | character varying(50) | not null default '' force_secure | character(1) | not null default '0' created | timestamp without time zone | not null updated | timestamp without time zone | not null created_userid | character varying(255) | not null default '0' updated_userid | character varying(255) | not null default '0' assetid | integer | not null default 0 Indexes: sq_asset_pkey primary key btree (assetid) blah=> select count(*) from sq_asset; count ------- 16467 (1 row) blah=> \d sq_asset_permission; Table "public.sq_asset_permission" Column | Type | Modifiers ------------+------------------------+---------------------- permission | integer | not null default 0 access | character(1) | not null default '0' assetid | character varying(255) | not null default '0' userid | character varying(255) | not null default '0' Indexes: sq_asset_permission_pkey primary key btree (assetid, userid, permission) "sq_asset_permission_access" btree ("access") "sq_asset_permission_assetid" btree (assetid) "sq_asset_permission_permission" btree (permission) "sq_asset_permission_userid" btree (userid) blah=> select count(*) from sq_asset_permission; count ------- 73715 (1 row) EXPLAIN ANALYZE SELECT p.* FROM sq_asset a, sq_asset_permission p WHERE a.assetid = p.assetid AND p.permission = '1' AND p.access = '1' AND p.userid = '0'; QUERY PLAN ------------------------------------------------------------------------ -------------------------------------------------------- Nested Loop (cost=0.00..4743553.10 rows=2582 width=27) (actual time=237.91..759310.60 rows=11393 loops=1) Join Filter: (("inner".assetid)::text = ("outer".assetid)::text) -> Seq Scan on sq_asset_permission p (cost=0.00..1852.01 rows=2288 width=23) (actual time=0.06..196.90 rows=12873 loops=1) Filter: ((permission = 1) AND ("access" = '1'::bpchar) AND (userid = '0'::character varying)) -> Seq Scan on sq_asset a (cost=0.00..1825.67 rows=16467 width=4) (actual time=1.40..29.09 rows=16467 loops=12873) Total runtime: 759331.85 msec (6 rows) It's a straight join so I can't see why it would be this slow.. The tables are pretty small too. Thanks for any suggestions :) Chris. ------=_NextPart_000_0023_01C40536.C7457F90 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Message
Hi=20 all,
 
I've go= t what=20 should be a relatively simple join between two tables that is taking foreve= r and=20 I can't work out why.
 
Version= =20 7.3.4RH.
 
It can'= t be=20 upgraded because the system is kept in sync with RedHat Enterprise (using= =20 up2date). Not my system otherwise I'd do that :(
 
Databas= e has been=20 'vacuum analyze'd.
 
blah=3D= > \d=20 sq_asset;
          &n= bsp;            = ; =20 Table "public.sq_asset"
    =20 Column    =20 |           =20 Type            = ;=20 |       =20 Modifiers        =20
----------------+-----------------------------+------------------------= --
 type_code     =20 | character varying(100)      | not=20 null
 version        | character= =20 varying(20)       | not null default=20 '0.0.0'
 name         =  =20 | character varying(255)      | not null default= =20 ''
 short_name     | character=20 varying(255)      | not null default=20 ''
 status         |=20 integer           &n= bsp;        =20 | not null default 1
 languages      |=20 character varying(50)       | not null defaul= t=20 ''
 charset        | character= =20 varying(50)       | not null default=20 ''
 force_secure   |=20 character(1)          &nb= sp;    =20 | not null default=20 '0'
 created        | timestamp= =20 without time zone | not=20 null
 updated        | timestamp= =20 without time zone | not null
 created_userid | character=20 varying(255)      | not null default=20 '0'
 updated_userid | character=20 varying(255)      | not null default=20 '0'
 assetid        |=20 integer           &n= bsp;        =20 | not null default 0
Indexes: sq_asset_pkey primary key btree=20 (assetid)
blah=3D= > select=20 count(*) from sq_asset;
 count
-------
 16467
(1=20 row)
 
 
blah=3D= > \d=20 sq_asset_permission;
        &nb= sp;   =20 Table "public.sq_asset_permission"
   Column  =20 |         =20 Type         =20 |      Modifiers     &nbs= p;=20
------------+------------------------+----------------------
 p= ermission=20 |=20 integer           &n= bsp;   =20 | not null default 0
 access     |=20 character(1)           | = not=20 null default '0'
 assetid    | character varying(255= ) |=20 not null default '0'
 userid     | character=20 varying(255) | not null default '0'
Indexes: sq_asset_permission_pkey pr= imary=20 key btree (assetid, userid, permission)
   =20 "sq_asset_permission_access" btree ("access")
   =20 "sq_asset_permission_assetid" btree (assetid)
   =20 "sq_asset_permission_permission" btree (permission)
   = =20 "sq_asset_permission_userid" btree (userid)
blah=3D= > select=20 count(*) from sq_asset_permission;
 count=20
-------
 73715
(1 row)
 
EXPLAIN= ANALYZE=20 SELECT p.*
FROM sq_asset a, sq_asset_permission p
WHERE a.assetid =3D= =20 p.assetid
AND p.permission =3D '1'
AND p.access =3D '1'
AND p.user= id =3D=20 '0';
           &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;         =20 QUERY=20 PLAN
-------------------------------------------------------------------= -------------------------------------------------------------
 Nest= ed=20 Loop  (cost=3D0.00..4743553.10 rows=3D2582 width=3D27) (actual=20 time=3D237.91..759310.60 rows=3D11393 loops=3D1)
   Join Filte= r:=20 (("inner".assetid)::text =3D ("outer".assetid)::text)
   ->=  =20 Seq Scan on sq_asset_permission p  (cost=3D0.00..1852.01 rows=3D2288 w= idth=3D23)=20 (actual time=3D0.06..196.90 rows=3D12873=20 loops=3D1)
         Filter:=20 ((permission =3D 1) AND ("access" =3D '1'::bpchar) AND (userid =3D '0'::cha= racter=20 varying))
   ->  Seq Scan on sq_asset a =20 (cost=3D0.00..1825.67 rows=3D16467 width=3D4) (actual time=3D1.40..29.09 ro= ws=3D16467=20 loops=3D12873)
 Total runtime: 759331.85 msec
(6=20 rows)
 
It's a = straight=20 join so I can't see why it would be this slow.. The tables are pretty small= =20 too.
 
Thanks = for any=20 suggestions :)
 
Chris.
 
------=_NextPart_000_0023_01C40536.C7457F90--