Try CLUSTER --- that usually helps with index scans on ranges. ---------------------------------------------------------------------------
ow wrote: > Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06. > All configuration settings are default. > > > Hi, > > Trying to find a way to improve range query performance. > > The table Test has about 30 million records. > > -- DLong, Dtimestamp, Dint, etc are domains of the respective types. > create table Test ( > id DLong not null, > a Dtimestamp null, > b Dint not null, > c Dint not null, > d Dstring null, > constraint PK_id primary key (id), > constraint AK_abc unique (a, b, c) > ); > > The following query retrieves a single record, it runs against AK index and is > extremely fast (30-150 ms) for the table of this size: > > -- returns result in 30-150 ms > select * from Test > where a = '2002-09-01' > and b = 5 > and c = 255 > > OTOH, the following range query that returns 30 records performs much slower, > about 33000 ms. The query is using AK index, as it should, but why does it take > so much longer to scan the index for the range of just 30 records? I see that > PG is hitting the disk very intensively for this query. Can the query be > rewritten, etc to improve performance? Thanks > > select * from Test > where a >= '2002-09-01' > and a <= '2002-09-30' > and b = 5 > and c = 255 > > QUERY PLAN > Index Scan using ak_abc on test (cost=0.00..106.27 rows=30 width=53) (actual > time=33.536..33200.998 rows=30 loops=1) > Index Cond: (((a)::timestamp without time zone >= '2002-09-01 > 00:00:00'::timestamp without time zone) AND ((a)::timestamp without time zone > <= '2002-09-30 00:00:00'::timestamp without time zone) AND ((b)::integer > = 5) AND ((c) (..) > Total runtime: 33201.219 ms > > > > > > > > > __________________________________ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
