Thread: difficulties with time based queries

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

> 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 ?

"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

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

"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

> -----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

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

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)

"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

> 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.