Date: Mon, 06 Apr 2009 01:54:26 +0200
To: "Rainer Mager" <rainer@vanten.com>, pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
Content-Transfer-Encoding: 7bit
Message-ID: <op.urxt00cbcigqcu@soyouz>
In-Reply-To: <003001c9b645$e8a7cec0$b9f76c40$@com>
User-Agent: Opera Mail/9.62 (Linux)

> When I try to do queries on this
> table I always find them slower than what I need and what I believe  
> should be possible.
>
>    ->  Bitmap Index Scan on ad_log_date_all  (cost=0.00..72750.51
> rows=2488252 width=0) (actual time=49776.332..49776.332 rows=2268490
> loops=1)
>
>          Index Cond: ((date(start_time) < '2009-03-31'::date) AND
> (date(start_time) >= '2009-03-30'::date))
>
>  Total runtime: 65279.352 ms

	Well, it is grabbing 2.268.490 rows, that's a lot of rows, so it is not  
going to be very fast like a few milliseconds.
	Your columns are small, ints, dates, not large text strings which would  
augment the total amount of data.
	So your timing looks pretty slow, it should be faster than this, maybe a  
few seconds.

	With this quantity of rows, you want to try to make the disk accesses as  
linear as possible.
	This means your table should be organized on disk by date, at least  
roughly.
	If your data comes from an import that was sorted on some other column,  
this may not be the case.

	What kind of bytes/s do you get from the drives ?

=> Can you post the result of "vmstat 1" during the entire execution of  
the query ?

	2 phases should be visible in the vmstat output, the indexscan, and the  
bitmap heapscan.

	You could use CLUSTER on the table (it will take a long time), or simply  
create another table and INSERT INTO ... SELECT ORDER BY date. This will  
also take a long time, but faster than CLUSTER. Then you could recreate  
the indexes.

	Do you UPDATE or DELETE a lot from this table ? Is it vacuum'd enough ?