Re: Query optimization

On Saturday 07 Dec 2002 8:10 pm, Fred Moyer wrote:
> Ikes, they are the same, a cut and paste error.  Sorry about that.  No
> joins involved, one table with 1 million records, about 255 rows, only
> about 10% of the rows contain data in this particular instance.
>
> object is indexed on active, registrant, and name as well as UPPER(name).
> Postgres version is 7.2.3

I think Jochem's got it with "enable_seqscan" - you've disabled scans so the
planner is checking one million index entries - bad idea. Try Jochem's
suggestion of re-enabling seqscan and see if that helps things along.

> db=# select count(*) from count;
>   count
> ---------
>  1032953

> >> time=204790.82..204790.84 rows=10 loops=1)
> >>   ->  Sort  (cost=nan..nan rows=1032953 width=2017) (actual
> >> time=204790.81..204790.82 rows=11 loops=1)
> >>         ->  Index Scan using registrant__object__idx on object
> >> (cost=0.00..81733.63 rows=1032953 width=2017) (actual
> >> time=0.14..94509.14 rows=1032946 loops=1)
> >> Total runtime: 205125.75 msec

--
  Richard Huxton