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
Cc: <fred(at)digicamp(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query optimization
Date: 2002-12-08 13:48:44
Message-ID: 200212081348.44370.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Joe ConwayDate: 2002-12-08 19:31:54
Subject: Re: Speeding up aggregates
Previous:From: Jochem van DietenDate: 2002-12-07 20:41:57
Subject: Re: Query optimization

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