From: PFC <lists@peufeu.com>
To: Rainer Mager <rainer@vanten.com>
To: pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries
Date: Mon, 06 Apr 2009 01:54:26 +0200
Message-ID: <op.urxt00cbcigqcu@soyouz> (raw)
In-Reply-To: <003001c9b645$e8a7cec0$b9f76c40$@com>
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
> 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 ?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, rainer@vanten.com
Subject: Re: difficulties with time based queries
In-Reply-To: <op.urxt00cbcigqcu@soyouz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox