Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 29E45633100 for ; Sun, 5 Apr 2009 20:33:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 88584-02 for ; Sun, 5 Apr 2009 20:33:26 -0300 (ADT) X-Greylist: delayed 00:07:30.362801 by SQLgrey-1.7.6 Received: from mail.vanten.com (doc.vanten.com [203.216.141.136]) by mail.postgresql.org (Postfix) with ESMTP id 5FC7E632F61 for ; Sun, 5 Apr 2009 20:33:32 -0300 (ADT) Received: from hexagon.office.vanten.com (hexagon.office.vanten.com [192.168.220.16]) by mail.vanten.com (Postfix) with ESMTP id 9CAE94111 for ; Mon, 6 Apr 2009 08:25:58 +0900 (JST) Received: from Espresso (Espresso.office.vanten.com [192.168.220.80]) by hexagon.office.vanten.com (Postfix) with ESMTP id 646393C46C for ; Mon, 6 Apr 2009 08:25:58 +0900 (JST) From: "Rainer Mager" To: Subject: difficulties with time based queries Date: Mon, 6 Apr 2009 08:26:11 +0900 Message-ID: <003001c9b645$e8a7cec0$b9f76c40$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0031_01C9B691.588F76C0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Acm2ReeWD4/AIKhKTR6uOZIK0VCyGA== Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200904/109 X-Sequence-Number: 33476 This is a multipart message in MIME format. ------=_NextPart_000_0031_01C9B691.588F76C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0031_01C9B691.588F76C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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) >=3D date('2009-03-30');

 

 

Bitmap = Heap Scan on ad_log  (cost=3D73372.57..3699152.24 rows=3D2488252 width=3D32) = (actual time=3D49792.862..64611.255 rows=3D2268490 = loops=3D1)

   Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND (date(start_time) = >=3D '2009-03-30'::date))

   ->  Bitmap Index Scan on ad_log_date_all  = (cost=3D0.00..72750.51 rows=3D2488252 width=3D0) (actual time=3D49776.332..49776.332 = rows=3D2268490 loops=3D1)

         Index Cond: ((date(start_time) < '2009-03-31'::date) AND = (date(start_time) >=3D '2009-03-30'::date))

 Total runtime: 65279.352 ms

 

 

The definition of the table is:

 

   Column   |            Type           &nb= sp; |            =              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

------=_NextPart_000_0031_01C9B691.588F76C0--