Re: index problem

On Mon, 15 Oct 2001, Szabo Zoltan wrote:

> Hi,
>
> I have that:
>
> 1)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121;
> NOTICE: QUERY PLAN:
>
> Group (cost=0.00..29970.34 rows=921 width=4)
> -> Index Scan using progdgyxy_idx2 on prog_dgy_xy
> (cost=0.00..29947.32 rows=9210 width=4)
>
> than:
> 2)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121;
> NOTICE: QUERY PLAN:
>
> Group (cost=66927.88..67695.39 rows=30700 width=4)
> -> Sort (cost=66927.88..66927.88 rows=307004 width=4)
> -> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004
> width=4)
>
> I making some banchmarks on: oracle vs postgres vs mysql. And this is
> breaking me now;) Mysql and oracle width same table and index use that
> index on pxygy_pid;
> I had vacuum before.

I assume you mean you did a vacuum analyze (a plain vacuum isn't
sufficient). If you did just do a regular vacuum, do a vacuum analyze
to get the updated statistics.

How many rows actually match pxygy_pid>12121? Is 307000 rows a reasonable
estimate? How many rows are in the table?