Re: index problem

On Tue, 16 Oct 2001, CoL wrote:

> ---------------------------
> The 2 table query, where prog_data has ~8800 rowsn and index on prog_id:
> bash-2.04$ time echo "explain select distinct 
> prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data 
> where pxygy_pid=prog_id " | psql -Uuser db
> NOTICE:  QUERY PLAN:
> 
> Unique  (cost=7432549.69..7680455.07 rows=2479054 width=32)
>    ->  Sort  (cost=7432549.69..7432549.69 rows=24790538 width=32)
>          ->  Merge Join  (cost=148864.65..161189.33 rows=24790538 width=32)
>                ->  Index Scan using prog_data_pkey on prog_data 
> (cost=0.00..701.12 rows=8872 width=28)
>                ->  Sort  (cost=148864.65..148864.65 rows=921013 width=4)
>                      ->  Seq Scan on prog_dgy_xy  (cost=0.00..30145.13 
> rows=921013 width=4)

I'm guessing that the approximately 25 million row estimate on the join
has to be wrong as well given that prog_data.prog_id should be unique.

Hmm, does the explain change if you vacuum analyze the other table
(prog_data)?  If not, what does explain show if you do a
set enable_seqscan='off';
before it?