Skip site navigation (1) Skip section navigation (2)

Re: Query optimization

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 (view raw or flat)
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

In response to

Responses

pgsql-performance by date

Next:From: Fred MoyerDate: 2002-12-07 20:10:41
Subject: Re: Query optimization
Previous:From: Ron JohnsonDate: 2002-12-07 15:48:36
Subject: Re: Speeding up aggregates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group