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

How to speed up min/max(id) in 50M rows table?

From: henk de wit <henk53602(at)hotmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: How to speed up min/max(id) in 50M rows table?
Date: 2007-10-12 20:41:56
Message-ID: BAY124-W503931258D0AE13AF37546F5A00@phx.gbl (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

I have a table with some 50 millions rows in PG 8.2. The table has indexes on relevant columns. My problem is that most everything I do with this table (which are actually very basic selects) is unbearable slow. For example:

select max(payment_id) from transactions

This takes 161 seconds.

The plan looks like this:

"Result  (cost=0.37..0.38 rows=1 width=0) (actual time=184231.636..184231.638 rows=1 loops=1)"
"  InitPlan"
"    ->  Limit  (cost=0.00..0.37 rows=1 width=8) (actual time=184231.620..184231.622 rows=1 loops=1)"
"          ->  Index Scan Backward using trans_payment_id_index on transactions  (cost=0.00..19144690.58 rows=51122691 width=8) (actual time=184231.613..184231.613 rows=1 loops=1)"
"                Filter: (payment_id IS NOT NULL)"
"Total runtime: 184231.755 ms"

As shown, in the plan, the index on the requested column "payment_id" is being used, but the query still takes quite a lot of time. If I use a where clause in a similar query, the query seemingly runs forever, e.g.

select min(time) from transactions where payment_id = 67

There are indexes on both the time (a timestamp with time zone) and payment_id (a bigint) columns. About 1 million rows satisfy the condition payment_id = 67. This query takes a totally unrealistic amount of time for execution (I have it running for >30 minutes now on a machine with 8GB and 4 cores(at)2(dot)66Ghz, and it still isn't finished). With mpstat it becomes clear that the query is totally IO bound (what is expected of course). The machine I'm running this on has a fast RAID that can do about 320 MB/s.

Are these normal execution times for these amount of rows and this hardware? Is there anything I can do to speed up these kind of simple queries on huge tables?

Thanks in advance for all suggestions

Express yourself instantly with MSN Messenger! Download today it's FREE!


pgsql-performance by date

Next:From: Kevin GrittnerDate: 2007-10-12 20:53:07
Subject: Re: How to speed up min/max(id) in 50M rows table?
Previous:From: Tom LaneDate: 2007-10-12 19:59:16
Subject: Re: Huge amount of memory consumed during transaction

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