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 5E86FD1C4EB for ; Mon, 8 Mar 2004 21:43:55 +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 28958-01 for ; Mon, 8 Mar 2004 17:43:49 -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 9DFE5D1BB93 for ; Mon, 8 Mar 2004 17:43:45 -0400 (AST) Received: from chris ([210.215.130.98]) by dnscache2.syd.nexon.com.au (8.12.8p1/8.12.8) with ESMTP id i28IgT9g056479 for ; Tue, 9 Mar 2004 05:42:30 +1100 (EST) (envelope-from chris@interspire.com) From: "Chris Smith" To: Subject: Re: simple query join Date: Tue, 9 Mar 2004 08:43:40 +1100 Message-ID: <000501c40556$6c25cf00$0d00a8c0@chris> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C405B2.9F964700" 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 In-Reply-To: <006901c404dc$a43af520$fd08a8c0@steve> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/104 X-Sequence-Number: 5960 This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C405B2.9F964700 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Eek. Casting both to varchar makes it super quick so I'll fix up the tables. Added to the list of things to check for next time... On a side note - I tried it with 7.4.1 on another box and it handled it ok. Thanks again :) Chris. -----Original Message----- From: Steven Butler [mailto:stevenb@kjross.com.au] Sent: Monday, March 08, 2004 6:12 PM To: Chris Smith; pgsql-performance@postgresql.org Subject: Re: [PERFORM] simple query join Looks to me like it's because your assetid is varchar in one table and an integer in the other table. AFAIK, PG is unable to use an index join when the join types are different. The query plan shows it is doing full table scans of both tables. Change both to varchar or both to integer and see what happens. Also make sure to vacuum analyze the tables regularly to keep the query planner statistics up-to-date. Cheers, Steve Butler assetid | integer | not null default 0 Indexes: sq_asset_pkey primary key btree (assetid) assetid | character varying(255) | not null default '0' 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) ------=_NextPart_000_0006_01C405B2.9F964700 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Message
Eek.=20 Casting both to varchar makes it super quick so = I'll=20 fix up the tables.
 
Added to the list of things to check for next=20 time...
 
On a=20 side note - I tried it with 7.4.1 on another box and it handled it=20 ok.
 
Thanks again :)
 
Chris.
 
-----Original Message-----
From: Steven = Butler=20 [mailto:stevenb@kjross.com.au]
Sent: Monday, March 08, 2004 6:= 12=20 PM
To: Chris Smith;=20 pgsql-performance@postgresql.org
Subject: Re: [PERFORM] simple = query=20 join

Looks to me like it's because your asset= id is=20 varchar in one table and an integer in the other table.  AFAIK, PG i= s=20 unable to use an index join when the join types are different.  The = query=20 plan shows it is doing full table scans of both tables.
 
Change both to varchar or both to intege= r and see=20 what happens.
 
Also make sure to vacuum analyze the tab= les=20 regularly to keep the query planner statistics up-to-date.
 
Cheers,
Steve Butler
 assetid     &nbs= p; =20 |=20 integer          &nbs= p;         =20 | not null default 0
Indexes: sq_asset_pkey primary key btree=20 (assetid)
 assetid    | character varying(255) | not= null=20 default '0'
EXPLAIN ANALYZE SELECT p.*
FROM sq_asset a,= =20 sq_asset_permission p
WHERE a.assetid =3D p.assetid
AND p.permiss= ion =3D=20 '1'
AND p.access =3D '1'
AND p.userid =3D=20 '0';
          &nb= sp;            =             &nb= sp;            =           =20 QUERY=20 PLAN
---------------------------------------------------------------= -----------------------------------------------------------------
 = Nested=20 Loop  (cost=3D0.00..4743553.10 rows=3D2582 width=3D27) (actual=20 time=3D237.91..759310.60 rows=3D11393 loops=3D1)
   Join F= ilter:=20 (("inner".assetid)::text =3D ("outer".assetid)::text)
  = =20 ->  Seq Scan on sq_asset_permission p  (cost=3D0.00..1852.= 01=20 rows=3D2288 width=3D23) (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':= :character=20 varying))
   ->  Seq Scan on sq_asset a =20 (cost=3D0.00..1825.67 rows=3D16467 width=3D4) (actual time=3D1.40..29.0= 9 rows=3D16467=20 loops=3D12873)
 Total runtime: 759331.85 msec
(6=20 rows)
------=_NextPart_000_0006_01C405B2.9F964700--