Skip site navigation (1) Skip section navigation (2)

Re: Indexes not always used after inserts/updates/vacuum analyze

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Michael G(dot) Martin" <michael(at)vpmonline(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Date: 2002-02-28 03:57:10
Message-ID: 10710.1014868630@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Michael G. Martin" <michael(at)vpmonline(dot)com> writes:
> Here is what I would expect which usually happens:

> explain select * from symbol_data where symbol_name='IBM';
> Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..512.99 rows=128 width=129)

> Here is one that fails:

> explain select * from symbol_data where symbol_name='ELTE';
> Seq Scan on symbol_data  (cost=0.00..707415.32 rows=438015 width=129)

The planner thinks that there are 438K occurrences of 'ELTE' in your
table.  If that's true, a sequential scan is not obviously the wrong
choice.  How many are there, in reality?  What do you get from

select * from pg_stats where tablename = 'symbol_data' and attname =
'symbol_name';

Also, to put the rubber to the road: if you force an indexscan by
doing "set enable_seqscan = off", does it get faster or slower?
(EXPLAIN ANALYZE would be useful here.)

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Michael G. MartinDate: 2002-02-28 04:15:17
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Previous:From: Michael G. MartinDate: 2002-02-28 02:53:45
Subject: Indexes not always used after inserts/updates/vacuum analyze

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group