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 C2FE8D1D18D for ; Mon, 8 Mar 2004 07:12:53 +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 06333-08 for ; Mon, 8 Mar 2004 03:12:53 -0400 (AST) Received: from gizmo04bw.bigpond.com (gizmo04bw.bigpond.com [144.140.70.14]) by svr1.postgresql.org (Postfix) with SMTP id 9F80BD1B530 for ; Mon, 8 Mar 2004 03:12:49 -0400 (AST) Received: (qmail 12553 invoked from network); 8 Mar 2004 07:08:04 -0000 Received: from unknown (HELO bwmam07.bigpond.com) (144.135.24.87) by gizmo04bw.bigpond.com with SMTP; 8 Mar 2004 07:08:04 -0000 Received: from cpe-203-45-192-2.qld.bigpond.net.au ([203.45.192.2]) by bwmam07.bigpond.com(MAM REL_3_4_2 62/1158095) with SMTP id 1158095; Mon, 08 Mar 2004 17:12:47 +1000 Message-ID: <006901c404dc$a43af520$fd08a8c0@steve> From: "Steven Butler" To: "Chris Smith" , References: <002201c404da$93d50790$0d00a8c0@chris> Subject: Re: simple query join Date: Mon, 8 Mar 2004 17:11:56 +1000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0066_01C40530.75C6FA00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/95 X-Sequence-Number: 5951 This is a multi-part message in MIME format. ------=_NextPart_000_0066_01C40530.75C6FA00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MessageLooks to me like it's because your assetid is varchar in one table a= nd 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 pla= nner 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 =3D p.assetid AND p.permission =3D '1' AND p.access =3D '1' AND p.userid =3D '0'; QUERY PLAN -------------------------------------------------------------------------= ------------------------------------------------------- Nested Loop (cost=3D0.00..4743553.10 rows=3D2582 width=3D27) (actual ti= me=3D237.91..759310.60 rows=3D11393 loops=3D1) Join Filter: (("inner".assetid)::text =3D ("outer".assetid)::text) -> Seq Scan on sq_asset_permission p (cost=3D0.00..1852.01 rows=3D22= 88 width=3D23) (actual time=3D0.06..196.90 rows=3D12873 loops=3D1) Filter: ((permission =3D 1) AND ("access" =3D '1'::bpchar) AND (= userid =3D '0'::character varying)) -> Seq Scan on sq_asset a (cost=3D0.00..1825.67 rows=3D16467 width= =3D4) (actual time=3D1.40..29.09 rows=3D16467 loops=3D12873) Total runtime: 759331.85 msec (6 rows) ------=_NextPart_000_0066_01C40530.75C6FA00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
Looks to me like it's because your assetid= is=20 varchar in one table and an integer in the other table.  AFAIK, PG is= =20 unable to use an index join when the join types are different.  The qu= ery=20 plan shows it is doing full table scans of both tables.
 
Change both to varchar or both to integer = and see=20 what happens.
 
Also make sure to vacuum analyze the table= s=20 regularly to keep the query planner statistics up-to-date.
 
Cheers,
Steve Butler
 assetid      =   |=20 integer           =          =20 | not null default 0
Indexes: sq_asset_pkey primary key btree=20 (assetid)
 assetid    | character varying(255) | not n= ull=20 default '0'
EXPLAIN ANALYZE SELECT p.*
FROM sq_asset a, sq_asset_permissi= on=20 p
WHERE a.assetid =3D p.assetid
AND p.permission =3D '1'
AND p.a= ccess =3D=20 '1'
AND p.userid =3D=20 '0';
           = ;            &n= bsp;            = ;            &n= bsp;         =20 QUERY=20 PLAN
-----------------------------------------------------------------= ---------------------------------------------------------------
 Ne= sted=20 Loop  (cost=3D0.00..4743553.10 rows=3D2582 width=3D27) (actual=20 time=3D237.91..759310.60 rows=3D11393 loops=3D1)
   Join Fil= ter:=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'::c= haracter=20 varying))
   ->  Seq Scan on sq_asset a =20 (cost=3D0.00..1825.67 rows=3D16467 width=3D4) (actual time=3D1.40..29.09 = rows=3D16467=20 loops=3D12873)
 Total runtime: 759331.85 msec
(6=20 rows)
------=_NextPart_000_0066_01C40530.75C6FA00--