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, the problem 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.369 rows=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=1 width=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.123 rows=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=1 width=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.772 rows=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=1 width=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.736 rows=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=1 width=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.570 rows=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=1 width=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.968 rows=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=1 width=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.109 rows=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=1 width=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.654 rows=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=1 width=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.798 rows=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=1 width=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.724 rows=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=1 width=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.558 rows=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=1 width=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.470 rows=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=1 width=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.841 rows=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=1 width=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.420 rows=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=1 width=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.998 rows=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=1 width=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.656 rows=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=1 width=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.004 rows=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=1 width=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.382 rows=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=1 width=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.798 rows=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=1 width=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.755 rows=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=1 width=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.953 rows=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=1 width=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.370 rows=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=1 width=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.860 rows=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=1 width=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.888 rows=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=1 width=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.181 rows=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=1 width=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.517 rows=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=1 width=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.511 rows=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=1 width=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.677 rows=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=1 width=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.817 rows=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=1 width=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.440 rows=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=1 width=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.636 rows=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=1 width=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.405 rows=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=1 width=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.807 rows=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=1 width=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.695 rows=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=1 width=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.594 rows=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=1 width=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.151 rows=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=1 width=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.843 rows=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=1 width=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.174 rows=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=1 width=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.095 rows=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=1 width=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.990 rows=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=1 width=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.226 rows=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=1 width=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.657 rows=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=1 width=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.715 rows=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=1 width=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.987 rows=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=1 width=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.249 rows=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=1 width=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.851 rows=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=1 width=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.772 rows=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=1 width=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.922 rows=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=1 width=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) (actual time=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=115075 width=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@xxxxxxxxx]
Inviato: luned? 20 febbraio 2017 13.38
A: Job
Cc: pgsql-general@xxxxxxxxxxxxxx
Oggetto: Re: 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@xxxxxxxxxxxxxxxxxxxx<mailto:Job@xxxxxxxxxxxxxxxxxxxx>>:
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@xxxxxxxxxxxxxx<mailto:pgsql-general@xxxxxxxxxxxxxx>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
