pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Smith <chris@interspire.com>
To: pgsql-performance@postgresql.org
Subject: simple query join
Date: Mon, 8 Mar 2004 17:57:09 +1100
Message-ID: <002201c404da$93d50790$0d00a8c0@chris> (raw)

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.
 

view thread (4+ messages)  latest in thread

Message-ID: <002201c404da$93d50790$0d00a8c0@chris>
Permalink:  ../002201c404da$93d50790$0d00a8c0@chris/
Also on:    postgresql.org/message-id/002201c404da$93d50790$0d00a8c0@chris

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: chris@interspire.com
  Subject: Re: simple query join
  In-Reply-To: <002201c404da$93d50790$0d00a8c0@chris>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox