Re: Index usage vs large repetitions of key

On Sat, 4 May 2002, Neil Conway wrote:

> On Sat, 4 May 2002 16:25:47 -0400 (EDT)
> "Francisco Reyes" <lists@natserv.com> wrote:
> > I have numerous queries I do against this table that only need to acces
> > one or two years.
>
> Can you post some of the queries that are problematic, as well as the
> output of EXPLAIN ANALYZE for them?

Most of the queries are large and ugly.
Following is simple one that is very much like the common queries we have.

-- Query
explain analyze
select record_key, ystart, cstart
        from ystats, hearn
        where year = 2002 and
        ystats.record_key = hearn.horse_key and
        cstat_date > '1-1-2002'
        and ystart <> cstart ;
--

-- Explain Analyze
Hash Join  (cost=528.26..101381.81 rows=14 width=12) (actual
time=5237.61..16835.63 rows=69552 loops=1)
  ->  Seq Scan on ystats  (cost=0.00..99960.80 rows=178505 width=6)
      (actual time=2049.91..13066.82 rows=127445 loops=1)

  ->  Hash  (cost=527.88..527.88 rows=154 width=6)
      (actual time=833.22..833.22 rows=0 loops=1)
        ->  Index Scan using he_cstat_date on hearn
            (cost=0.00..527.88 rows=154 width=6)
            (actual time=0.47..568.92 rows=40821 loops=1)
Total runtime: 17525.13 msec
--

The estimate for ystats comes out to 178505, which is not far from the
actual 127445. This is MUCH smaller than the 3 Million + records on that
table, yet the optimizer insists on doing a sequential scan.

The estimate for hearn was also pretty bad. :-(
The optimizer estimated 154 rows and 40,821 were returned.