Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 29E5364FD40 for ; Fri, 29 Aug 2008 04:33:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 69628-09 for ; Fri, 29 Aug 2008 04:32:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ichigo.office.vanten.com (static-222-230-136-226.b-fam.svips.gol.ne.jp [222.230.136.226]) by postgresql.org (Postfix) with ESMTP id 8A18964FCD3 for ; Fri, 29 Aug 2008 04:32:32 -0300 (ADT) Received: from rainerxp (rainer-xp.office.vanten.com [192.168.220.186]) by ichigo.office.vanten.com (Postfix) with ESMTP id 3A3C750861 for ; Fri, 29 Aug 2008 16:32:28 +0900 (JST) From: "Rainer Mager" To: References: <000c01c908e4$f64e92c0$e2ebb840$@com> <20B8B02003014E48B263F657E9CEFAE3@amd64> In-Reply-To: Subject: Re: indexing for distinct search in timestamp based table Date: Fri, 29 Aug 2008 16:32:18 +0900 Message-ID: <000501c909a9$5db8b980$192a2c80$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C909F4.CDA06180" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AckJYhkjIgW80LcyRUKYxNyaoDCxYQAQQjHw 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: 200808/417 X-Sequence-Number: 31298 This is a multipart message in MIME format. ------=_NextPart_000_0006_01C909F4.CDA06180 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thanks for the suggestions. David's assumption is correct in that this is a log table with no updates or deletes. I tried making it CLUSTERED in our test environment, but it didn't seem to make any difference for my query. It did take about 6 hours to do the transformation, so it would be difficult to find the time to do in production, but I'm sure we could work something out if it really looked beneficial. Unfortunately, as I said, initial tests don't seem to indicate any benefit. I believe that my performance difficulty comes from the need for DISTINCT (or GROUP BY) data. That is, the normal start_time index seems fine for limiting the date range, but when I need to select DISTINCT data from the date range it seems that Postgres still needs to scan the entire limited date range. Unfortunately, we support arbitrary date range queries on this table, so I don't think the partitioning idea is an option for us. What I'm playing with now is creating separate tables to hold the channel_name, ad_name, and player_name data with PRIMARY KEY ids. Since there are very few of these compared to the number of rows in the main table, this will give me a quick way to get the DISTINCT values over the entire data set. My problem then will be reducing that to the DISTINCT values for a limited date range. As a side effect bonus of this I expect the database to shrink considerably as these text fields, although not that long (roughly 20 to 50 characters), are certainly longer than a simple foreign key reference. --Rainer From: scott@edvert.net [mailto:scott@edvert.net] On Behalf Of Scott Carey Sent: Friday, August 29, 2008 8:02 AM To: David Rowley Cc: Rainer Mager; pgsql-performance@postgresql.org Subject: Re: [PERFORM] indexing for distinct search in timestamp based table Another suggestion is to partition the table by date ranges. If most of the range queries occur on particular batches of time, this will make all queries more efficient, and improve locality and efficiency of all indexes on the table. This is more work than simply a table CLUSTER, especially in maintenance overhead, but it will generally help a lot in cases like these. Additionally, if these don't change much after some period of time the tables older than the modification window can be vacuumed, clustered, and reindexed if needed to make them as efficient as possible and maintenance free after that point (other than backups and archives). Another benefit of clustering is in backup / restore. You can incrementally back up only the index partitions that have changed -- for large databases this reduces pg_dump and pg_restore times substantially. To do this you combine regular expressions with the pg_dump "exclude tables" or "include tables" flags. On Thu, Aug 28, 2008 at 3:48 PM, David Rowley wrote: I once also had a similar performance problem when looking for all matching rows between two timestamps. In fact that's why I'm here today. The problem was with MySQL. I had some tables of around 10 million rows and all my searching was timestamp based. MySQL didn't do what I wanted. I found that using a CLUSTERED index with postgresql to be lightning quick. Yet mostly the matching rows I was working with was not much over the 100k mark. I'm wondering if clustering the table on ad_log_start_time will help cut down on random reads. That's if you can afford to block the users while postgresql clusters the table. If you're inserting in order of the start_time column (which I was) then the cluster should almost maintain itself (I think), providing you're not updating or deleting anyway, I'd assume that since it looks like a log table. David. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rainer Mager Sent: 28 August 2008 09:06 To: pgsql-performance@postgresql.org Subject: [PERFORM] indexing for distinct search in timestamp based table I'm looking for some help in speeding up searches. My table is pretty simple (see below), but somewhat large, and continuously growing. Currently it has about 50 million rows. The table is (I know I have excessive indexes, I'm trying to get the appropriate ones and drop the extras): Table "public.ad_log" Column | Type | Modifiers --------------+-----------------------------+------------------------------- ----------------------------- ad_log_id | integer | not null default nextval('ad_log_ad_log_id_seq'::regclass) channel_name | text | not null player_name | text | not null ad_name | text | not null start_time | timestamp without time zone | not null end_time | timestamp without time zone | not null Indexes: "ad_log_pkey" PRIMARY KEY, btree (ad_log_id) "ad_log_channel_name_key" UNIQUE, btree (channel_name, player_name, ad_name, start_time, end_time) "ad_log_ad_and_start" btree (ad_name, start_time) "ad_log_ad_name" btree (ad_name) "ad_log_all" btree (channel_name, player_name, start_time, ad_name) "ad_log_channel_name" btree (channel_name) "ad_log_end_time" btree (end_time) "ad_log_player_and_start" btree (player_name, start_time) "ad_log_player_name" btree (player_name) "ad_log_start_time" btree (start_time) The query I'm trying to speed up is below. In it the tag can be one of channel_name, player_name, or ad_name. I'm actually trying to return the distinct values and I found GROUP BY to be slightly faster than using DISTINCT. Also, any of those fields may be unspecified in the WHERE clauses in which case we use '%', but it seems Postgres optimizes that pretty well. SELECT FROM ad_log WHERE channel_name LIKE :channel_name AND player_name LIKE :player_name AND ad_name LIKE :ad_name AND start_time BETWEEN :start_date AND (date(:end_date) + 1) GROUP BY ORDER BY A typical query is: explain analyze SELECT channel_name FROM ad_log WHERE channel_name LIKE '%' AND ad_name LIKE '%' AND start_time BETWEEN '2008-07-01' AND (date('2008-07-28') + 1) GROUP BY channel_name ORDER BY channel_name; with the result being: QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------- Sort (cost=1163169.02..1163169.03 rows=5 width=10) (actual time=75460.187..75460.192 rows=15 loops=1) Sort Key: channel_name Sort Method: quicksort Memory: 17kB -> HashAggregate (cost=1163168.91..1163168.96 rows=5 width=10) (actual time=75460.107..75460.114 rows=15 loops=1) -> Bitmap Heap Scan on ad_log (cost=285064.30..1129582.84 rows=13434427 width=10) (actual time=8506.250..65771.597 rows=13701296 loops=1) Recheck Cond: ((start_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (start_time <= '2008-07-29'::date)) Filter: ((channel_name ~~ '%'::text) AND (ad_name ~~ '%'::text)) -> Bitmap Index Scan on ad_log_start_time (cost=0.00..281705.70 rows=13434427 width=0) (actual time=8488.443..8488.443 rows=13701296 loops=1) Index Cond: ((start_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (start_time <= '2008-07-29'::date)) Total runtime: 75460.361 ms It seems to me there should be some way to create an index to speed this up, but the various ones I've tried so far haven't helped. Any suggestions would be greatly appreciated. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ------=_NextPart_000_0006_01C909F4.CDA06180 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Thanks for the suggestions.

 

David’s assumption is correct in that this is a log = table with no updates or deletes. I tried making it CLUSTERED in our test environment, but it didn’t seem to make any difference for my = query. It did take about 6 hours to do the transformation, so it would be = difficult to find the time to do in production, but I’m sure we could work = something out if it really looked beneficial. Unfortunately, as I said, initial = tests don’t seem to indicate any benefit.

 

I believe that my performance difficulty comes from the = need for DISTINCT (or GROUP BY) data. That is, the normal start_time index seems = fine for limiting the date range, but when I need to select DISTINCT data = from the date range it seems that Postgres still needs to scan the entire limited = date range.

 

Unfortunately, we support arbitrary date range queries on = this table, so I don’t think the partitioning idea is an option for = us.

 

 

What I’m playing with now is creating separate = tables to hold the channel_name, ad_name, and player_name data with PRIMARY KEY = ids. Since there are very few of these compared to the number of rows in the main = table, this will give me a quick way to get the DISTINCT values over the entire = data set. My problem then will be reducing that to the DISTINCT values for a = limited date range.

 

As a side effect bonus of this I expect the database to = shrink considerably as these text fields, although not that long (roughly 20 to = 50 characters), are certainly longer than a simple foreign key = reference.

 

 

--Rainer

 

 

From:= = scott@edvert.net [mailto:scott@edvert.net] On Behalf Of Scott Carey
Sent: Friday, August 29, 2008 8:02 AM
To: David Rowley
Cc: Rainer Mager; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] indexing for distinct search in timestamp = based table

 

Another suggestion = is to partition the table by date ranges.  If most of the range queries = occur on particular batches of time, this will make all queries more efficient, = and improve locality and efficiency of all indexes on the table.

This is more work than simply a table CLUSTER, especially in maintenance overhead, but it will generally help a lot in cases like these.  Additionally, if these don't change much after some period of time the = tables older than the modification window can be vacuumed, clustered, and = reindexed if needed to make them as efficient as possible and maintenance free after = that point (other than backups and archives).

Another benefit of clustering is in backup / restore.  You can incrementally back up only the index partitions that have changed -- for = large databases this reduces pg_dump and pg_restore times substantially.  = To do this you combine regular expressions with the pg_dump "exclude tables" or "include tables" flags.

On Thu, Aug 28, 2008 at 3:48 PM, David Rowley = <dgrowley@gmail.com> = wrote:

I once also had a similar performance problem when = looking for all matching
rows between two timestamps. In fact that's why I'm here today. The = problem
was with MySQL. I had some tables of around 10 million rows and all = my
searching was timestamp based. MySQL didn't do what I wanted. I found = that
using a CLUSTERED index with postgresql to be lightning quick. Yet = mostly
the matching rows I was working with was not much over the 100k mark. = I'm
wondering if clustering the table on ad_log_start_time will help cut = down on
random reads.

That's if you can afford to block the users while postgresql clusters = the
table.

If you're inserting in order of the start_time column (which I was) then = the
cluster should almost maintain itself (I think), providing you're = not
updating or deleting anyway, I'd assume that since it looks like a = log
table.

David.



-----Original Message-----
From: pgsql-performance-= owner@postgresql.org
[mailto:pgsql-performance-= owner@postgresql.org] On Behalf Of Rainer Mager
Sent: 28 August 2008 09:06
To: pgsql-performance@postgr= esql.org
Subject: [PERFORM] indexing for distinct search in timestamp based = table

I'm looking for some help in speeding up searches. My table is pretty = simple
(see below), but somewhat large, and continuously growing. Currently it = has
about 50 million rows.

The table is (I know I have excessive indexes, I'm trying to get the
appropriate ones and drop the extras):
                    =                     =  Table "public.ad_log"
   Column    |            Type             |
Modifiers
--------------+-----------------------------+----------------------------= ---
-----------------------------
 ad_log_id    | integer         =             | not null default
nextval('ad_log_ad_log_id_seq'::regclass)
 channel_name | text             =            | not null
 player_name  | text             =            | not null
 ad_name      | text         =                | not null
 start_time   | timestamp without time zone | not null
 end_time     | timestamp without time zone | not = null
Indexes:
   "ad_log_pkey" PRIMARY KEY, btree (ad_log_id)
   "ad_log_channel_name_key" UNIQUE, btree = (channel_name, player_name,
ad_name, start_time, end_time)
   "ad_log_ad_and_start" btree (ad_name, = start_time)
   "ad_log_ad_name" btree (ad_name)
   "ad_log_all" btree (channel_name, player_name, start_time, ad_name)
   "ad_log_channel_name" btree (channel_name)
   "ad_log_end_time" btree (end_time)
   "ad_log_player_and_start" btree (player_name, start_time)
   "ad_log_player_name" btree (player_name)
   "ad_log_start_time" btree (start_time)



The query I'm trying to speed up is below. In it the <field> tag = can be one
of channel_name, player_name, or ad_name. I'm actually trying to return = the
distinct values and I found GROUP BY to be slightly faster than = using
DISTINCT. Also, any of those fields may be unspecified in the WHERE = clauses
in which case we use '%', but it seems Postgres optimizes that pretty = well.

SELECT <field> FROM ad_log
       WHERE channel_name LIKE :channel_name
       AND player_name LIKE :player_name
       AND ad_name LIKE :ad_name
       AND start_time BETWEEN :start_date AND (date(:end_date) + 1)
       GROUP BY <field> ORDER BY = <field>


A typical query is:

explain analyze SELECT channel_name FROM ad_log WHERE channel_name LIKE = '%'
AND ad_name LIKE '%' AND start_time BETWEEN '2008-07-01' AND
(date('2008-07-28') + 1) GROUP BY channel_name ORDER BY = channel_name;

with the result being:

QUERY PLAN
-------------------------------------------------------------------------= ---
-------------------------------------------------------------------------= ---
-------
 Sort  (cost=3D1163169.02..1163169.03 rows=3D5 width=3D10) = (actual
time=3D75460.187..75460.192 rows=3D15 loops=3D1)
  Sort Key: channel_name
  Sort Method:  quicksort  Memory: 17kB
  ->  HashAggregate  (cost=3D1163168.91..1163168.96 = rows=3D5 width=3D10) (actual
time=3D75460.107..75460.114 rows=3D15 loops=3D1)
        ->  Bitmap Heap Scan on ad_log  (cost=3D285064.30..1129582.84
rows=3D13434427 width=3D10) (actual time=3D8506.250..65771.597 = rows=3D13701296
loops=3D1)
              Recheck Cond: = ((start_time >=3D '2008-07-01
00:00:00'::timestamp without time zone) AND (start_time <=3D
'2008-07-29'::date))
              Filter: ((channel_name = ~~ '%'::text) AND (ad_name ~~
'%'::text))
              ->  Bitmap = Index Scan on ad_log_start_time
(cost=3D0.00..281705.70 rows=3D13434427 width=3D0) (actual = time=3D8488.443..8488.443
rows=3D13701296 loops=3D1)
                    = Index Cond: ((start_time >=3D '2008-07-01
00:00:00'::timestamp without time zone) AND (start_time <=3D
'2008-07-29'::date))
 Total runtime: 75460.361 ms


It seems to me there should be some way to create an index to speed this = up,
but the various ones I've tried so far haven't helped. Any suggestions = would
be greatly appreciated.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgr= esql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


--
Sent via pgsql-performance mailing list (pgsql-performance@postgr= esql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

 

------=_NextPart_000_0006_01C909F4.CDA06180--