Re: [HACKERS] I feel the need for speed. What am I doing

Dann,

I run 7.2.1

I tried adding DISTINCT on one of my table and I get similar
performance.

set enable_seqscan = 0;
select distinct "RT_REC_KEY", "cnxarraycolumn", "CRC" from a
except
select distinct "RT_REC_KEY", "cnxarraycolumn", "CRC" from b;


Dann Corbit wrote:
>
> > -----Original Message-----
> > From: Jean-Luc Lachance [mailto:jllachan@nsd.ca]
> > Sent: Tuesday, January 07, 2003 2:43 PM
> > To: Tom Lane
> > Cc: Dann Corbit; Nigel J. Andrews;
> > pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] [HACKERS] I feel the need for speed.
> > What am I doing wrong?
> >
> >
> > There is a construct that most people forget for that kind of query:
> >
> > select "RT_REC_KEY", "cnxarraycolumn", "CRC" from a
> > except
> > select "RT_REC_KEY", "cnxarraycolumn", "CRC" from b;
> >
> > simple.
>
> I should have mentioned that I am not using the latest version of
> PostgreSQL.  I am using 7.1.3.  Perhaps this stuff has been repaired in
> newer versions.  Possibly, there is a reason that people forget to use
> it (at least on PostgreSQL 7.1.3):
>
> connxdatasync=> SET enable_seqscan = 0;
> SET VARIABLE
> connxdatasync=>
> connxdatasync=> SELECT a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" FROM
> connxdatasync-> "CNX_DS_53_SIS_STU_OPT_FEE_TB" a
> connxdatasync-> LEFT OUTER JOIN
> connxdatasync-> "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b
> connxdatasync->         ON ( a."RT_REC_KEY" = b."RT_REC_KEY" AND
> a."cnxarraycolumn" = b."cnxarraycolumn")
> connxdatasync-> WHERE b.oid IS NULL ;
>  RT_REC_KEY | cnxarraycolumn | CRC
> ------------+----------------+-----
> (0 rows)
>
> 1:55.12 to complete
>
> connxdatasync=>
> connxdatasync=> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
> "CNX_DS_53_SIS_STU_OPT_FEE_TB" a
> connxdatasync-> except
> connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
> "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b;
>  RT_REC_KEY | cnxarraycolumn | CRC
> ------------+----------------+-----
> (0 rows)
>
> 12:55.25 to complete: More than 6 times slower to complete.
>
> connxdatasync=>
> connxdatasync=> EXPLAIN
> connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
> "CNX_DS_53_SIS_STU_OPT_FEE_TB" a
> connxdatasync-> except
> connxdatasync-> select "RT_REC_KEY", "cnxarraycolumn", "CRC" from
> "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b;
> NOTICE:  QUERY PLAN:
>
> SetOp Except  (cost=202028537.97..202120623.90 rows=1227812 width=24)
>   ->  Sort  (cost=202028537.97..202028537.97 rows=12278124 width=24)
>         ->  Append  (cost=100000000.00..200225099.24 rows=12278124
> width=24)
>               ->  Subquery Scan *SELECT* 1
> (cost=100000000.00..100112549.62 rows=6139062 width=24)
>                     ->  Seq Scan on CNX_DS_53_SIS_STU_OPT_FEE_TB a
> (cost=100000000.00..100112549.62 rows=6139062 width=24)
>               ->  Subquery Scan *SELECT* 2
> (cost=100000000.00..100112549.62 rows=6139062 width=24)
>                     ->  Seq Scan on CNX_DS2_53_SIS_STU_OPT_FEE_TB b
> (cost=100000000.00..100112549.62 rows=6139062 width=24)
>
> EXPLAIN
> connxdatasync=>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html