X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id CCF23310A2D for ; Mon, 30 Jun 2003 18:00:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 37727-06 for ; Mon, 30 Jun 2003 14:59:59 -0300 (ADT) Received: from root.iarchives.com (unknown [64.90.195.10]) by svr1.postgresql.org (Postfix) with ESMTP id 2CA753109F3 for ; Mon, 30 Jun 2003 14:59:59 -0300 (ADT) Received: from zucchini ([192.168.100.34]) by root.iarchives.com with Microsoft SMTPSVC(5.0.2195.5329); Mon, 30 Jun 2003 11:58:37 -0600 Message-ID: <032101c33f31$0d239910$2264a8c0@zucchini> From: "Robert Wille" To: Subject: Query planner plans very inefficient plans Date: Mon, 30 Jun 2003 11:57:20 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_031E_01C33EFE.C28187F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-OriginalArrivalTime: 30 Jun 2003 17:58:37.0693 (UTC) FILETIME=[3B4682D0:01C33F31] X-Archive-Number: 200306/207 X-Sequence-Number: 2291 This is a multi-part message in MIME format. ------=_NextPart_000_031E_01C33EFE.C28187F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a number of very common queries that the optimizer plans a very inef= ficient plan. I vacuum hourly. I'm wondering what I can do to make the quer= ies faster. Here are the relevant tables: create table image( imageid integer not null, /* The image's ID */ containerid integer not null, /* The container that owns it */ name varchar(120) not null, /* Its name */ state bigint not null default 0, /* Its state */ primary key (imageid), unique (containerid, name) /* All images in a container must be uni= quely named */ ); create table ancestry( containerid integer not null, /* The container that has an ancestor= */ ancestorid integer not null, /* The ancestor of the container */ unique (containerid, ancestorid), unique (ancestorid, containerid) ); I have somewhere around 3M rows in the image table, and 37K rows in the anc= estry table. The following is representative of some of the common queries = I issue: select * from image natural join ancestry where ancestorid=3D1000000 and (s= tate & 7::bigint) =3D 0::bigint; When I ask postgres to EXPLAIN it, I get the following: Merge Join (cost=3D81858.22..81900.60 rows=3D124 width=3D49) -> Sort (cost=3D81693.15..81693.15 rows=3D16288 width=3D41) -> Seq Scan on image (cost=3D0.00..80279.17 rows=3D16288 width=3D= 41) -> Sort (cost=3D165.06..165.06 rows=3D45 width=3D8) -> Index Scan using ancestry_ancestorid_key on ancestry (cost=3D0= .00..163.83 rows=3D45 width=3D8) It appears to me that the query executes as follows: 1. Scan every row in the image table to find those where (state & 7::bigint= ) =3D 0::bigint 2. Sort the results 3. Use an index on ancestry to find rows where ancestorid=3D1000000 4. Sort the results 5. Join the two It seems to me that if this query is going to return a small percentage of = the rows (which is the common case), it could be done much faster by first = joining (all columns involved in the join are indexed), and then by applyin= g the (state & 7::bigint) =3D 0::bigint constraint to the results. Similarly, when I update, I get the following: explain update image set state=3D0 from ancestry where ancestorid=3D1000000= and ancestry.containerid=3Dimage.containerid and (state & 7::bigint) =3D 0= ::bigint; NOTICE: QUERY PLAN: Merge Join (cost=3D81841.92..81884.30 rows=3D124 width=3D43) -> Sort (cost=3D81676.74..81676.74 rows=3D16288 width=3D39) -> Seq Scan on image (cost=3D0.00..80279.17 rows=3D16288 width=3D= 39) -> Sort (cost=3D165.19..165.19 rows=3D45 width=3D4) -> Index Scan using ancestry_ancestorid_key on ancestry (cost=3D0= .00..163.95 rows=3D45 width=3D4) How can I avoid the sequential scan of the entire image table (i.e. how can= I get it to perform the join first)? Thanks in advance. Robert Wille ------=_NextPart_000_031E_01C33EFE.C28187F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have a number of very common queries tha= t the=20 optimizer plans a very inefficient plan. I vacuum hourly. I'm wondering wha= t I=20 can do to make the queries faster.
 
Here are the relevant tables:
 
create table image(
    = imageid=20 integer not null,     /* The image's ID */
<= /DIV>
    containerid integer not= =20 null,     /* The container that owns it=20 */
    name varchar(120) not=20 null,     /* Its name */
    state bigint not null d= efault=20 0,    /* Its state */
    primary key= =20 (imageid),
    unique (containerid,=20 name)     /* All images in a container must be uni= quely=20 named */
);
 
create table ancestry(
  &nbs= p;=20 containerid integer not null,     /* The container= that=20 has an ancestor */
    ancestorid integer not=20 null,     /* The ancestor of the container=20 */
    unique (containerid, ancestorid),
  &= nbsp;=20 unique (ancestorid, containerid)
);
 
I have somewhere around 3M rows in the ima= ge table,=20 and 37K rows in the ancestry table. The following is representative of some= of=20 the common queries I issue:
 
select * from image natural join ancestry = where=20 ancestorid=3D1000000 and (state & 7::bigint) =3D 0::bigint;
 
When I ask postgres to EXPLAIN it, I get t= he=20 following:
 
Merge Join  (cost=3D81858.22..81900.6= 0 rows=3D124=20 width=3D49)
  ->  Sort  (cost=3D81693.15..81693.15 row= s=3D16288=20 width=3D41)
        ->  Seq S= can on=20 image  (cost=3D0.00..80279.17 rows=3D16288 width=3D41)
  ->=  =20 Sort  (cost=3D165.06..165.06 rows=3D45=20 width=3D8)
        ->  Index = Scan=20 using ancestry_ancestorid_key on ancestry  (cost=3D0.00..163.83 rows= =3D45=20 width=3D8)
 
It appears to me that the query executes a= s=20 follows:
 
1. Scan every row in the image table to fi= nd those=20 where (state & 7::bigint) =3D 0::bigint
2. Sort the results
3. Use an index on ancestry to find rows w= here=20 ancestorid=3D1000000
4. Sort the results
5. Join the two
 
It seems to me that if this query is going= to=20 return a small percentage of the rows (which is the common case), it could = be=20 done much faster by first joining (all columns involved in the join are=20 indexed), and then by applying the (state & 7::bigint) =3D 0::bigint=20 constraint to the results.
 
Similarly, when I update, I get the=20 following:
 
explain update image set state=3D0 from an= cestry=20 where ancestorid=3D1000000 and ancestry.containerid=3Dimage.containerid and= (state=20 & 7::bigint) =3D 0::bigint;
 
NOTICE:  QUERY PLAN:
 
Merge Join  (cost=3D81841.92..81884.3= 0 rows=3D124=20 width=3D43)
  ->  Sort  (cost=3D81676.74..81676.74 row= s=3D16288=20 width=3D39)
        ->  Seq S= can on=20 image  (cost=3D0.00..80279.17 rows=3D16288 width=3D39)
  ->=  =20 Sort  (cost=3D165.19..165.19 rows=3D45=20 width=3D4)
        ->  Index = Scan=20 using ancestry_ancestorid_key on ancestry  (cost=3D0.00..163.95 rows= =3D45=20 width=3D4)
 
How can I avoid the sequential scan of the= entire=20 image table (i.e. how can I get it to perform the join first)?

Thanks in advance.
 
Robert Wille
 
------=_NextPart_000_031E_01C33EFE.C28187F0--