Thread: Re: [GENERAL] I feel the need for speed. What am I doing wrong?

> -----Original Message-----
> From: johnnnnnn [mailto:john@phaedrusdeinus.org]
> Sent: Tuesday, January 07, 2003 3:33 PM
> To: pgsql-hackers@postgresql.org
> Subject: Re: [GENERAL] [HACKERS] I feel the need for speed.
> What am I doing wrong?
>
>
> On Tue, Jan 07, 2003 at 03:10:06PM -0800, Dann Corbit wrote:
> > 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
>
> Those big round numbers suggest that you haven't run vacuum
> analyze on all of your tables. Since PostgreSQL uses a
> cost-based optimizer, you do actually have to give it some
> idea of what things will cost before it can give you an
> appropriate plan.
>
> Reference for your version:
> http://www14.us.postgresql.org/users-lounge/docs/7.1/reference
/sql-vacuum.html

No analyze for 7.1.3.
Just ran vacuum a few minutes before the query.  No boost at all.  Even
with SET enable_seqscan = 0 it still does a table scan.

--On Tuesday, January 07, 2003 15:25:06 -0800 Dann Corbit 
<DCorbit@connx.com> wrote:

>
> No analyze for 7.1.3.
> Just ran vacuum a few minutes before the query.  No boost at all.  Even
> with SET enable_seqscan = 0 it still does a table scan.
did you do VACUUM ANALYZE?

If not, the stats weren't updated.


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

"Dann Corbit" <DCorbit@connx.com> writes:

> No analyze for 7.1.3.
> Just ran vacuum a few minutes before the query.  No boost at all. 

VACUUM or VACUUM ANALYZE?  Standalone ANALYZE was not in 7.1 but
VACUUM ANALYZE does what you need to do...

-Doug

> No analyze for 7.1.3.
> Just ran vacuum a few minutes before the query.  No boost at all.  Even
> with SET enable_seqscan = 0 it still does a table scan.

Dann, I can attest to 7.2 having a much better planner and performance 
than 7.1 did.  Can you upgrade?