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?