Обсуждение: BUG #15045: Partitioning not working as intended
The following bug has been logged on the website:
Bug reference: 15045
Logged by: Tomasz Kamiński
Email address: mlodikk@gmail.com
PostgreSQL version: 10.1
Operating system: Centos 6
Description:
Hi,
i found something wrong with new partition mechanism in postgresql 10.1
I have table partitioned by time stored as epoch time. Each partition is one
month (my time is GMT+1).
I tried to make script, that's count rows from last month. I wrote something
like this:
SELECT count(*) FROM trends where
clock between (extract(epoch from (date_trunc ('month', CURRENT_DATE)-
interval '1 month')::date)::integer - 3600 )
and (extract(epoch from date_trunc ('month', CURRENT_DATE)::date)::integer -
3601 )
Problem is, that all partitions are scanned.
But when i write explicite values like this, only one partition is scaned as
intended:
SELECT count(*) FROM public.trends where
clock between 1514761200 and 1517439599
Bellow are my execution analyze plans, for both selects:
values from extract
[
{
"Execution Time": 3446.662,
"Planning Time": 4.129,
"Plan": {
"Partial Mode": "Finalize",
"Node Type": "Aggregate",
"Strategy": "Plain",
"Actual Total Time": 3426.528,
"Shared Hit Blocks": 10898,
"Plans": [
{
"Node Type": "Gather",
"Actual Total Time": 3426.521,
"Shared Hit Blocks": 10898,
"Plans": [
{
"Partial Mode": "Partial",
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 3417.768,
"Shared Read Blocks": 11365,
"Local Written Blocks": 0,
"Actual Startup Time": 3417.768,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10884,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 3417.768,
"Shared Read Blocks": 11354,
"Local Written Blocks": 0,
"Actual Startup Time": 3417.768,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10868,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Aggregate",
"Strategy": "Plain",
"Actual Total Time": 3419.766,
"Shared Hit Blocks": 32650,
"Plans": [
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 3289.22,
"Shared Read Blocks": 11365,
"Local Written Blocks": 0,
"Actual Startup Time": 514.603,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1255242,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10884,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 3289.744,
"Shared Read Blocks": 11354,
"Local Written Blocks": 0,
"Actual Startup Time": 514.604,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1258654,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10868,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Append",
"Actual Total Time": 3292.375,
"Shared Hit Blocks": 32650,
"Plans": [
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.021,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.021,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.022,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.022,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.207,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.023,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.023,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2017_08_older_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2017_08_older.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_08_older.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2017_08_older",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2017_08_older",
"Recheck Cond": "((trends_2017_08_older.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_08_older.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.207,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.001,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.001,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.001,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.001,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.003,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.008,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.008,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2017_09_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2017_09.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_09.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2017_09",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2017_09",
"Recheck Cond": "((trends_2017_09.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_09.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.003,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.002,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.002,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.002,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.002,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.003,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.004,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.004,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2017_10_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2017_10.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_10.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2017_10",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2017_10",
"Recheck Cond": "((trends_2017_10.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_10.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.003,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 244.549,
"Shared Read Blocks": 5183,
"Local Written Blocks": 0,
"Actual Startup Time": 244.549,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 244.553,
"Shared Read Blocks": 5190,
"Local Written Blocks": 0,
"Actual Startup Time": 244.553,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Index Only Scan",
"Actual Total Time": 246.361,
"Shared Hit Blocks": 0,
"Schema": "public",
"Scan Direction": "Forward",
"Shared Read Blocks": 15870,
"Relation Name": "trends_2017_11",
"Local Hit Blocks": 0,
"Heap Fetches": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 105215.94,
"Rows Removed by Index Recheck": 0,
"Actual Startup Time": 246.361,
"Alias": "trends_2017_11",
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name": "trends_2017_11_uidx_itemid_clock",
"Startup Cost": 0.47,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1,
"Index Cond": "((trends_2017_11.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_11.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 269.987,
"Shared Read Blocks": 5844,
"Local Written Blocks": 0,
"Actual Startup Time": 269.987,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 1,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 269.989,
"Shared Read Blocks": 5802,
"Local Written Blocks": 0,
"Actual Startup Time": 269.989,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Index Only Scan",
"Actual Total Time": 269.989,
"Shared Hit Blocks": 1,
"Schema": "public",
"Scan Direction": "Forward",
"Shared Read Blocks": 17458,
"Relation Name": "trends_2017_12",
"Local Hit Blocks": 0,
"Heap Fetches": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 115740.78,
"Rows Removed by Index Recheck": 0,
"Actual Startup Time": 269.989,
"Alias": "trends_2017_12",
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name": "trends_2017_12_uidx_itemid_clock",
"Startup Cost": 0.47,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1,
"Index Cond": "((trends_2017_12.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2017_12.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 2600.397,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.038,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1255242,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10883,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 2601.082,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.034,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1258654,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10868,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Seq Scan",
"Actual Total Time": 2600.743,
"Shared Hit Blocks": 32636,
"Schema": "public",
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_01",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 122715.74,
"Actual Startup Time": 0.036,
"Filter": "((trends_2018_01.clock <=
((date_part('epoch'::text, ((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone))::date)::timestamp without time
zone))::integer - 3601)) AND (trends_2018_01.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer -
3600)))",
"Alias": "trends_2018_01",
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1566318,
"Parallel Aware": true,
"Actual Rows": 1254740,
"Parent Relationship": "Member",
"Actual Loops": 3,
"Rows Removed by Filter": 0
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 13.934,
"Shared Read Blocks": 338,
"Local Written Blocks": 0,
"Actual Startup Time": 13.934,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 13.912,
"Shared Read Blocks": 362,
"Local Written Blocks": 0,
"Actual Startup Time": 13.912,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Index Only Scan",
"Actual Total Time": 13.934,
"Shared Hit Blocks": 0,
"Schema": "public",
"Scan Direction": "Forward",
"Shared Read Blocks": 1042,
"Relation Name": "trends_2018_02",
"Local Hit Blocks": 0,
"Heap Fetches": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 6299.74,
"Rows Removed by Index Recheck": 0,
"Actual Startup Time": 13.934,
"Alias": "trends_2018_02",
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name": "trends_2018_02_uidx_itemid_clock",
"Startup Cost": 0.46,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1,
"Index Cond": "((trends_2018_02.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_02.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.03,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.03,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.033,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.033,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.031,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.021,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.021,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_03_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_03.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_03.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_03",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_03",
"Recheck Cond": "((trends_2018_03.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_03.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.031,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.016,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.016,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.016,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.016,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.016,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.01,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.01,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_04_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_04.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_04.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_04",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_04",
"Recheck Cond": "((trends_2018_04.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_04.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.016,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.011,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.011,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.011,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.011,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.012,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.009,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.009,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_05_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_05.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_05.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_05",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_05",
"Recheck Cond": "((trends_2018_05.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_05.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.012,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.015,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.015,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.015,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.015,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.014,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.007,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.007,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_06_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_06.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_06.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_06",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_06",
"Recheck Cond": "((trends_2018_06.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_06.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.014,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.01,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.01,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.01,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.01,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.011,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.007,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.007,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_07_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_07.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_07.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_07",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_07",
"Recheck Cond": "((trends_2018_07.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_07.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.011,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.014,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.014,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.014,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.014,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.013,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.007,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.007,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_08_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_08.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_08.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_08",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_08",
"Recheck Cond": "((trends_2018_08.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_08.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.013,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.01,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.01,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.01,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.01,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.011,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.007,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.007,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_09_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_09.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_09.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_09",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_09",
"Recheck Cond": "((trends_2018_09.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_09.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.011,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.015,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.015,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.015,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.015,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.014,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.007,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.007,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_10_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_10.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_10.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_10",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_10",
"Recheck Cond": "((trends_2018_10.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_10.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.014,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.011,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.011,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.011,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.011,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.012,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.009,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.009,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_11_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_11.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_11.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_11",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_11",
"Recheck Cond": "((trends_2018_11.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_11.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.012,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
},
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.013,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.013,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 0.013,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.013,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 0,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 0,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Exact Heap Blocks": 0,
"Node Type": "Bitmap Heap Scan",
"Actual Total Time": 0.013,
"Shared Hit Blocks": 1,
"Schema": "public",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Actual Total Time": 0.007,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 14.39,
"Actual Startup Time": 0.007,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Index Name":
"trends_2018_12_uidx_itemid_clock",
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 5,
"Index Cond": "((trends_2018_12.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_12.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Parallel Aware": false,
"Actual Rows": 0,
"Parent Relationship": "Outer",
"Actual Loops": 1
}
],
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_12",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 22.97,
"Rows Removed by Index Recheck": 0,
"Lossy Heap Blocks": 0,
"Alias": "trends_2018_12",
"Recheck Cond": "((trends_2018_12.clock >=
((date_part('epoch'::text, (((date_trunc('month'::text,
(CURRENT_DATE)::timestamp with time zone) - '1
mon'::interval))::date)::timestamp without time zone))::integer - 3600)) AND
(trends_2018_12.clock <= ((date_part('epoch'::text,
((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time
zone))::date)::timestamp without time zone))::integer - 3601)))",
"Temp Read Blocks": 0,
"Actual Startup Time": 0.013,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 3,
"Parallel Aware": true,
"Actual Rows": 0,
"Parent Relationship": "Member",
"Actual Loops": 3
}
],
"Shared Read Blocks": 34370,
"Temp Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Hit Blocks": 0,
"Plan Width": 0,
"Actual Loops": 3,
"Actual Startup Time": 516.603,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Startup Cost": 14.39,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1566360,
"Parallel Aware": false,
"Actual Rows": 1254740,
"Parent Relationship": "Outer",
"Total Cost": 350270.85
}
],
"Shared Read Blocks": 34370,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 8,
"Actual Loops": 3,
"Actual Startup Time": 3419.766,
"Temp Read Blocks": 0,
"Output": [
"PARTIAL count(*)"
],
"Local Read Blocks": 0,
"Startup Cost": 354186.75,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1,
"Parallel Aware": false,
"Actual Rows": 1,
"Parent Relationship": "Outer",
"Total Cost": 354186.76
}
],
"Shared Read Blocks": 11651,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 8,
"Actual Loops": 1,
"Actual Startup Time": 3426.489,
"Single Copy": false,
"Temp Read Blocks": 0,
"Output": [
"(PARTIAL count(*))"
],
"Workers Launched": 2,
"Local Read Blocks": 0,
"Startup Cost": 355186.75,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Workers Planned": 2,
"Local Written Blocks": 0,
"Plan Rows": 2,
"Parallel Aware": false,
"Actual Rows": 3,
"Parent Relationship": "Outer",
"Total Cost": 355186.96
}
],
"Shared Read Blocks": 11651,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 8,
"Actual Loops": 1,
"Actual Startup Time": 3426.528,
"Temp Read Blocks": 0,
"Output": [
"count(*)"
],
"Local Read Blocks": 0,
"Startup Cost": 355186.96,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1,
"Parallel Aware": false,
"Actual Rows": 1,
"Total Cost": 355186.97
},
"Triggers": []
}
]
Explicite values:
[
{
"Execution Time": 600.527,
"Planning Time": 0.565,
"Plan": {
"Partial Mode": "Finalize",
"Node Type": "Aggregate",
"Strategy": "Plain",
"Actual Total Time": 594.074,
"Shared Hit Blocks": 10889,
"Plans": [
{
"Node Type": "Gather",
"Actual Total Time": 594.069,
"Shared Hit Blocks": 10889,
"Plans": [
{
"Partial Mode": "Partial",
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 590.84,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 590.84,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10855,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 590.872,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 590.872,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10892,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Aggregate",
"Strategy": "Plain",
"Actual Total Time": 591.826,
"Shared Hit Blocks": 32636,
"Plans": [
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 471.098,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.016,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1257874,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10855,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 471.05,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.017,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1258272,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10892,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Append",
"Actual Total Time": 470.468,
"Shared Hit Blocks": 32636,
"Plans": [
{
"Workers": [
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 323.643,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.015,
"Worker Number": 0,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1257874,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10855,
"Local Read Blocks": 0,
"Actual Loops": 1
},
{
"Shared Dirtied Blocks": 0,
"Actual Total Time": 323.445,
"Shared Read Blocks": 0,
"Local Written Blocks": 0,
"Actual Startup Time": 0.015,
"Worker Number": 1,
"Temp Read Blocks": 0,
"Local Hit Blocks": 0,
"Actual Rows": 1258272,
"Shared Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Shared Hit Blocks": 10892,
"Local Read Blocks": 0,
"Actual Loops": 1
}
],
"Node Type": "Seq Scan",
"Actual Total Time": 322.334,
"Shared Hit Blocks": 32636,
"Schema": "public",
"Shared Read Blocks": 0,
"Relation Name": "trends_2018_01",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 0,
"Total Cost": 56135.06,
"Actual Startup Time": 0.017,
"Filter": "((trends_2018_01.clock >= 1514761200) AND
(trends_2018_01.clock <= 1517439599))",
"Alias": "trends_2018_01",
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1566318,
"Parallel Aware": true,
"Actual Rows": 1254740,
"Parent Relationship": "Member",
"Actual Loops": 3,
"Rows Removed by Filter": 0
}
],
"Shared Read Blocks": 0,
"Temp Written Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Hit Blocks": 0,
"Plan Width": 0,
"Actual Loops": 3,
"Actual Startup Time": 0.018,
"Temp Read Blocks": 0,
"Local Read Blocks": 0,
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1566318,
"Parallel Aware": false,
"Actual Rows": 1254740,
"Parent Relationship": "Outer",
"Total Cost": 56135.06
}
],
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 8,
"Actual Loops": 3,
"Actual Startup Time": 591.826,
"Temp Read Blocks": 0,
"Output": [
"PARTIAL count(*)"
],
"Local Read Blocks": 0,
"Startup Cost": 60050.86,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1,
"Parallel Aware": false,
"Actual Rows": 1,
"Parent Relationship": "Outer",
"Total Cost": 60050.87
}
],
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 8,
"Actual Loops": 1,
"Actual Startup Time": 594.063,
"Single Copy": false,
"Temp Read Blocks": 0,
"Output": [
"(PARTIAL count(*))"
],
"Workers Launched": 2,
"Local Read Blocks": 0,
"Startup Cost": 61050.86,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Workers Planned": 2,
"Local Written Blocks": 0,
"Plan Rows": 2,
"Parallel Aware": false,
"Actual Rows": 3,
"Parent Relationship": "Outer",
"Total Cost": 61051.07
}
],
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 8,
"Actual Loops": 1,
"Actual Startup Time": 594.074,
"Temp Read Blocks": 0,
"Output": [
"count(*)"
],
"Local Read Blocks": 0,
"Startup Cost": 61051.07,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 1,
"Parallel Aware": false,
"Actual Rows": 1,
"Total Cost": 61051.08
},
"Triggers": []
}
]
On Fri, Feb 2, 2018 at 1:57 AM, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15045
Logged by: Tomasz Kamiński
Email address: mlodikk@gmail.com
PostgreSQL version: 10.1
Operating system: Centos 6
Description:
Hi,
i found something wrong with new partition mechanism in postgresql 10.1
I have table partitioned by time stored as epoch time. Each partition is one
month (my time is GMT+1).
I tried to make script, that's count rows from last month. I wrote something
like this:
SELECT count(*) FROM trends where
clock between (extract(epoch from (date_trunc ('month', CURRENT_DATE)-
interval '1 month')::date)::integer - 3600 )
and (extract(epoch from date_trunc ('month', CURRENT_DATE)::date)::integer -
3601 )
Problem is, that all partitions are scanned.
Partition pruning has to happen at planning time, but the time when the query is executed can differ from the time it was planned, so pruning can't be done based on expressions like CURRENT_DATE. This might be improved in the future.
Cheers,
Jeff