Re: Auto-clustering?

[Filip RembiaÅkowski <filip.rembialkowski@xxxxxxxxx>]

2010/12/17 Royce Ausburn <royce@xxxxxxxxxxx>

Hi all,

I have a table that in the typical case holds two minute sample data for a few thousand sources. ÂOften we need to report on these data for a particular source over a particular time period and we're finding this query tends to get a bit slow.

The structure of the table:

ÂÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Table "public.sample"
  ÂColumn    |      Type      |          ÂModifiers         Â
-------------------+--------------------------+-------------------------------------------------
Âclient      Â| integer         Â| not null
Âaggregateid    | bigint          | not null
Âsample      Â| bigint          | not null default nextval('samplekey'::regclass)
Âcustomer     Â| integer         Â|Â
Âperiod      Â| integer         Â| not null
Âstarttime     | integer         Â| not null
Âduration     Â| integer         Â| not null
Âip        Â| text           |Â
Âtariff      Â| integer         Â|Â
Âbytessentrate   | bigint          |Â
Âbytessent     | bigint          |Â
Âbytesreceived   | bigint          |Â
Âpackets      | integer         Â| not null
Âqueuetype     | integer         Â| not null default 0
Âcollection    Â| integer         Â|Â
Âbytesreceivedrate | bigint          |Â
Âgreatestrate   Â| bigint          |Â
Âinvalidated    | timestamp with time zone |Â
Indexes:
ÂÂ Â"sample_pkey" PRIMARY KEY, btree (sample)
ÂÂ Â"sample_collection_starttime_idx" btree (collection, starttime)
ÂÂ Â"sample_customer_starttime_idx" btree (customer, starttime)
ÂÂ Â"sample_sample_idx" btree (client, sample)
Foreign-key constraints:
ÂÂ Â"sample_client_fkey" FOREIGN KEY (client) REFERENCES client(client)

fc=# explain Âanalyse select collection, period, tariff, sum(bytesSent), sum(bytesReceived), sum(packets), max(sample), (starttime / 3600) * 3600 as startchunk from sample_20101001 where starttime between 1287493200 and 1290171599 Âand collection=128 Â Âand ip = '10.9.125.207' group by startchunk, tariff, collection, period; Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â QUERY PLAN Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

ÂHashAggregate Â(cost=34959.01..34959.03 rows=1 width=44) (actual time=67047.850..67047.850 rows=0 loops=1)

ÂÂ -> ÂBitmap Heap Scan on sample_20101001 Â(cost=130.56..34958.91 rows=5 width=44) (actual time=67047.847..67047.847 rows=0 loops=1)

ÂÂ Â Â Â Recheck Cond: ((collection = 128) AND (starttime >= 1287493200) AND (starttime <= 1290171599))

ÂÂ Â Â Â Filter: (ip = '10.9.125.207'::text)

ÂÂ Â Â Â -> ÂBitmap Index Scan on sample_20101001_collection_starttime_idx Â(cost=0.00..130.56 rows=9596 width=0) (actual time=9806.115..9806.115 rows=6830 loops=1)

ÂÂ Â Â Â Â Â Â Index Cond: ((collection = 128) AND (starttime >= 1287493200) AND (starttime <= 1290171599))

ÂTotal runtime: 67048.201 ms

(7 rows)

how about (auto)vacuuming?
Â

I figure at most there should only be ~20,000 rows to be read from disk, and I expect that the index is doing a pretty good job of making sure only the rows that need reading are read. inclusion of the ip in the query is almost redundant as most of the time an ip has its own collection.... ÂMy suspicion is that the rows that we're interested in are very sparsely distributed on disk, so we're having to read too many pages for the query...

you can test this suspicion in very simple way:
- create test table (like yours including indexes including constraints, but with no data)
- insert into test select * from yours order by
- analyze test tablee available
- test the query on the new table

If new query is much faster, and if you have intensive random UPD/DEL/INS activity, periodic CLUSTER could be a good idea...
but it depends on actual usage patterns (SELECT/modify ratio, types of updates, and so on).

Â

All of the queries on this table are reporting on a single collection, so ideally a collection's data would all be stored in the same part of the disk... or at least clumped together. ÂThis can be achieved using "cluster", however as far as I know there's no automated, non-cronesque means of clustering and having the table become unusable during the cluster is not ideal.

cron is a way of automation, isn't it :-)

Â

Â

I've considered partitioning, but I don't think that's going to give the effect I need. ÂApparently clustering is only going to scale to a few dozen child tables, so that's only going to give one order of magnitude performance for significant complexity.

Â

regarding partitioning: I guess it starts to make sense around 10M rows or 10G Bytes in one table.

regarding clustering: it does not help with index bloat.

and finally, you did not specify what PostgreSQL version are you using.


cheers,
Filip