Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 1802D64FDD3 for ; Wed, 26 Nov 2008 00:07:51 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34053-05 for ; Wed, 26 Nov 2008 00:07:48 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by mail.postgresql.org (Postfix) with ESMTP id 82BC064FDF1 for ; Wed, 26 Nov 2008 00:07:47 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so138320wah.19 for ; Tue, 25 Nov 2008 20:07:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=Tk3QhOfkfw6tfsoNgs4B729OwOW80UpRzWntWoHsclE=; b=YJtd4YqlC8RlBtoBHncfpAkctccDL/Fl6Ztcy0F1pxx7HL8sGknjub914PCP22Yr4u GpZoV43yXglyVKSeBOyTuD6rsdwHwnxnIfs/aIVTTst/eKUV687NzfI2T+sWvOwQx7nz lgyHTISq6s73tUaA1IDFXOnQ8vJdNKOdaEeRY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=EqItwyHJRmP4Z4HOt0VfZ0lVqkLC0MTj5oaPi75m4vJSkbLrZvNMMTfLub8KNEF9rS pUJ6gTSGLEphJxFUZQE7A/o3fWlyqn4dfr/zlwJB17DvIVYZLGyzD9q+Pd3+1XM1Gzh5 q8wsyRdajBe2iLYcBPCLVwEzBKmEnYaNErGi4= Received: by 10.114.134.20 with SMTP id h20mr3085368wad.116.1227672466668; Tue, 25 Nov 2008 20:07:46 -0800 (PST) Received: by 10.115.22.20 with HTTP; Tue, 25 Nov 2008 20:07:46 -0800 (PST) Message-ID: Date: Tue, 25 Nov 2008 20:07:46 -0800 From: "Greg Jaman" To: pgsql-performance@postgresql.org Subject: Partition table query performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9579_10417737.1227672466655" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.411 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200811/257 X-Sequence-Number: 31988 ------=_Part_9579_10417737.1227672466655 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a problem with partitioning and I'm wondering if anyone can provide some insight. I'm trying to find the max value of a column across multiple partitions. The query against the partition set is quite slow while queries against child partitions is very fast! I setup a basic Range Partition table definition. A parent table: Data { dataID, sensorID, value, ts } child tables Data_YYYY_WEEKNO { dataID, sensorID, value, ts} inherited from Data Each child tables has a primary key index on dataID and a composite index on (sensorID, ts). Each child has check constraints for the week range identified in the table name (non overlapping) I want to perform a simple operation: select the max ts (timestamp) giving a sensorID. Given my indexs on the table, this should be a simple and fast operation. DB=# EXPLAIN ANALYZE select max(ts) from "Data" where valid=true and "sensorID"=8293 ; -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=334862.92..334862.93 rows=1 width=8) (actual time=85183.381..85183.383 rows=1 loops=1) -> Append (cost=2.30..329397.68 rows=2186096 width=8) (actual time=1.263..76592.755 rows=2205408 loops=1) -> Bitmap Heap Scan on "Data" (cost=2.30..8.84 rows=3 width=8) (actual time=0.027..0.027 rows=0 loops=1) Recheck Cond: ("sensorID" = 8293) Filter: valid -> Bitmap Index Scan on "def_data_sensorID_ts" (cost=0.00..2.30 rows=6 width=0) (actual time=0.021..0.021 rows=0 loops=1) Index Cond: ("sensorID" = 8293) -> *Index Scan using "Data_2008_01_sensorID_ts_index" on "Data_2008_01" "Data"* (cost=0.00..4.27 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1) Index Cond: ("sensorID" = 8293) Filter: valid -> *Bitmap Heap Scan on "Data_2008_02" "Data"* (cost=3.01..121.08 rows=98 width=8) (actual time=0.017..0.017 rows=0 loops=1) Recheck Cond: ("sensorID" = 8293) Filter: valid -> Bitmap Index Scan on "Data_2008_02_sensorID_ts_index" (cost=0.00..2.99 rows=98 width=0) (actual time=0.011..0.011 rows=0 loops=1) Index Cond: ("sensorID" = 8293) . . . (omitted a list of all partitions with same as data above) . Total runtime: 85188.694 ms When I query against a specific partition: DB=# EXPLAIN ANALYZE select max(ts) from "Data_2008_48" where valid=true and "sensorID"=8293 ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=0.10..0.11 rows=1 width=0) (actual time=3.830..3.832 rows=1 loops=1) InitPlan -> Limit (cost=0.00..0.10 rows=1 width=8) (actual time=3.817..3.819 rows=1 loops=1) -> Index Scan Backward using "Data_2008_48_sensorID_ts_index" on "Data_2008_48" (cost=0.00..15304.55 rows=148959 width=8) (actual time=3.813..3.813 rows=1 loops=1) Index Cond: ("sensorID" = 8293) Filter: ((ts IS NOT NULL) AND valid) Total runtime: 0.225 ms The query plan against the child partition makes sense - Uses the index to find the max value. The query plan for the partitions uses a combination of bitmap heap scans and index scans. Why would the query plan choose to use a bitmap heap scan after bitmap index scan or is that the best choice? (what is it doing?) and what can I do to speed up this query? As a sanity check I did a union query of all partitions to find the max(ts). My manual union query executed in 13ms vs the query against the parent table that was 85,188ms!!!. Greg Jaman ------=_Part_9579_10417737.1227672466655 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
I have a problem with partitioning and I'm wondering if anyone can = provide some insight.   I'm trying to find the max value of a= column across multiple partitions.  The query against the partition s= et is quite slow while queries against child partitions is very fast!


I setup a basic Range Partition table definition.
   = A parent table:  Data {  dataID, sensorID, value, ts }
 &= nbsp; child tables   Data_YYYY_WEEKNO { dataID, sensorID, value, = ts}  inherited from Data
       =    Each child tables has a primary key index on dataID and a comp= osite index on (sensorID, ts).
          Each child has check= constraints for the week range identified in the table name (non overlappi= ng)

I want to perform a simple operation:  select the max ts (t= imestamp) giving a sensorID.  Given my indexs on the table, this shoul= d be a simple and fast operation.


DB=3D# EXPLAIN ANALYZE  select max(ts) from "Data" w= here valid=3Dtrue and "sensorID"=3D8293 ;
--------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------=
 Aggregate  (cost=3D334862.92..334862.93 rows=3D1 width=3D8) (act= ual time=3D85183.381..85183.383 rows=3D1 loops=3D1)
   ->&n= bsp; Append  (cost=3D2.30..329397.68 rows=3D2186096 width=3D8) (actual= time=3D1.263..76592.755 rows=3D2205408 loops=3D1)
         ->  Bitmap Heap Sc= an on "Data"  (cost=3D2.30..8.84 rows=3D3 width=3D8) (actual= time=3D0.027..0.027 rows=3D0 loops=3D1)
     &= nbsp;         Recheck Cond: ("= sensorID" =3D 8293)
        = ;       Filter: valid
            &nb= sp;  ->  Bitmap Index Scan on "def_data_sensorID_ts"=   (cost=3D0.00..2.30 rows=3D6 width=3D0) (actual time=3D0.021..0.021 r= ows=3D0 loops=3D1)
         = ;            Index C= ond: ("sensorID" =3D 8293)
         ->  Index Scan = using "Data_2008_01_sensorID_ts_index" on "Data_2008_01"= ; "Data"  (cost=3D0.00..4.27 rows=3D1 width=3D8) (actual= time=3D0.014..0.014 rows=3D0 loops=3D1)
     &= nbsp;         Index Cond: ("se= nsorID" =3D 8293)
            &nb= sp;  Filter: valid
        = ->  Bitmap Heap Scan on "Data_2008_02" "Data&quo= t;  (cost=3D3.01..121.08 rows=3D98 width=3D8) (actual time=3D0.017= ..0.017 rows=3D0 loops=3D1)
       &n= bsp;       Recheck Cond: ("sensorID"= ; =3D 8293)
            &nb= sp;  Filter: valid
        =        ->  Bitmap Index Scan on "= ;Data_2008_02_sensorID_ts_index"  (cost=3D0.00..2.99 rows=3D98 wi= dth=3D0) (actual time=3D0.011..0.011 rows=3D0 loops=3D1)
  &nb= sp;            =       Index Cond: ("sensorID" =3D 8293)<= br> .
.
. (omitted a list of all partitions with same as data above)
.=
 Total runtime: 85188.694 ms




When I query again= st a specific partition:


DB=3D# EXPLAIN ANALYZE  select max= (ts) from "Data_2008_48" where valid=3Dtrue and "sensorID&qu= ot;=3D8293 ;
            &nb= sp;            =             &nb= sp;            =             &nb= sp;            =         QUERY PLAN   &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;    
--------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------
 Result  (cost=3D0.10..0.11 rows=3D1 width=3D0) (actual time=3D3.= 830..3.832 rows=3D1 loops=3D1)
   InitPlan
  &nbs= p;  ->  Limit  (cost=3D0.00..0.10 rows=3D1 width=3D8) (ac= tual time=3D3.817..3.819 rows=3D1 loops=3D1)
    &nb= sp;      ->  Index Scan Backward using &qu= ot;Data_2008_48_sensorID_ts_index" on "Data_2008_48"  (= cost=3D0.00..15304.55 rows=3D148959 width=3D8) (actual time=3D3.813..3.813 = rows=3D1 loops=3D1)
            &nb= sp;    Index Cond: ("sensorID" =3D 8293)
 =             &nb= sp;   Filter: ((ts IS NOT NULL) AND valid)
 Total runtime= : 0.225 ms


The query plan against the child partition makes sens= e - Uses the index to find the max value.  The query plan for the part= itions uses a combination of bitmap heap scans and index scans. 
Why would the query plan choose to use a bitmap heap scan after bitmap inde= x scan or is that the best choice?  (what is it doing?) and what can I= do to speed up this query?

As a sanity check I did a union query of= all partitions to find the max(ts). My manual union query executed in 13ms= vs the query against the parent table that was 85,188ms!!!.  


Greg Jaman
 



------=_Part_9579_10417737.1227672466655--