pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddifficulties with time based queries
13+ messages / 7 participants
[nested] [flat]
* difficulties with time based queries
@ 2009-04-05 23:26 Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-05 23:54 ` Re: difficulties with time based queries PFC <lists@peufeu.com>
2009-04-06 00:12 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 13+ messages in thread
From: Rainer Mager @ 2009-04-05 23:26 UTC (permalink / raw)
To: pgsql-performance
I have a somewhat large table (more than 100 million rows) that contains log
data with start_time and end_time columns. 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.
For example, I limited the following query to just a single day and it still
is much slower than what I would expect. In reality I need to do queries
that span a few weeks.
explain analyze select * from ad_log where date(start_time) <
date('2009-03-31') and date(start_time) >= date('2009-03-30');
Bitmap Heap Scan on ad_log (cost=73372.57..3699152.24 rows=2488252
width=32) (actual time=49792.862..64611.255 rows=2268490 loops=1)
Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND
(date(start_time) >= '2009-03-30'::date))
-> 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
The definition of the table is:
Column | Type |
Modifiers
------------+-----------------------------+---------------------------------
---------------------------
ad_log_id | integer | not null default
nextval('ad_log_ad_log_id_seq'::regclass)
channel | integer | not null
player | integer | not null
ad | integer | not null
start_time | timestamp without time zone |
end_time | timestamp without time zone |
Indexes:
"ad_log_pkey" PRIMARY KEY, btree (ad_log_id)
"ad_log_unique" UNIQUE, btree (channel, player, ad, start_time,
end_time)
"ad_log_ad" btree (ad)
"ad_log_ad_date" btree (ad, date(start_time))
"ad_log_channel" btree (channel)
"ad_log_channel_date" btree (channel, date(start_time))
"ad_log_date_all" btree (date(start_time), channel, player, ad)
"ad_log_player" btree (player)
"ad_log_player_date" btree (player, date(start_time))
Foreign-key constraints:
"ad_log_ad_fkey" FOREIGN KEY (ad) REFERENCES ads(id)
"ad_log_channel_fkey" FOREIGN KEY (channel) REFERENCES channels(id)
"ad_log_player_fkey" FOREIGN KEY (player) REFERENCES players_history(id)
Triggers:
rollup_ad_logs_daily AFTER INSERT ON ad_log FOR EACH ROW EXECUTE
PROCEDURE rollup_ad_logs_daily()
Any suggestions would be appreciated.
--Rainer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
@ 2009-04-05 23:46 ` David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2 siblings, 1 reply; 13+ messages in thread
From: David Wilson @ 2009-04-05 23:46 UTC (permalink / raw)
To: Rainer Mager <rainer@vanten.com>; +Cc: pgsql-performance
On Sun, Apr 5, 2009 at 7:26 PM, Rainer Mager <rainer@vanten.com> wrote:
> Bitmap Heap Scan on ad_log (cost=73372.57..3699152.24 rows=2488252
> width=32) (actual time=49792.862..64611.255 rows=2268490 loops=1)
>
> Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND
> (date(start_time) >= '2009-03-30'::date))
>
> -> 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
The stats look good and it's using a viable index for your query. What
kind of hardware is this on, and what are the relevant postgresql.conf
lines? (Or, for that matter, what does iostat say while this query's
running?)
--
- David T. Wilson
david.t.wilson@gmail.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
@ 2009-04-06 01:26 ` Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
2009-04-06 12:24 ` Re: difficulties with time based queries Matthew Wakeling <matthew@flymine.org>
0 siblings, 2 replies; 13+ messages in thread
From: Rainer Mager @ 2009-04-06 01:26 UTC (permalink / raw)
To: pgsql-performance
Thanks for all the replies, I'll try to address the follow up questions:
> From: David Wilson [mailto:david.t.wilson@gmail.com]
>
> The stats look good and it's using a viable index for your query. What
> kind of hardware is this on, and what are the relevant postgresql.conf
> lines? (Or, for that matter, what does iostat say while this query's
> running?)
I'm running on Windows, so I don't have iostat, but perfmon tells me my Avg.
Disk Queue Length went up to 1.2 during the query (versus a normal value of
about 0.02). Also disk throughput was at about 1.2 MB/s during the query. I
don't know how much of this is random versus linear.
> From: PFC [mailto:lists@peufeu.com]
>
> 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 ?
The data should be mostly ordered by date. It is all logged in semi-realtime
such that 99% will be logged within an hour of the timestamp. Also, as
stated above, during this query it was about 1.2 MB/s, which I know isn't
great. I admit this isn't the best hardware in the world, but I would expect
better than that for linear queries.
> Do you UPDATE or DELETE a lot from this table ? Is it vacuum'd
> enough ?
No, this table has no UPDATEs or DELETEs. It is auto vacuum'd, but no manual
vacuuming.
In regards to clustering, I'm hesitant to do that unless I have no other
choice. My understanding is that I would need to do periodic re-clustering
to maintain it, and during that time the table is very busy.
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Hmm ... it's pretty unusual to see the index fetch portion of a bitmap
> scan take the bulk of the runtime. Usually that part is fast and where
> the pain comes is in fetching from the heap. I wonder whether that
> index has become bloated. How big are the table and the index
> physically? (Look at pg_class.relpages, or if you want a really
> accurate number try pg_relation_size().)
Can you give me some more info on how to look at these stats? That is,
what/where is pg_class.relpages, etc. I'll also do some searching for this
info.
> What Postgres version is this, exactly?
8.3.3
> BTW, I think you've gone way overboard in your indexing of this table;
> those indexes are certainly consuming well more space than the table
> does, and a lot of them are redundant.
Agreed, I need to look carefully at all of the queries we do on this table
and reduce this.
--Rainer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
@ 2009-04-06 01:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-06 03:35 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-08 22:49 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-10 05:20 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 13+ messages in thread
From: Tom Lane @ 2009-04-06 01:33 UTC (permalink / raw)
To: Rainer Mager <rainer@vanten.com>; +Cc: pgsql-performance
"Rainer Mager" <rainer@vanten.com> writes:
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Hmm ... it's pretty unusual to see the index fetch portion of a bitmap
>> scan take the bulk of the runtime. Usually that part is fast and where
>> the pain comes is in fetching from the heap. I wonder whether that
>> index has become bloated. How big are the table and the index
>> physically? (Look at pg_class.relpages, or if you want a really
>> accurate number try pg_relation_size().)
> Can you give me some more info on how to look at these stats?
Since you've got 8.3 it's easy: select pg_relation_size('tablename')
(or indexname). The result is in bytes, so you might want to
divide by 1K or 1M to keep the number readable.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-04-06 03:35 ` Rainer Mager <rainer@vanten.com>
2009-04-06 10:37 ` Re: difficulties with time based queries Robert Haas <robertmhaas@gmail.com>
2 siblings, 1 reply; 13+ messages in thread
From: Rainer Mager @ 2009-04-06 03:35 UTC (permalink / raw)
To: pgsql-performance
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> "Rainer Mager" <rainer@vanten.com> writes:
> >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> >> Hmm ... it's pretty unusual to see the index fetch portion of a
> bitmap
> >> scan take the bulk of the runtime. Usually that part is fast and
> where
> >> the pain comes is in fetching from the heap. I wonder whether that
> >> index has become bloated. How big are the table and the index
> >> physically? (Look at pg_class.relpages, or if you want a really
> >> accurate number try pg_relation_size().)
>
> > Can you give me some more info on how to look at these stats?
>
> Since you've got 8.3 it's easy: select pg_relation_size('tablename')
> (or indexname). The result is in bytes, so you might want to
> divide by 1K or 1M to keep the number readable.
Ok, nice and simple...I like it:
The result for the table ad_log, is 30,063 MB. The result for the index,
ad_log_date_all, is 17,151 MB. I guess this roughly makes sense since the
index is on 4 fields and the table only has 6 fields.
For the particular query I'm trying to optimize at the moment I believe I
should be able to use an index that references only 2 fields, which, I
imagine, should reduce the time needed to read it. I'll play with this a bit
and see what happens.
Any other suggestions?
--Rainer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
2009-04-06 03:35 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
@ 2009-04-06 10:37 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Robert Haas @ 2009-04-06 10:37 UTC (permalink / raw)
To: Rainer Mager <rainer@vanten.com>; +Cc: pgsql-performance
On Sun, Apr 5, 2009 at 11:35 PM, Rainer Mager <rainer@vanten.com> wrote:
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> "Rainer Mager" <rainer@vanten.com> writes:
>> >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> >> Hmm ... it's pretty unusual to see the index fetch portion of a
>> bitmap
>> >> scan take the bulk of the runtime. Usually that part is fast and
>> where
>> >> the pain comes is in fetching from the heap. I wonder whether that
>> >> index has become bloated. How big are the table and the index
>> >> physically? (Look at pg_class.relpages, or if you want a really
>> >> accurate number try pg_relation_size().)
>>
>> > Can you give me some more info on how to look at these stats?
>>
>> Since you've got 8.3 it's easy: select pg_relation_size('tablename')
>> (or indexname). The result is in bytes, so you might want to
>> divide by 1K or 1M to keep the number readable.
>
> Ok, nice and simple...I like it:
>
> The result for the table ad_log, is 30,063 MB. The result for the index,
> ad_log_date_all, is 17,151 MB. I guess this roughly makes sense since the
> index is on 4 fields and the table only has 6 fields.
>
> For the particular query I'm trying to optimize at the moment I believe I
> should be able to use an index that references only 2 fields, which, I
> imagine, should reduce the time needed to read it. I'll play with this a bit
> and see what happens.
Even if your query "could use" an index four fields, a lot of times it
won't be the winning strategy, because it means reading a lot more
data from the disk. Plus, all of these huge indices are competing for
RAM with data from the table itself. You might want to think about
getting rid of all of the indices with more than 1 or 2 columns.
ad_log_unique is probably huge and it seems like it's probably not
improving your data integrity as much as you might think...
...Robert
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-04-08 22:49 ` Rainer Mager <rainer@vanten.com>
2009-04-14 09:18 ` Re: difficulties with time based queries PFC <lists@peufeu.com>
2 siblings, 1 reply; 13+ messages in thread
From: Rainer Mager @ 2009-04-08 22:49 UTC (permalink / raw)
To: 'Rainer Mager' <rainer@vanten.com>; pgsql-performance
So, I defragged my disk and reran my original query and it got a little
better, but still far higher than I'd like. I then rebuilt (dropped and
recreated) the ad_log_date_all index and reran the query and it is quite a
bit better:
# explain analyze select * from ad_log where date(start_time) <
date('2009-03-31') and date(start_time) >= date('2009-03-30');
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------------------
Bitmap Heap Scan on ad_log (cost=64770.21..3745596.62 rows=2519276
width=32) (actual time=1166.479..13862.107 rows=2275167 loops=1)
Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND
(date(start_time) >= '2009-03-30'::date))
-> Bitmap Index Scan on ad_log_date_all (cost=0.00..64140.39
rows=2519276 width=0) (actual time=1143.582..1143.582 rows=2275167 loops=1)
Index Cond: ((date(start_time) < '2009-03-31'::date) AND
(date(start_time) >= '2009-03-30'::date))
Total runtime: 14547.885 ms
During the query the disk throughput peaked at 30MB/s and was mostly at
around 20MB/s, much better.
So, a few questions:
What can I do to prevent the index from getting bloated, or in whatever
state it was in?
What else can I do to further improve queries on this table? Someone
suggested posting details of my conf file. Which settings are most likely to
be useful for this?
Any other suggestions?
Thanks,
--Rainer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
2009-04-08 22:49 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
@ 2009-04-14 09:18 ` PFC <lists@peufeu.com>
2009-04-14 12:56 ` Re: difficulties with time based queries Nikolas Everett <nik9000@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: PFC @ 2009-04-14 09:18 UTC (permalink / raw)
To: Rainer Mager <rainer@vanten.com>; pgsql-performance
> What can I do to prevent the index from getting bloated, or in whatever
> state it was in?
>
>
> What else can I do to further improve queries on this table? Someone
> suggested posting details of my conf file. Which settings are most
> likely to
> be useful for this?
If you often do range queries on date, consider partitioning your table
by date (something like 1 partition per month).
Of course, if you also often do range queries on something other than
date, and uncorrelated, forget it.
If you make a lot of big aggregate queries, consider materialized views :
Like "how many games player X won this week", etc
- create "helper" tables which contain the query results
- every night, recompute the results taking into account the most recent
data
- don't recompute results based on old data that never changes
This is only interesting if the aggregation reduces the data volume by
"an appreciable amount". For instance, if you run a supermarket with 1000
distinct products in stock and you sell 100.000 items a day, keeping a
cache of "count of product X sold each day" will reduce your data load by
about 100 on the query "count of product X sold this month".
The two suggestion above are not mutually exclusive.
You could try bizgres also. Or even MySQL !... MySQL's query engine is
slower than pg but the tables take much less space than Postgres, and it
can do index-only queries. So you can fit more in the cache. This is only
valid for MyISAM (InnoDB is a bloated hog). Of course, noone would want to
use MyISAM for the "safe" storage, but it's pretty good as a read-only
storage. You can even use the Archive format for even more compactness and
use of cache. Of course you'd have to devise a way to dump from pg and
load into MySQL but that's not hard. MySQL can be good if you target a
table with lots of small rows with a few ints, all of them in a
multicolumn index, so it doesn't need to hit the table itself.
Note that one in his right mind would never run aggregate queries on a
live R/W MyISAM table since the long queries will block all writes and
blow up the reaction time. But for a read-only cache updated at night, or
replication slave, it's okay.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
2009-04-08 22:49 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-14 09:18 ` Re: difficulties with time based queries PFC <lists@peufeu.com>
@ 2009-04-14 12:56 ` Nikolas Everett <nik9000@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Nikolas Everett @ 2009-04-14 12:56 UTC (permalink / raw)
To: PFC <lists@peufeu.com>; +Cc: Rainer Mager <rainer@vanten.com>; pgsql-performance
>
> If you often do range queries on date, consider partitioning your
> table by date (something like 1 partition per month).
> Of course, if you also often do range queries on something other
> than date, and uncorrelated, forget it.
If you pick your partition to line up with your queries than you can
probably do away with the date index. Even if it doesn't always line up
perfectly its worth considering.
>
> If you make a lot of big aggregate queries, consider materialized
> views :
>
> Like "how many games player X won this week", etc
>
> - create "helper" tables which contain the query results
> - every night, recompute the results taking into account the most
> recent data
> - don't recompute results based on old data that never changes
>
> This is only interesting if the aggregation reduces the data volume
> by "an appreciable amount". For instance, if you run a supermarket with 1000
> distinct products in stock and you sell 100.000 items a day, keeping a cache
> of "count of product X sold each day" will reduce your data load by about
> 100 on the query "count of product X sold this month".
This obviously creates some administration overhead. So long as this is
manageable for you this is a great solution. You might also want to look at
Mondrian at http://mondrian.pentaho.org/ . It takes some tinkering but buys
you some neat views into your data and automatically uses those aggregate
tables.
Nik Everett
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Re: difficulties with time based queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-04-10 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2009-04-10 05:20 UTC (permalink / raw)
To: Rainer Mager <rainer@vanten.com>; +Cc: pgsql-performance
"Rainer Mager" <rainer@vanten.com> writes:
> So, I need indices that make it fast querying against start_time as well as
> all possible combinations of channel, player, and ad.
There's some general principles in the manual --- have you read
http://www.postgresql.org/docs/8.3/static/indexes.html
especially 11.3 and 11.5?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` Re: difficulties with time based queries David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Re: difficulties with time based queries Rainer Mager <rainer@vanten.com>
@ 2009-04-06 12:24 ` Matthew Wakeling <matthew@flymine.org>
1 sibling, 0 replies; 13+ messages in thread
From: Matthew Wakeling @ 2009-04-06 12:24 UTC (permalink / raw)
To: pgsql-performance
On Mon, 6 Apr 2009, Rainer Mager wrote:
> The data should be mostly ordered by date. It is all logged in semi-realtime
> such that 99% will be logged within an hour of the timestamp. Also, as
> stated above, during this query it was about 1.2 MB/s, which I know isn't
> great. I admit this isn't the best hardware in the world, but I would expect
> better than that for linear queries.
Might you have an unbalanced index tree? Reindexing would also solve that
problem.
Matthew
--
There are only two kinds of programming languages: those people always
bitch about and those nobody uses. (Bjarne Stroustrup)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
@ 2009-04-05 23:54 ` PFC <lists@peufeu.com>
2 siblings, 0 replies; 13+ messages in thread
From: PFC @ 2009-04-05 23:54 UTC (permalink / raw)
To: Rainer Mager <rainer@vanten.com>; pgsql-performance
> 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 ?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: difficulties with time based queries
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
@ 2009-04-06 00:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2009-04-06 00:12 UTC (permalink / raw)
To: Rainer Mager <rainer@vanten.com>; +Cc: pgsql-performance
"Rainer Mager" <rainer@vanten.com> writes:
> explain analyze select * from ad_log where date(start_time) <
> date('2009-03-31') and date(start_time) >= date('2009-03-30');
> Bitmap Heap Scan on ad_log (cost=73372.57..3699152.24 rows=2488252
> width=32) (actual time=49792.862..64611.255 rows=2268490 loops=1)
> Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND
> (date(start_time) >= '2009-03-30'::date))
> -> 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
Hmm ... it's pretty unusual to see the index fetch portion of a bitmap
scan take the bulk of the runtime. Usually that part is fast and where
the pain comes is in fetching from the heap. I wonder whether that
index has become bloated. How big are the table and the index
physically? (Look at pg_class.relpages, or if you want a really
accurate number try pg_relation_size().)
What Postgres version is this, exactly?
BTW, I think you've gone way overboard in your indexing of this table;
those indexes are certainly consuming well more space than the table
does, and a lot of them are redundant.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2009-04-14 12:56 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-05 23:26 difficulties with time based queries Rainer Mager <rainer@vanten.com>
2009-04-05 23:46 ` David Wilson <david.t.wilson@gmail.com>
2009-04-06 01:26 ` Rainer Mager <rainer@vanten.com>
2009-04-06 01:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-06 03:35 ` Rainer Mager <rainer@vanten.com>
2009-04-06 10:37 ` Robert Haas <robertmhaas@gmail.com>
2009-04-08 22:49 ` Rainer Mager <rainer@vanten.com>
2009-04-14 09:18 ` PFC <lists@peufeu.com>
2009-04-14 12:56 ` Nikolas Everett <nik9000@gmail.com>
2009-04-10 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-06 12:24 ` Matthew Wakeling <matthew@flymine.org>
2009-04-05 23:54 ` PFC <lists@peufeu.com>
2009-04-06 00:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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