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, MinLen 103, 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 continual thing? 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. > -- ----------------------------------------------------------------------------- PG.. [EMAIL PROTECTED] Law of probable dispersal: Whatever it is that hits the fan will not be evenly distributed. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
