Re: Index doesn't appear to be working.

From: Thomas Lockhart <lockhart(at)fourpalms(dot)org>
To: John Oakes <john(at)networkproductions(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Index doesn't appear to be working.
Date: 2002-03-07 13:48:13
Message-ID: 3C876F9D.6F3DA3B9@fourpalms.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> and it tells me:
> Seq Scan on one (cost=0.00..263.02 rows=5613 width=56)
> Shouldn't it tell me
> Index Scan using one_passfail_idx on one?

Only if most of the students are failing ;)

Since most entries in this table are likely to be 'P', the optimizer
correctly concludes that it would be faster to sequentially scan the
table for rows. This is likely to be the optimizer choice for any
two-value column.

If the table has only a few rows (as might be true for a single class or
for a test dataset) then the optimizer will never choose an index scan.

If the table has more than a few hundred rows then you might get an
index scan if you search for students who are failing (again, assuming
that most are not). Make sure that you "vacuum analyze" after populating
the table to get correct statistics.

- Thomas

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Chris Gamache 2002-03-07 13:56:28 Re: improving performance of UNION and ORDER BY
Previous Message news 2002-03-07 12:46:03 Re: NEWBIE: Date format/timestamp issue ?