From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | fred(at)digicamp(dot)com, <pgsql-performance(at)postgresql(dot)org> |
Cc: | <fred(at)digicamp(dot)com> |
Subject: | Re: Query optimization |
Date: | 2002-12-07 17:13:08 |
Message-ID: | 200212071713.09092.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fred Moyer | 2002-12-07 20:10:41 | Re: Query optimization |
Previous Message | Ron Johnson | 2002-12-07 15:48:36 | Re: Speeding up aggregates |