Horton

Thread

  1. Re: Wierd Explain

    Varun Kacholia <varunk@cse.iitb.ac.in> — 2002-06-18T07:00:47Z

    hi , 
  Many ppl have suggested me vaccum analyze .
  But the manuals say me that it is reqd after "major update/deletion".
  I have just 'created' the tables and done fresh insertions.
  is it still required?
  BTW i am using 7.1.3 ... seems it does not support analyze :(.
thanks
  
On Mon, Jun 17, 2002 at 11:17:06PM +0200, Jochem van Dieten wrote:
> Varun Kacholia wrote:
> > hi ,
> >  I have just migrated to PostgreSQL...and found the following wierd :
> >  
> >  suryadb=# explain select * from dbwin where id in (select id from wdwin 
> >  where word='bacd');
> >  NOTICE:  QUERY PLAN:
> >  Seq Scan on dbwin  (cost=0.00..8158.20 rows=1000 width=76)
> 
> 1000 rows should trigger the obvious question: did I run vacuum analyze?
> 
> Jochem
> 

-- 
------
Varun
Do not kill time ... else time will kill you


----- End forwarded message -----

-- 
------
Varun
REALITY.SYS corrupted: Reboot universe? (Y/N/Q)

  2. Re: Wierd Explain

    Martijn van Oosterhout <kleptog@svana.org> — 2002-06-18T08:34:25Z

    On Tue, Jun 18, 2002 at 12:33:26PM +0530, Varun Kacholia wrote:
> hi , 
>   Many ppl have suggested me vaccum analyze .
>   But the manuals say me that it is reqd after "major update/deletion".
>   I have just 'created' the tables and done fresh insertions.
>   is it still required?
>   BTW i am using 7.1.3 ... seems it does not support analyze :(.

Fresh inserts would count as "major" since now there are values and before
there wern't :).

Anyway, a vacuum analyze is recommended anything you think the planner is
doing something wrong. Prior to 7.2 you could only do the analyze with a
vacuum, in 7.2 it was separated out

Also, EXPLAIN VERBOSE could be helpful also.

> On Mon, Jun 17, 2002 at 11:17:06PM +0200, Jochem van Dieten wrote:
> > Varun Kacholia wrote:
> > > hi ,
> > >  I have just migrated to PostgreSQL...and found the following wierd :
> > >  
> > >  suryadb=# explain select * from dbwin where id in (select id from wdwin 
> > >  where word='bacd');
> > >  NOTICE:  QUERY PLAN:
> > >  Seq Scan on dbwin  (cost=0.00..8158.20 rows=1000 width=76)
> > 
> > 1000 rows should trigger the obvious question: did I run vacuum analyze?
> > 
> > Jochem

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

  3. Re: Wierd Explain

    Thomas Beutin <tyrone@laokoon.in-berlin.de> — 2002-06-18T13:14:29Z

    Hi,

On Tue, Jun 18, 2002 at 12:33:26PM +0530, Varun Kacholia wrote:
> hi , 
>   Many ppl have suggested me vaccum analyze .
>   But the manuals say me that it is reqd after "major update/deletion".
>   I have just 'created' the tables and done fresh insertions.
>   is it still required?
AFAIK is not.

>   BTW i am using 7.1.3 ... seems it does not support analyze :(.
There were great feature improvements regarding vacuum on 7.2.x

greetings.
-tb

> thanks
>   
> On Mon, Jun 17, 2002 at 11:17:06PM +0200, Jochem van Dieten wrote:
> > Varun Kacholia wrote:
> > > hi ,
> > >  I have just migrated to PostgreSQL...and found the following wierd :
> > >  
> > >  suryadb=# explain select * from dbwin where id in (select id from wdwin 
> > >  where word='bacd');
> > >  NOTICE:  QUERY PLAN:
> > >  Seq Scan on dbwin  (cost=0.00..8158.20 rows=1000 width=76)
> > 
> > 1000 rows should trigger the obvious question: did I run vacuum analyze?
> > 
> > Jochem
> > 
> 
> -- 
> ------
> Varun
> Do not kill time ... else time will kill you
> 
> 
> ----- End forwarded message -----
> 
> -- 
> ------
> Varun
> REALITY.SYS corrupted: Reboot universe? (Y/N/Q) 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

  4. Re: Wierd Explain

    Doug McNaught <doug@wireboard.com> — 2002-06-18T14:39:00Z

    Thomas Beutin <tyrone@laokoon.IN-Berlin.DE> writes:

> Hi,
> 
> On Tue, Jun 18, 2002 at 12:33:26PM +0530, Varun Kacholia wrote:
> > hi , 
> >   Many ppl have suggested me vaccum analyze .
> >   But the manuals say me that it is reqd after "major update/deletion".
> >   I have just 'created' the tables and done fresh insertions.
> >   is it still required?
> AFAIK is not.

The 'analyze' part is needed since otherwise there will not be
accurate statistics about the table data, which can lead to bad choice
of query plans.

In 7.1.3 you can't do 'analyze' on its own, so you need to do 'vacuum
analyze'.

-Doug

  5. Re: Wierd Explain

    scott.marlowe <scott.marlowe@ihs.com> — 2002-06-18T15:03:50Z

    On Tue, 18 Jun 2002, Thomas Beutin wrote:

> Hi,
> 
> On Tue, Jun 18, 2002 at 12:33:26PM +0530, Varun Kacholia wrote:
> > hi , 
> >   Many ppl have suggested me vaccum analyze .
> >   But the manuals say me that it is reqd after "major update/deletion".
> >   I have just 'created' the tables and done fresh insertions.
> >   is it still required?

> AFAIK is not.

Actually, you do have to run it then.  Analyze is never done automatically 
by Postgresql, so the postmaster has no idea how your data are organized 
yet.  (Varun's data, not Thomas's  :-)

Scott Marlowe

  6. Re: Wierd Explain

    Thomas Beutin <tyrone@laokoon.in-berlin.de> — 2002-06-18T15:43:58Z

    Hi,

On Tue, Jun 18, 2002 at 09:03:50AM -0600, Scott Marlowe wrote:
> On Tue, 18 Jun 2002, Thomas Beutin wrote:
> > On Tue, Jun 18, 2002 at 12:33:26PM +0530, Varun Kacholia wrote:
> > >   Many ppl have suggested me vaccum analyze .
> > >   But the manuals say me that it is reqd after "major update/deletion".
> > >   I have just 'created' the tables and done fresh insertions.
> > >   is it still required?
> 
> > AFAIK is not.
> 
> Actually, you do have to run it then.  Analyze is never done automatically 
> by Postgresql, so the postmaster has no idea how your data are organized 
> yet.  (Varun's data, not Thomas's  :-)

Sorry, i forgot the postings bevor. But correct me if i'm wrong:
Creating a fresh table an inserting data. For the data retreiving
the vacuum analyze is not neccessary, but to get correct values
by explain it is required. Right?

Greetings.
-tb
-- 
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

  7. Re: Wierd Explain

    scott.marlowe <scott.marlowe@ihs.com> — 2002-06-18T16:14:58Z

    On Tue, 18 Jun 2002, Thomas Beutin wrote:

> Hi,
> 
> On Tue, Jun 18, 2002 at 09:03:50AM -0600, Scott Marlowe wrote:
> > On Tue, 18 Jun 2002, Thomas Beutin wrote:
> > > On Tue, Jun 18, 2002 at 12:33:26PM +0530, Varun Kacholia wrote:
> > > >   Many ppl have suggested me vaccum analyze .
> > > >   But the manuals say me that it is reqd after "major update/deletion".
> > > >   I have just 'created' the tables and done fresh insertions.
> > > >   is it still required?
> > 
> > > AFAIK is not.
> > 
> > Actually, you do have to run it then.  Analyze is never done automatically 
> > by Postgresql, so the postmaster has no idea how your data are organized 
> > yet.  (Varun's data, not Thomas's  :-)
> 
> Sorry, i forgot the postings bevor. But correct me if i'm wrong:
> Creating a fresh table an inserting data. For the data retreiving
> the vacuum analyze is not neccessary, but to get correct values
> by explain it is required. Right?

Correct, for the most part.

Analyze is NEVER required.  It just lets the database determine the 
frequency and range of values in a table so the planner can make a better 
guess when deciding how to return rows.  Since a huge insert results in a 
massive change in the layout of your tables, an analyze is needed if you 
want the best plan for your data.

Without it, select and explain will work fine, but the numbers they will 
use to make decisions will be incorrect, and the query plan chosen will 
likely be suboptimal.

Scott Marlowe

 -- "Force has no place where there is need of skill.", "Haste in every 
business brings failures.", "This is the bitterest pain among men, to have 
much knowledge but no power." -- Herodotus

  8. Re: Very Very Wierd

    Martijn van Oosterhout <kleptog@svana.org> — 2002-06-18T23:21:19Z

    On Wed, Jun 19, 2002 at 01:24:28AM +0530, Varun Kacholia wrote:
>  hi ,
>    Now i think something has happened wrong to the db that after doing
>    vaccum analyze, it has started behaving very very weirdly.
>    
>    suryadb=# explain SELECT * FROM dbmedia WHERE ID IN ((SELECT id FROM wdmedia WHERE word = 'boyzone') )  LIMIT 200;
>    NOTICE:  QUERY PLAN:
>    Limit  (cost=0.00..1005445.27 rows=200 width=76)
>      ->  Seq Scan on dbmedia  (cost=0.00..507161673.46 rows=100883 width=76)
> 	     ^^^^^^^^^^^    
> 			 SubPlan
> 			           ->  Materialize  (cost=5027.19..5027.19 rows=2575 width=4)
> 					                   ->  Index Scan using wdkmedia on wdmedia  (cost=0.00..5027.19 rows=2575 width=4)
> 					
>  why the hell cant it recognise that ID is a primary field and it has
>  just to do an index lookup? and also seeing at the time which is taken 
>  to execute the query i am damn sure that entire table scan is done.
>  it takes abt 30 secs to finish it (Athlon XP 1.7 gigs, 128 MB DDR).
>  And i think that this is terrible.Also it is not that the word is too
>  frequent that it chooses to do a sequential scan than an indexed one .
>  Should i build up an index also on ID so that it recognises it?
>  or is there a flaw in postgresql that queries with 'IN' are not
>  looked up from index but sequentially scanned?

Look up the FAQ. Use EXISTS, not IN. No-one has shown to satisfaction when
an IN can be converted to the equivalent EXISTS.

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.