Re: Query optimization - Mailing list pgsql-performance

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

Here is the relevant table info (some schema details omitted for brevity)

id              | numeric(10,0)            | not null default
nextval('seq_object'
::text)
name           | character varying(64)    |
registrant      | boolean                  |
active          | numeric(1,0)             | not null default 1

registrant__object__idx
active__object__idx,
name__object__idx,
upper_name__object__idx,
id__object__idx,
Primary key: pk_object__id

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

db=# explain analyze select count(*) from object;
NOTICE:  QUERY PLAN:

Aggregate  (cost=100073270.91..100073270.91 rows=1 width=0) (actual
time=3085.51..3085.51 rows=1 loops=1)
  ->  Seq Scan on object  (cost=100000000.00..100070688.53 rows=1032953
width=0) (actual time=0.01..2008.51 rows=1032953 loops=1)
Total runtime: 3085.62 msec

EXPLAIN

> On Saturday 07 Dec 2002 2:16 am, Fred Moyer wrote:
>>
>> database=# explain analyze SELECT active,registrant,name FROM object
>> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


Fred Moyer
Digital Campaigns, Inc.