Thread: Re: Wierd Explain
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)
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.
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.
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
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
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.
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
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.