X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 57E68475AEC for ; Fri, 14 Feb 2003 19:08:33 -0500 (EST) Received: from imo-m09.mx.aol.com (imo-m09.mx.aol.com [64.12.136.164]) by postgresql.org (Postfix) with ESMTP id 0161A475F80 for ; Fri, 14 Feb 2003 19:08:30 -0500 (EST) Received: from SLetovsky@aol.com by imo-m09.mx.aol.com (mail_out_v34.21.) id 2.27.394f051e (4529); Fri, 14 Feb 2003 19:07:49 -0500 (EST) From: SLetovsky@aol.com Message-ID: <27.394f051e.2b7eded5@aol.com> Date: Fri, 14 Feb 2003 19:07:49 EST Subject: Re: [Gmod-schema] Re: performace problem after VACUUM ANALYZE To: tgl@sss.pgh.pa.us, cain@cshl.org Cc: pgsql-performance@postgresql.org, gmod-schema@lists.sourceforge.net MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="part1_27.394f051e.2b7eded5_boundary" X-Mailer: 8.0 for Windows sub 182 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/90 X-Sequence-Number: 1146 --part1_27.394f051e.2b7eded5_boundary Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Tom, Thanks for your help in this. We have some flexibility in the schema design; we are going through our first PostGres performance testing now. Am I correct in interpreting your comments as saying you believe that if we could lose the OR and the strand constraint PG would probably use the index properly? There is an alternative representation on the table that would do that. Cheers, -Stan In a message dated 2/14/2003 6:22:15 PM Eastern Standard Time, tgl@sss.pgh.pa.us writes: > Subj: [Gmod-schema] Re: [PERFORM] performace problem after VACUUM ANALYZE > Date: 2/14/2003 6:22:15 PM Eastern Standard Time > From: tgl@sss.pgh.pa.us > To: cain@cshl.org > CC: pgsql-performance@postgresql.org, gmod-schema@lists.sourceforge.net > Sent from the Internet > > > > Scott Cain writes: > >Here is the query that is causing the problems: > > >select distinct f.name,fl.nbeg,fl.nend,fl.strand,f.type_id,f.feature_id > > from feature f, featureloc fl > > where > > fl.srcfeature_id = 1 and > > ((fl.strand=1 and fl.nbeg <= 393164 and fl.nend >= 390956) OR > > (fl.strand=-1 and fl.nend <= 393164 and fl.nbeg >= 390956)) and > > f.feature_id = fl.feature_id > > >[ and the index he'd like it to use is ] > > >Index "featureloc_src_strand_beg_end" > > Column | Type > >---------------+---------- > > srcfeature_id | integer > > strand | smallint > > nbeg | integer > > nend | integer > >btree > > After fooling with this I see a couple of problems. One is the > same old cross-datatype-comparison issue that so frequently bites > people: "1" and "-1" are integer constants, and comparing them to > a smallint column isn't an indexable operation. You need casts. > (Or, forget the "optimization" of storing strand as a smallint. > Depending on your row layout, it's quite likely saving you no space > anyway.) > > Problem two is that the optimizer isn't smart enough to recognize that a > query condition laid out in this form should be processed as two > indexscans --- it would possibly have gotten it right if the first index > column had been inside the OR, but not this way. The upshot is that > when you force it to use index featureloc_src_strand_beg_end, it's > actually only using the srcfeature_id column of the index --- which is > slow of course, and also explains why the optimizer doesn't find that > option very attractive. > > I had some success in getting current sources to generate a desirable > plan by doing this: > > regression=# explain select distinct * > regression-# from feature f join featureloc fl on (f.feature_id = > fl.feature_id) where > regression-# ((fl.srcfeature_id = 1 and fl.strand=1::int2 and fl.nbeg <= > 393164 and fl.nend >= 390956) OR > regression(# (fl.srcfeature_id = 1 and fl.strand=-1::int2 and fl.nend <= > 393164 and fl.nbeg >= 390956)); > > Unique (cost=34.79..34.85 rows=5 width=50) > -> Sort (cost=34.79..34.80 rows=5 width=50) > Sort Key: f.name, fl.nbeg, fl.nend, fl.strand > -> Hash Join (cost=9.68..34.73 rows=5 width=50) > Hash Cond: ("outer".feature_id = "inner".feature_id) > -> Seq Scan on feature f (cost=0.00..20.00 rows=1000 width=36) > -> Hash (cost=9.68..9.68 rows=1 width=14) > -> Index Scan using featureloc_src_strand_beg_end, > featureloc_src_strand_beg_end on featureloc fl (cost=0.00..9.68 rows=1 > width=14) > Index Cond: (((srcfeature_id = 1) AND (strand = 1::smallint) > AND (nbeg <= 393164) AND (nend >= 390956)) OR ((srcfeature_id = 1) AND > (strand = -1::smallint) AND (nbeg >= 390956) AND (nend <= 393164))) > Filter: (((srcfeature_id = 1) AND (strand = 1::smallint) AND > (nbeg <= 393164) AND (nend >= 390956)) OR ((srcfeature_id = 1) AND (strand > = -1::smallint) AND (nend <= 393164) AND (nbeg >= 390956))) > (10 rows) > > Shoving the join condition into an explicit JOIN clause is a hack, but > it nicely does the job of keeping the WHERE clause as a pristine > OR-of-ANDs structure, so that the optimizer can hardly fail to notice > that that's the preferred canonical form. > > I would strongly recommend an upgrade to PG 7.3, both on general > principles and because you can actually see what the indexscan condition > is in EXPLAIN's output. Before 7.3 you had to grovel through EXPLAIN > VERBOSE to be sure what was really happening with a multicolumn index. > > regards, tom lane > > > ------------------------------------------------------- > This SF.NET email is sponsored by: FREE SSL Guide from Thawte > are you planning your Web Server Security? Click here to get a FREE > Thawte SSL guide and find the answers to all your SSL security issues. > http://ads.sourceforge.net/cgi-bin/redirect.pl?thaw0026en > _______________________________________________ > Gmod-schema mailing list > Gmod-schema@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/gmod-schema > --part1_27.394f051e.2b7eded5_boundary Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Tom,
       Thanks for your help in this. We have = some flexibility in the schema
design; we are going through our first PostGres performance testing now.
Am I correct in interpreting your comments as saying you believe that
if we could lose the OR and the strand constraint PG would probably
use the index properly? There is an alternative representation on the table=
that would do that.

Cheers, -Stan

In a message dated 2/14/2003 6:22:15 PM Eastern Standard Time, tgl@sss.pgh.= pa.us writes:

Subj: [Gmod-schema] Re: [P= ERFORM] performace problem after VACUUM ANALYZE
Date: 2/14/2003 6:22:15 PM Eastern Standard Time
From: tgl@sss.pgh.pa.us
To: cain@cshl.org
CC: pgsql-performance@= postgresql.org, gm= od-schema@lists.sourceforge.net
Sent from the Internet



Scott Cain <cain@cshl.org> writes:
>Here is the query that is causing the problems:

>select distinct f.name,fl.nbeg,fl.nend,fl.strand,f.type_id,f.feature_id=
>   from feature f, featureloc fl
>   where
>    fl.srcfeature_id =3D 1 and
>    ((fl.strand=3D1  and fl.nbeg <=3D 393164 and= fl.nend >=3D 390956) OR
>    (fl.strand=3D-1 and fl.nend <=3D 393164 and fl.nb= eg >=3D 390956)) and
>    f.feature_id  =3D fl.feature_id

>[ and the index he'd like it to use is ]

>Index "featureloc_src_strand_beg_end"
>   Column    |   Type   >---------------+----------
> srcfeature_id | integer
> strand     | smallint
> nbeg      | integer
> nend      | integer
>btree

After fooling with this I see a couple of problems.  One is the
same old cross-datatype-comparison issue that so frequently bites
people: "1" and "-1" are integer constants, and comparing them to
a smallint column isn't an indexable operation.  You need casts.
(Or, forget the "optimization" of storing strand as a smallint.
Depending on your row layout, it's quite likely saving you no space
anyway.)

Problem two is that the optimizer isn't smart enough to recognize that a
query condition laid out in this form should be processed as two
indexscans --- it would possibly have gotten it right if the first index
column had been inside the OR, but not this way.  The upshot is that when you force it to use index featureloc_src_strand_beg_end, it's
actually only using the srcfeature_id column of the index --- which is
slow of course, and also explains why the optimizer doesn't find that
option very attractive.

I had some success in getting current sources to generate a desirable
plan by doing this:

regression=3D# explain select distinct *
regression-#  from feature f join featureloc fl on (f.feature_id = =3D fl.feature_id) where
regression-#   ((fl.srcfeature_id =3D 1 and fl.strand=3D1::int2&n= bsp; and fl.nbeg <=3D 393164 and fl.nend >=3D 390956) OR
regression(#   (fl.srcfeature_id =3D 1 and fl.strand=3D-1::int2 a= nd fl.nend <=3D 393164 and fl.nbeg >=3D 390956));

Unique  (cost=3D34.79..34.85 rows=3D5 width=3D50)
  ->  Sort  (cost=3D34.79..34.80 rows=3D5 width=3D50)
     Sort Key: f.name, fl.nbeg, fl.nend, fl.strand
     ->  Hash Join  (cost=3D9.68..34.73 ro= ws=3D5 width=3D50)
        Hash Cond: ("outer".feature_id = =3D "inner".feature_id)
        ->  Seq Scan on feature = f  (cost=3D0.00..20.00 rows=3D1000 width=3D36)
        ->  Hash  (cost=3D9= .68..9.68 rows=3D1 width=3D14)
           ->  In= dex Scan using featureloc_src_strand_beg_end, featureloc_src_strand_beg_end= on featureloc fl  (cost=3D0.00..9.68 rows=3D1 width=3D14)
            &nb= sp; Index Cond: (((srcfeature_id =3D 1) AND (strand =3D 1::smallint) AND (n= beg <=3D 393164) AND (nend >=3D 390956)) OR ((srcfeature_id =3D 1) AN= D (strand =3D -1::smallint) AND (nbeg >=3D 390956) AND (nend <=3D 393= 164)))
            &nb= sp; Filter: (((srcfeature_id =3D 1) AND (strand =3D 1::smallint) AND (nbeg = <=3D 393164) AND (nend >=3D 390956)) OR ((srcfeature_id =3D 1) AND (s= trand =3D -1::smallint) AND (nend <=3D 393164) AND (nbeg >=3D 390956)= ))
(10 rows)

Shoving the join condition into an explicit JOIN clause is a hack, but
it nicely does the job of keeping the WHERE clause as a pristine
OR-of-ANDs structure, so that the optimizer can hardly fail to notice
that that's the preferred canonical form.

I would strongly recommend an upgrade to PG 7.3, both on general
principles and because you can actually see what the indexscan condition
is in EXPLAIN's output.  Before 7.3 you had to grovel through EXPLAIN<= BR> VERBOSE to be sure what was really happening with a multicolumn index.

      regards, tom lane


-------------------------------------------------------
This SF.NET email is sponsored by: FREE  SSL Guide from Thawte
are you planning your Web Server Security? Click here to get a FREE
Thawte SSL guide and find the answers to all your  SSL security issues= .
http://ads.sourceforge.net/cgi-bin/redirect.pl?thaw0026en
_______________________________________________
Gmod-schema mailing list
Gmod-schema@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gmod-schema


--part1_27.394f051e.2b7eded5_boundary--