Re: Query optimization

On Saturday 07 Dec 2002 2:16 am, Fred Moyer wrote:
>
> database=# explain analyze SELECT active,registrant,name FROM person WHERE
> object.active = 1 AND object.registrant = 't' ORDER BY UPPER(object.name)
> DESC LIMIT 10 OFFSET 0;
> NOTICE: QUERY PLAN:

What's the connection between "person" and "object"? Looks like an
unconstrained join from here. Schema and count(*) for both and details of
indexes would be useful.

> Limit (cost=nan..nan rows=10 width=2017) (actual
^^^^^^^^
Never seen this "nan" before - presumably Not A Number, but I don't know why
the planner generates it

> 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

Without seeing schema details difficult to suggest much. If it's this
particular query that's the problem you might try a partial index

CREATE INDEX foo_object_idx ON object (upper(object.name)) WHERE active=1 AND
registrant='t';

See CREATE INDEX in the manuals for details.

--
Richard Huxton