Thread: [GENERAL] Slow queries on very big (and partitioned) table
Hu guys, we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a table. Table is partitioned by day, with indexes on partitioned table. Also a simple query (for example i want to search log occurred in a specific day), is immediate in tha partitioned table(table_2017_02_15) but *extremely* slow in global table. Where am i wrong? Shall i create global index? Thank you! /F
Please share us an explain analyze of your query and \d+ of your table
2017-02-20 13:33 GMT+01:00 Job <Job@colliniconsulting.it>:
Hu guys,
we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a table.
Table is partitioned by day, with indexes on partitioned table.
Also a simple query (for example i want to search log occurred in a specific day), is immediate in tha partitioned table (table_2017_02_15) but *extremely* slow in global table.
Where am i wrong?
Shall i create global index?
Thank you!
/F
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Greetings, * Job (Job@colliniconsulting.it) wrote: > we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a table. > Table is partitioned by day, with indexes on partitioned table. You probably shouldn't be partitioning by day for such a small dataset, unless you've only got a few days worth of data that make up those 800m records. Having hundreds of partitions leads to slow query planning time. There is work happening to improve on this by having declarative partitions instead of using CHECK constraints and the constrain exclusion mechanism. Thanks! Stephen
Attachment
Hi,
here is primary a partitioned table (for 20/2/2017 logs):
flashstart=# \d webtraffic_archive_day_2017_02_20;
Table "public.webtraffic_archive_day_2017_02_20"
Column | Type | Modifiers
-----------+-----------------------------+--------------------------------------------------------------------------------
id | numeric(1000,1) | not null default
function_get_next_sequence('webtraffic_archive_id_seq'::text)
timestamp | timestamp without time zone |
domain | character varying(255) |
action | integer |
profile | character varying(50) |
accessi | integer |
url | text |
Indexes:
"webtraffic_archive_day_2017_02_20_action_wbidx" btree (action)
"webtraffic_archive_day_2017_02_20_domain_wbidx" btree (domain)
"webtraffic_archive_day_2017_02_20_profile_wbidx" btree (profile)
"webtraffic_archive_day_2017_02_20_timestamp_date_wbidx" brin (("timestamp"::date))
"webtraffic_archive_day_2017_02_20_timestamp_time_wbidx" btree (("timestamp"::time without time zone))
"webtraffic_archive_day_2017_02_20_timestamp_wbidx" brin ("timestamp")
and here is "master" table:
Column | Type | Modifiers
-----------+-----------------------------+--------------------------------------------------------------------------------
id | numeric(1000,1) | not null default
function_get_next_sequence('webtraffic_archive_id_seq'::text)
timestamp | timestamp without time zone |
domain | character varying(255) |
action | integer |
profile | character varying(50) |
accessi | integer |
url | text |
Indexes:
"keywebrecord_archive" PRIMARY KEY, btree (id)
This is the query planner:
explain analyze
select * from webtraffic_archive where timestamp::date='2017-02-20' and profile='f62467'
And here is the results (after lots of seconds). Index seems to work. The query on a partitioned table is very fast,
theproblem is on the entire table.
Thank you!
/F
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..12.57 rows=10 width=71) (actual time=1319111.913..1319111.973 rows=10 loops=1)
-> Append (cost=0.00..144674.15 rows=115133 width=71) (actual time=1319111.909..1319111.964 rows=10 loops=1)
-> Seq Scan on webtraffic_archive (cost=0.00..0.00 rows=1 width=953) (actual time=0.006..0.006 rows=0
loops=1)
Filter: (((profile)::text = 'f62467'::text) AND (("timestamp")::date = '2017-02-20'::date))
-> Index Scan using webtraffic_archive_day_2016_12_25_profile_composed_wbidx on
webtraffic_archive_day_2016_12_25 (cost=0.56..58.08 rows=1 width=71) (actual time=0.109..0.109 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2016_12_26_profile_composed_wbidx on
webtraffic_archive_day_2016_12_26 (cost=0.56..58.08 rows=1 width=70) (actual time=0.084..0.084 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2016_12_27_profile_composed_wbidx on
webtraffic_archive_day_2016_12_27 (cost=0.56..58.08 rows=1 width=70) (actual time=0.076..0.076 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2016_12_28_profile_composed_wbidx on
webtraffic_archive_day_2016_12_28 (cost=0.56..58.08 rows=1 width=70) (actual time=0.069..0.069 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2016_12_29_profile_composed_wbidx on
webtraffic_archive_day_2016_12_29 (cost=0.56..58.08 rows=1 width=70) (actual time=0.069..0.069 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2016_12_30_profile_composed_wbidx on
webtraffic_archive_day_2016_12_30 (cost=0.56..58.08 rows=1 width=69) (actual time=0.105..0.105 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2016_12_31_profile_composed_wbidx on
webtraffic_archive_day_2016_12_31 (cost=0.56..58.08 rows=1 width=70) (actual time=0.074..0.074 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2017_01_01_profile_composed_wbidx on
webtraffic_archive_day_2017_01_01 (cost=0.56..58.08 rows=1 width=70) (actual time=0.073..0.073 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Index Scan using webtraffic_archive_day_2017_01_02_profile_composed_wbidx on
webtraffic_archive_day_2017_01_02 (cost=0.42..57.94 rows=1 width=70) (actual time=0.060..0.060 rows=0 loops=1)
Index Cond: ((("timestamp")::date = '2017-02-20'::date) AND ((profile)::text = 'f62467'::text))
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_03 (cost=160.00..200.02 rows=1 width=70) (actual
time=147.369..147.369rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 64434
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=648
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_03_timestamp_date_wbidx (cost=0.00..160.00
rows=1width=0) (actual time=0.822..0.822 rows=7680 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_04 (cost=120.00..160.02 rows=1 width=70) (actual
time=0.123..0.123rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_04_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=0.120..0.120 rows=0 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_05 (cost=320.00..360.02 rows=1 width=70) (actual
time=1.772..1.772rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_05_timestamp_date_wbidx (cost=0.00..320.00
rows=1width=0) (actual time=1.765..1.765 rows=0 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_06 (cost=280.00..320.02 rows=1 width=69) (actual
time=1.736..1.736rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_06_timestamp_date_wbidx (cost=0.00..280.00
rows=1width=0) (actual time=1.734..1.734 rows=0 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_07 (cost=120.00..160.02 rows=1 width=69) (actual
time=20264.570..20264.570rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 12159978
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=186017
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_07_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=31.462..31.462 rows=1861120 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_08 (cost=120.00..160.02 rows=1 width=71) (actual
time=3384.968..3384.968rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 11646333
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=167506
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_08_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=11.634..11.634 rows=1675520 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_09 (cost=320.00..360.02 rows=1 width=70) (actual
time=5.109..5.109rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=65
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_09_timestamp_date_wbidx (cost=0.00..320.00
rows=1width=0) (actual time=0.927..0.927 rows=1280 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_10 (cost=280.00..320.02 rows=1 width=70) (actual
time=0.654..0.654rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_10_timestamp_date_wbidx (cost=0.00..280.00
rows=1width=0) (actual time=0.652..0.652 rows=0 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_11 (cost=360.00..400.02 rows=1 width=71) (actual
time=0.798..0.798rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_11_timestamp_date_wbidx (cost=0.00..360.00
rows=1width=0) (actual time=0.796..0.796 rows=0 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_12 (cost=320.00..360.02 rows=1 width=71) (actual
time=0.724..0.724rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_12_timestamp_date_wbidx (cost=0.00..320.00
rows=1width=0) (actual time=0.720..0.720 rows=0 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_13 (cost=120.00..160.02 rows=1 width=70) (actual
time=4999.558..4999.558rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16423765
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=252592
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_13_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=17.721..17.721 rows=2526720 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_14 (cost=120.00..160.02 rows=1 width=69) (actual
time=3910.470..3910.470rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 12836330
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=182873
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_14_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=12.805..12.805 rows=1829120 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_15 (cost=120.00..160.02 rows=1 width=70) (actual
time=32007.841..32007.841rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 12401675
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=177192
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_15_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=12.378..12.378 rows=1772800 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_16 (cost=120.00..160.02 rows=1 width=71) (actual
time=49685.420..49685.420rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 18157978
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=275572
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_16_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=47.678..47.678 rows=2755840 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_17 (cost=120.00..160.02 rows=1 width=71) (actual
time=51471.998..51471.998rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 18029623
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=269104
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_17_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=45.964..45.964 rows=2691840 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_18 (cost=320.00..360.02 rows=1 width=71) (actual
time=9.656..9.656rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Filter: ((profile)::text = 'f62467'::text)
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_18_timestamp_date_wbidx (cost=0.00..320.00
rows=1width=0) (actual time=9.636..9.636 rows=0 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_19 (cost=120.00..160.02 rows=1 width=72) (actual
time=52791.004..52791.004rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 18778591
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=273912
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_19_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=43.508..43.508 rows=2739200 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_20 (cost=120.00..160.02 rows=1 width=71) (actual
time=48498.382..48498.382rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 18372511
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=259665
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_20_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=43.928..43.928 rows=2597120 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_21 (cost=120.00..160.02 rows=1 width=71) (actual
time=29380.798..29380.798rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 11764289
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=163116
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_21_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=11.587..11.587 rows=1632000 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_22 (cost=120.00..160.02 rows=1 width=71) (actual
time=17235.755..17235.755rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 7239810
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=99379
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_22_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=15.286..15.286 rows=994560 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_23 (cost=120.00..160.02 rows=1 width=71) (actual
time=46032.953..46032.953rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 17575564
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=244012
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_23_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=55.424..55.424 rows=2440960 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_24 (cost=120.00..160.02 rows=1 width=71) (actual
time=42607.370..42607.370rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 17494825
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=241565
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_24_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=41.294..41.294 rows=2416640 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_25 (cost=120.00..160.02 rows=1 width=71) (actual
time=54908.860..54908.860rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 17077455
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=235512
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_25_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=40.047..40.047 rows=2355200 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_26 (cost=120.00..160.02 rows=1 width=71) (actual
time=50011.888..50011.888rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 17266774
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=237348
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_26_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=26.221..26.221 rows=2374400 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_27 (cost=120.00..160.02 rows=1 width=71) (actual
time=37861.181..37861.181rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15058588
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=206427
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_27_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=47.047..47.047 rows=2064640 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_28 (cost=120.00..160.02 rows=1 width=70) (actual
time=53615.517..53615.517rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15086191
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=205943
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_28_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=41.159..41.159 rows=2059520 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_29 (cost=120.00..160.02 rows=1 width=72) (actual
time=30450.511..30450.511rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 13381205
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=184505
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_29_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=19.002..19.002 rows=1845760 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_30 (cost=120.00..160.02 rows=1 width=71) (actual
time=25969.677..25969.677rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15271240
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=230288
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_30_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=20.996..20.996 rows=2304000 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_01_31 (cost=120.00..160.02 rows=1 width=71) (actual
time=39736.817..39736.817rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15911204
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=218360
-> Bitmap Index Scan on webtraffic_archive_day_2017_01_31_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=28.985..28.985 rows=2183680 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_01 (cost=120.00..160.02 rows=1 width=72) (actual
time=26867.440..26867.440rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16668578
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=228846
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_01_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=22.513..22.513 rows=2288640 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_02 (cost=120.00..160.02 rows=1 width=70) (actual
time=30785.636..30785.636rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15365933
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=211366
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_02_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=47.604..47.604 rows=2114560 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_03 (cost=120.00..160.02 rows=1 width=70) (actual
time=31734.405..31734.405rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15290777
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=208508
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_03_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=21.841..21.841 rows=2085120 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_04 (cost=120.00..160.02 rows=1 width=70) (actual
time=39953.807..39953.807rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 13623913
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=185670
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_04_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=33.536..33.536 rows=1857280 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_05 (cost=120.00..160.02 rows=1 width=75) (actual
time=25899.695..25899.695rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 12473497
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=177998
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_05_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=22.731..22.731 rows=1780480 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_06 (cost=120.00..160.02 rows=1 width=70) (actual
time=34831.594..34831.594rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15808780
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=216563
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_06_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=44.837..44.837 rows=2165760 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_07 (cost=120.00..160.02 rows=1 width=70) (actual
time=40175.151..40175.151rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 15586301
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=213778
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_07_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=46.492..46.492 rows=2138880 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_08 (cost=120.00..160.02 rows=1 width=71) (actual
time=41063.843..41063.843rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16970731
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=232156
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_08_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=61.649..61.649 rows=2321920 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_09 (cost=120.00..160.02 rows=1 width=70) (actual
time=37495.174..37495.174rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 17620957
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=240673
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_09_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=51.741..51.741 rows=2407680 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_10 (cost=120.00..160.02 rows=1 width=70) (actual
time=46934.095..46934.095rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16600684
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=226562
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_10_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=28.363..28.363 rows=2266880 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_11 (cost=120.00..160.02 rows=1 width=69) (actual
time=220541.990..220541.990rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 13815084
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=187513
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_11_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=44.574..44.574 rows=1875200 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_12 (cost=120.00..160.02 rows=1 width=70) (actual
time=3281.226..3281.226rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 12617059
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=172606
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_12_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=45.065..45.065 rows=1726720 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_13 (cost=200.00..240.02 rows=1 width=70) (actual
time=5837.657..5837.657rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 9479110
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=129496
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_13_timestamp_date_wbidx (cost=0.00..200.00
rows=1width=0) (actual time=86.073..86.073 rows=1295360 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_14 (cost=120.00..160.02 rows=1 width=70) (actual
time=5108.715..5108.715rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16548497
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=226646
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_14_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=52.060..52.060 rows=2266880 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_15 (cost=120.00..160.02 rows=1 width=70) (actual
time=5010.987..5010.987rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16199790
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=221125
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_15_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=39.582..39.582 rows=2211840 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_16 (cost=120.00..160.02 rows=1 width=70) (actual
time=7840.249..7840.249rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16510447
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=225509
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_16_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=29.277..29.277 rows=2255360 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_17 (cost=120.00..160.02 rows=1 width=70) (actual
time=7538.851..7538.851rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 16250418
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=220375
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_17_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=30.116..30.116 rows=2204160 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_18 (cost=120.00..160.02 rows=1 width=69) (actual
time=6893.772..6893.772rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 13014362
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=176514
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_18_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=61.474..61.474 rows=1766400 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_19 (cost=120.00..160.02 rows=1 width=70) (actual
time=4696.922..4696.922rows=0 loops=1)
Recheck Cond: (("timestamp")::date = '2017-02-20'::date)
Rows Removed by Index Recheck: 12014412
Filter: ((profile)::text = 'f62467'::text)
Heap Blocks: lossy=163941
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_19_timestamp_date_wbidx (cost=0.00..120.00
rows=1width=0) (actual time=56.764..56.764 rows=1639680 loops=1)
Index Cond: (("timestamp")::date = '2017-02-20'::date)
-> Bitmap Heap Scan on webtraffic_archive_day_2017_02_20 (cost=18532.26..134990.72 rows=115075 width=71)
(actualtime=1626.415..1626.468 rows=10 loops=1)
Recheck Cond: ((profile)::text = 'f62467'::text)
Filter: (("timestamp")::date = '2017-02-20'::date)
Heap Blocks: exact=4
-> Bitmap Index Scan on webtraffic_archive_day_2017_02_20_profile_wbidx (cost=0.00..18503.49
rows=115075width=0) (actual time=1600.196..1600.196 rows=105628 loops=1)
Index Cond: ((profile)::text = 'f62467'::text)
Planning time: 17.152 ms
Execution time: 1319389.125 ms
________________________________________
Da: Jaime Soler [jaime.soler@gmail.com]
Inviato: luned? 20 febbraio 2017 13.38
A: Job
Cc: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Slow queries on very big (and partitioned) table
Please share us an explain analyze of your query and \d+ of your table
2017-02-20 13:33 GMT+01:00 Job <Job@colliniconsulting.it<mailto:Job@colliniconsulting.it>>:
Hu guys,
we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a table.
Table is partitioned by day, with indexes on partitioned table.
Also a simple query (for example i want to search log occurred in a specific day), is immediate in tha partitioned
table(table_2017_02_15) but *extremely* slow in global table.
Where am i wrong?
Shall i create global index?
Thank you!
/F
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Greetings,
* Job (Job@colliniconsulting.it) wrote:
> here is primary a partitioned table (for 20/2/2017 logs):
> flashstart=# \d webtraffic_archive_day_2017_02_20;
> Table "public.webtraffic_archive_day_2017_02_20"
> Column | Type | Modifiers
>
-----------+-----------------------------+--------------------------------------------------------------------------------
> id | numeric(1000,1) | not null default
function_get_next_sequence('webtraffic_archive_id_seq'::text)
> timestamp | timestamp without time zone |
> domain | character varying(255) |
> action | integer |
> profile | character varying(50) |
> accessi | integer |
> url | text |
> Indexes:
> "webtraffic_archive_day_2017_02_20_action_wbidx" btree (action)
> "webtraffic_archive_day_2017_02_20_domain_wbidx" btree (domain)
> "webtraffic_archive_day_2017_02_20_profile_wbidx" btree (profile)
> "webtraffic_archive_day_2017_02_20_timestamp_date_wbidx" brin (("timestamp"::date))
> "webtraffic_archive_day_2017_02_20_timestamp_time_wbidx" btree (("timestamp"::time without time zone))
> "webtraffic_archive_day_2017_02_20_timestamp_wbidx" brin ("timestamp")
You don't have a CHECK constraint on your individual partitions, so PG
has no idea which of the partitions contains data from what time-period.
To have PostgreSQL's constraint exclusion work to eliminate entire
partitions, you need to add a CHECK constraint on the partition key. In
this case, it looks like you'd want something like:
CHECK (timestamp >= partition_date AND timestamp < partition_date+1)
Unfortunately, that'll require locking each table and scanning it to
make sure that the CHECK constraint isn't violated.
A couple of other notes- you probably don't need both a BRIN and a btree
index on the same column. If the BRIN works well enough for you then
you can drop the btree index. If it doesn't, then you might as well
just keep the btree index and drop the BRIN. It seems equally
unnecessary to have a BRIN on a cast of the column. You should also be
using timestamp w/ timezone, really.
> This is the query planner:
> explain analyze
> select * from webtraffic_archive where timestamp::date='2017-02-20' and profile='f62467'
If this is really what you're mostly doing, having constraint exclusion
and an index on 'profile' would probably be enough, if you insist on
continuing to have the table partitioned by day (which I continue to
argue is a bad idea- based on the number of total rows you mentioned and
the number of partitions, you have partitions with less than 20M rows
each and that's really small, month-based partitions with a BRIN would
probably work better). If you get to the point of having years worth of
daily partitions, you'd going to see increases in planning time.
Thanks!
Stephen
Attachment
> Unfortunately, that'll require locking each table and scanning it to make sure that the CHECK constraint isn't violated. Actually, CHECK constraints can be added with the NOT VALID clause. New tuples will be checked immediately, while the validation of existing tuples can be done later using ALTER TABLE ... VALIDATECONSTRAINT ... which takes a less invasive lock than if you'd omitted NOT VALID. Steve.
On 2/20/2017 5:22 AM, Stephen Frost wrote: > You probably shouldn't be partitioning by day for such a small dataset, > unless you've only got a few days worth of data that make up those 800m > records. agreed. we do like 6 months retention by weeks, so there's 26 or so partitions, that is reasonable. the primary reason for the partitions is to make it easy to drop old data by pruning a whole partition. -- john r pierce, recycling bits in santa cruz
Hi Stephen, Thank you for your excellent opinion! >If this is really what you're mostly doing, having constraint exclusion and an index on 'profile' would probably be enough,if you insist on continuing to have the table partitioned by day (which I continue to argue is a bad idea- >based on the number of total rows you mentioned and the number of partitions, you have partitions with less than 20M rowseach and that's really small, month-based partitions with a BRIN would probably work better). If you get to >the point of having years worth of daily partitions, you'd going to see increases in planning time. Based on our simulation, we can raise until 5Gb of datas for a single day, with some millions of rows. We thought to implement one partition for day. Do you think it should be fine? Thank you! /F
> We thought to implement one partition for day. That would be 365 partitions in a year. In our experience INSERTS suffers the most in a partitioned table because triggers are the only way to route the row to theproper child (inherited) table. Question: How is your insert pattern? Do you insert always current date. In that case you can write the trigger code to havecurrent date at the top so that the insert trigger finds the matching date as early as possible. Selects, updates and deletes are reasonably better.
Greetings, * Job (Job@colliniconsulting.it) wrote: > >If this is really what you're mostly doing, having constraint exclusion and an index on 'profile' would probably be enough,if you insist on continuing to have the table partitioned by day (which I continue to argue is a bad idea- > >based on the number of total rows you mentioned and the number of partitions, you have partitions with less than 20M rowseach and that's really small, month-based partitions with a BRIN would probably work better). If you get to > >the point of having years worth of daily partitions, you'd going to see increases in planning time. > > Based on our simulation, we can raise until 5Gb of datas for a single day, with some millions of rows. 5GB and a few millions rows isn't actually all that much. > We thought to implement one partition for day. > Do you think it should be fine? Really depends on what you're doing. If you're running very short queries that pull out just a record or a few records, then you're going to be unhappy with the planning time required when you have hundreds and thousands of partitions, which is why I typically recommend against using partitions-by-day unless you're only keeping a few months worth of data. Thanks! Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes:
> * Job (Job@colliniconsulting.it) wrote:
>> We thought to implement one partition for day.
>> Do you think it should be fine?
> Really depends on what you're doing. If you're running very short
> queries that pull out just a record or a few records, then you're going
> to be unhappy with the planning time required when you have hundreds and
> thousands of partitions, which is why I typically recommend against
> using partitions-by-day unless you're only keeping a few months worth of
> data.
Or to put it more simply: if you have more than O(100) partitions,
you're doing it wrong. There is a cost to subdividing things too finely.
The improved partitioning support that's going into v10 will probably
allow more partitions before it really starts to groan, but it'll still
not be a great idea to create more than the minimum number of partitions
you really need.
regards, tom lane