Re: Why does it not use the index? - Mailing list pgsql-general

Philip,
In order for psql to work out the most effective way to run queries it
relies on statistics about the size of your tables (amounst other
things).
Running VACUUM ANALYZE does two things:
The vacuum removes any tuples you have deleted from the database (before
this i think they are just marked as deleted)
The analzye bit then rebuilds the statistics which the query planner
uses - making your queries faster.

If you are only ever adding tuples to the database then you don't need
to Vacuum as often, although i think you still need to vacuum every X
million queries.

I made the same mistake last week...


On Mon, 2003-07-21 at 19:51, Philip Greer wrote:
> Thanks for the response:
>
> I took a look at the table with 'vacuum verbose analyze', here's the results:
>
> dumps=# vacuum verbose analyze fal_profdel;
> NOTICE:  --Relation fal_profdel--
> NOTICE:  Pages 62232: Changed 0, reaped 205, Empty 0, New 0; Tup 4664867: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 447,
MinLen103, MaxLen 106; Re-using: Free/Avail. Space 14896/0; EndEmpty/Avail. Pages 0/0. CPU 2.53s/0.58u sec. 
> NOTICE:  Index fal_prfdel_date: Pages 25318; Tuples 4664867: Deleted 0. CPU 0.90s/4.39u sec.
> NOTICE:  Index fal_prfdel_cn: Pages 23128; Tuples 4664867: Deleted 0. CPU 0.95s/4.63u sec.
> NOTICE:  Index fal_prfdel_pk: Pages 28323; Tuples 4664867: Deleted 0. CPU 1.21s/4.40u sec.
> NOTICE:  Analyzing...
> VACUUM
>
>
> Then - afterwards, I ran the explain again:
>
> dumps=# explain select card_num from fal_profdel where card_num = '4828820006970';
> NOTICE:  QUERY PLAN:
>
> Index Scan using fal_prfdel_cn on fal_profdel  (cost=0.00..4.95 rows=1 width=12)
>
> EXPLAIN
>
>
> WTF? Why would a vacuum be necessary in order for it to start using the index?
>
>
> By the way, the actual query takes subseconds to return now. MUCH better.
>
>
> So - let me know why one would have to use vacuum in order for the scans to cease and index use begin. Is it a
continualthing? Or does vacuum need to be done after a 'create index' in order for it to begin using the index? 
>
> Thanks for your assistance, much apprecaited!
>
>
> On Mon, Jul 21, 2003 at 11:00:56AM -0700, Stephan Szabo filled up my inbox with the following:
> > On Mon, 21 Jul 2003, Philip Greer wrote:
> >
> > > dumps=# \d fal_profdel
> > >                Table "fal_profdel"
> > >  Attribute |           Type           | Modifier
> > > -----------+--------------------------+----------
> > >  sid       | character(4)             | not null
> > >  card_num  | character(19)            | not null
> > >  date_del  | timestamp with time zone |
> > >  filename  | character varying(30)    |
> > > Indices: fal_prfdel_cn,
> > >          fal_prfdel_date,
> > >          fal_prfdel_pk
> > >
> > > dumps=# \d fal_prfdel_cn
> > >    Index "fal_prfdel_cn"
> > >  Attribute |     Type
> > > -----------+---------------
> > >  card_num  | character(19)
> > > unique btree
> > >
> > > dumps=# explain select card_num from fal_profdel where card_num = 'removed_for_privacy';
> > > NOTICE:  QUERY PLAN:
> > >
> > > Seq Scan on fal_profdel  (cost=0.00..120546.39 rows=46649 width=12)
> > >
> > > EXPLAIN
> > > ================================================================================
> > >
> > > Now, why the heck is the select query not using the index? I've tried
> > > it by having an exact 19 character card_num as well - still explains
> > > as a 'Seq Scan' (tablespace scan) - and each query takes up to 37
> > > seconds (thus confirming that it is indeed doing scans and not using
> > > the index).
> >
> > Have you vacuum analyzed the table recently? What does explain show if you
> > do set enable_seqscan=off; before the explain and then how long does the
> > query actually take to run with seqscan disabled.
> >