R: [GENERAL] Slow queries on very big (and partitioned) table - Mailing list pgsql-general

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