Re: BUG #15475: Views over CITEXT columns return no data

Hi Andrew,

Here you go:

BROKEN VERSION:

$ psql -c "EXPLAIN ANALYSE SELECT COUNT(*)
FROM my_events
WHERE my_date >= '2018-08-01'
AND my_date < '2018-09-01'
AND type_id NOT IN ('A','LIST','OF','NOTS');" my_databasename
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=88550586.73..88550586.74 rows=1 width=8) (actual time=32891.602..32891.602 rows=1 loops=1)
-> Gather (cost=88550586.52..88550586.73 rows=2 width=8) (actual time=32884.164..32894.896 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=88549586.52..88549586.53 rows=1 width=8) (actual time=32871.097..32871.097 rows=1 loops=3)
-> Merge Left Join (cost=627157.66..78783047.74 rows=3906615513 width=0) (actual time=32842.042..32863.090 rows=56293 loops=3)
Merge Cond: (((my_events_raw_201807.my_citext)::integer) = my_lookup.my_int)
-> Sort (cost=581238.25..586738.88 rows=2200254 width=32) (actual time=32842.038..32848.497 rows=56293 loops=3)
Sort Key: ((my_events_raw_201807.my_citext)::integer)
Sort Method: quicksort Memory: 3265kB
Worker 0: Sort Method: quicksort Memory: 3284kB
Worker 1: Sort Method: quicksort Memory: 3338kB
-> Result (cost=0.43..244162.87 rows=2200254 width=32) (actual time=10.142..32772.369 rows=56293 loops=3)
-> Parallel Append (cost=0.43..244162.87 rows=2200254 width=32) (actual time=10.139..32727.527 rows=56293 loops=3)
-> Parallel Bitmap Heap Scan on my_events_raw_201807 (cost=2158.55..86099.06 rows=29931 width=32) (actual time=24.819..364.996 rows=5154 loops=1)
Recheck Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
Rows Removed by Filter: 73532
-> Bitmap Index Scan on my_events_raw_201807_my_date_idx (cost=0.00..2140.59 rows=75616 width=0) (actual time=21.943..21.943 rows=78686 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
-> Parallel Index Scan using my_events_raw_201601_my_date_idx on my_events_raw_201601 (cost=0.43..8.46 rows=1 width=32) (actual time=0.047..0.047 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201602_my_date_idx on my_events_raw_201602 (cost=0.43..8.46 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201603_my_date_idx on my_events_raw_201603 (cost=0.43..8.46 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201604_my_date_idx on my_events_raw_201604 (cost=0.43..8.46 rows=1 width=32) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201605_my_date_idx on my_events_raw_201605 (cost=0.43..8.46 rows=1 width=32) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201606_my_date_idx on my_events_raw_201606 (cost=0.43..8.46 rows=1 width=32) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201612_my_date_idx on my_events_raw_201612 (cost=0.43..8.46 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201701_my_date_idx on my_events_raw_201701 (cost=0.43..8.46 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201702_my_date_idx on my_events_raw_201702 (cost=0.43..8.46 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201703_my_date_idx on my_events_raw_201703 (cost=0.43..8.46 rows=1 width=32) (actual time=0.027..0.027 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201704_my_date_idx on my_events_raw_201704 (cost=0.43..8.46 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201705_my_date_idx on my_events_raw_201705 (cost=0.43..8.46 rows=1 width=32) (actual time=0.024..0.024 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201706_my_date_idx on my_events_raw_201706 (cost=0.43..8.46 rows=1 width=32) (actual time=0.027..0.027 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201707_my_date_idx on my_events_raw_201707 (cost=0.43..8.46 rows=1 width=32) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201708_my_date_idx on my_events_raw_201708 (cost=0.43..8.46 rows=1 width=32) (actual time=0.026..0.026 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201709_my_date_idx on my_events_raw_201709 (cost=0.43..8.46 rows=1 width=32) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201710_my_date_idx on my_events_raw_201710 (cost=0.43..8.46 rows=1 width=32) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201711_my_date_idx on my_events_raw_201711 (cost=0.43..8.46 rows=1 width=32) (actual time=0.191..0.191 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201712_my_date_idx on my_events_raw_201712 (cost=0.43..8.46 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201801_my_date_idx on my_events_raw_201801 (cost=0.43..8.46 rows=1 width=32) (actual time=0.067..0.067 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201802_my_date_idx on my_events_raw_201802 (cost=0.43..8.46 rows=1 width=32) (actual time=0.182..0.182 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201803_my_date_idx on my_events_raw_201803 (cost=0.43..8.46 rows=1 width=32) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201804_my_date_idx on my_events_raw_201804 (cost=0.43..8.46 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201805_my_date_idx on my_events_raw_201805 (cost=0.43..8.46 rows=1 width=32) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201806_my_date_idx on my_events_raw_201806 (cost=0.43..8.46 rows=1 width=32) (actual time=0.038..0.038 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201809_my_date_idx on my_events_raw_201809 (cost=0.43..8.46 rows=1 width=32) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201810_my_date_idx on my_events_raw_201810 (cost=0.43..8.46 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201607_my_date_idx on my_events_raw_201607 (cost=0.43..8.45 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201608_my_date_idx on my_events_raw_201608 (cost=0.43..8.45 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201609_my_date_idx on my_events_raw_201609 (cost=0.43..8.45 rows=1 width=32) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201610_my_date_idx on my_events_raw_201610 (cost=0.43..8.45 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201611_my_date_idx on my_events_raw_201611 (cost=0.43..8.45 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201811_my_date_idx on my_events_raw_201811 (cost=0.14..8.17 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201812_my_date_idx on my_events_raw_201812 (cost=0.14..8.17 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Seq Scan on my_events_raw_201808 (cost=0.00..146775.63 rows=2170289 width=32) (actual time=1.801..32576.815 rows=54575 loops=3)
Filter: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone) AND (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[])))
Rows Removed by Filter: 1806589
-> Materialize (cost=45919.42..47694.96 rows=355109 width=4) (never executed)
-> Sort (cost=45919.42..46807.19 rows=355109 width=4) (never executed)
Sort Key: my_lookup.my_int
-> Seq Scan on my_lookup (cost=0.00..8324.09 rows=355109 width=4) (never executed)
Planning Time: 130.655 ms
Execution Time: 32901.375 ms
(131 rows)

WORKING VERSION WITH CHANGE FROM CITEXT TO TEXT

$ psql -c "EXPLAIN ANALYSE SELECT COUNT(*)
FROM my_events
WHERE my_date >= '2018-08-01'
AND my_date < '2018-09-01'
AND type_id NOT IN ('A','LIST','OF','NOTS');" my_databasename
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=312923.12..312923.13 rows=1 width=8) (actual time=5459.973..5459.974 rows=1 loops=1)
-> Gather (cost=312922.91..312923.12 rows=2 width=8) (actual time=5459.093..5470.668 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=311922.91..311922.92 rows=1 width=8) (actual time=5440.634..5440.634 rows=1 loops=3)
-> Parallel Hash Left Join (cost=8680.58..306422.32 rows=2200235 width=0) (actual time=5440.619..5440.620 rows=0 loops=3)
Hash Cond: ((my_events_raw_201807.my_citext)::integer = my_lookup.my_int)
-> Parallel Append (cost=0.43..244162.87 rows=2200254 width=32) (actual time=10.760..5208.188 rows=56293 loops=3)
-> Parallel Bitmap Heap Scan on my_events_raw_201807 (cost=2158.55..86099.06 rows=29931 width=32) (actual time=28.020..151.682 rows=5154 loops=1)
Recheck Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
Rows Removed by Filter: 73532
-> Bitmap Index Scan on my_events_raw_201807_my_date_idx (cost=0.00..2140.59 rows=75616 width=0) (actual time=25.655..25.655 rows=78686 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
-> Parallel Index Scan using my_events_raw_201601_my_date_idx on my_events_raw_201601 (cost=0.43..8.46 rows=1 width=32) (actual time=0.150..0.150 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201602_my_date_idx on my_events_raw_201602 (cost=0.43..8.46 rows=1 width=32) (actual time=0.060..0.060 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201603_my_date_idx on my_events_raw_201603 (cost=0.43..8.46 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201604_my_date_idx on my_events_raw_201604 (cost=0.43..8.46 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201605_my_date_idx on my_events_raw_201605 (cost=0.43..8.46 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201606_my_date_idx on my_events_raw_201606 (cost=0.43..8.46 rows=1 width=32) (actual time=0.043..0.043 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201612_my_date_idx on my_events_raw_201612 (cost=0.43..8.46 rows=1 width=32) (actual time=0.038..0.038 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201701_my_date_idx on my_events_raw_201701 (cost=0.43..8.46 rows=1 width=32) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201702_my_date_idx on my_events_raw_201702 (cost=0.43..8.46 rows=1 width=32) (actual time=0.038..0.038 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201703_my_date_idx on my_events_raw_201703 (cost=0.43..8.46 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201704_my_date_idx on my_events_raw_201704 (cost=0.43..8.46 rows=1 width=32) (actual time=0.026..0.026 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201705_my_date_idx on my_events_raw_201705 (cost=0.43..8.46 rows=1 width=32) (actual time=0.026..0.026 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201706_my_date_idx on my_events_raw_201706 (cost=0.43..8.46 rows=1 width=32) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201707_my_date_idx on my_events_raw_201707 (cost=0.43..8.46 rows=1 width=32) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201708_my_date_idx on my_events_raw_201708 (cost=0.43..8.46 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201709_my_date_idx on my_events_raw_201709 (cost=0.43..8.46 rows=1 width=32) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201710_my_date_idx on my_events_raw_201710 (cost=0.43..8.46 rows=1 width=32) (actual time=0.034..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201711_my_date_idx on my_events_raw_201711 (cost=0.43..8.46 rows=1 width=32) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201712_my_date_idx on my_events_raw_201712 (cost=0.43..8.46 rows=1 width=32) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201801_my_date_idx on my_events_raw_201801 (cost=0.43..8.46 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201802_my_date_idx on my_events_raw_201802 (cost=0.43..8.46 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201803_my_date_idx on my_events_raw_201803 (cost=0.43..8.46 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201804_my_date_idx on my_events_raw_201804 (cost=0.43..8.46 rows=1 width=32) (actual time=0.055..0.055 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201805_my_date_idx on my_events_raw_201805 (cost=0.43..8.46 rows=1 width=32) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201806_my_date_idx on my_events_raw_201806 (cost=0.43..8.46 rows=1 width=32) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201809_my_date_idx on my_events_raw_201809 (cost=0.43..8.46 rows=1 width=32) (actual time=0.050..0.050 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201810_my_date_idx on my_events_raw_201810 (cost=0.43..8.46 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201607_my_date_idx on my_events_raw_201607 (cost=0.43..8.45 rows=1 width=32) (actual time=0.033..0.034 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201608_my_date_idx on my_events_raw_201608 (cost=0.43..8.45 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201609_my_date_idx on my_events_raw_201609 (cost=0.43..8.45 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201610_my_date_idx on my_events_raw_201610 (cost=0.43..8.45 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201611_my_date_idx on my_events_raw_201611 (cost=0.43..8.45 rows=1 width=32) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201811_my_date_idx on my_events_raw_201811 (cost=0.14..8.17 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Index Scan using my_events_raw_201812_my_date_idx on my_events_raw_201812 (cost=0.14..8.17 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[]))
-> Parallel Seq Scan on my_events_raw_201808 (cost=0.00..146775.63 rows=2170289 width=32) (actual time=1.135..5141.246 rows=54575 loops=3)
Filter: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone) AND (type_id <> ALL ('{A,LIST,OF,NOTS}'::text[])))
Rows Removed by Filter: 1806589
-> Parallel Hash (cost=6252.62..6252.62 rows=147962 width=4) (actual time=116.203..116.204 rows=118370 loops=3)
Buckets: 131072 Batches: 8 Memory Usage: 2816kB
-> Parallel Seq Scan on my_lookup (cost=0.00..6252.62 rows=147962 width=4) (actual time=0.506..53.040 rows=118370 loops=3)
Planning Time: 141.878 ms
Execution Time: 5471.541 ms
(124 rows)

WORKING SQL LOADED VERSION WITH CITEXT:

$ psql -c "EXPLAIN ANALYSE SELECT COUNT(*)
FROM my_events
WHERE my_date >= '2018-08-01'
AND my_date < '2018-09-01'
AND type_id NOT IN ('A','LIST','OF','NOTS');" my_databasename
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=88550586.73..88550586.74 rows=1 width=8) (actual time=30522.517..30522.517 rows=1 loops=1)
-> Gather (cost=88550586.52..88550586.73 rows=2 width=8) (actual time=30522.496..30523.629 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=88549586.52..88549586.53 rows=1 width=8) (actual time=30500.142..30500.142 rows=1 loops=3)
-> Merge Left Join (cost=627157.66..78783047.74 rows=3906615513 width=0) (actual time=30470.062..30491.867 rows=56293 loops=3)
Merge Cond: (((my_events_raw_201807.my_citext)::integer) = my_lookup.my_int)
-> Sort (cost=581238.25..586738.88 rows=2200254 width=32) (actual time=30470.058..30476.691 rows=56293 loops=3)
Sort Key: ((my_events_raw_201807.my_citext)::integer)
Sort Method: quicksort Memory: 3265kB
Worker 0: Sort Method: quicksort Memory: 3354kB
Worker 1: Sort Method: quicksort Memory: 3269kB
-> Result (cost=0.43..244162.87 rows=2200254 width=32) (actual time=8.912..30402.504 rows=56293 loops=3)
-> Parallel Append (cost=0.43..244162.87 rows=2200254 width=32) (actual time=8.910..30360.150 rows=56293 loops=3)
-> Parallel Bitmap Heap Scan on my_events_raw_201807 (cost=2158.55..86099.06 rows=29931 width=32) (actual time=25.072..387.294 rows=5154 loops=1)
Recheck Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
Rows Removed by Filter: 73532
-> Bitmap Index Scan on my_events_raw_201807_my_date_idx (cost=0.00..2140.59 rows=75616 width=0) (actual time=22.898..22.898 rows=78686 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
-> Parallel Index Scan using my_events_raw_201601_my_date_idx on my_events_raw_201601 (cost=0.43..8.46 rows=1 width=32) (actual time=0.061..0.061 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201602_my_date_idx on my_events_raw_201602 (cost=0.43..8.46 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201603_my_date_idx on my_events_raw_201603 (cost=0.43..8.46 rows=1 width=32) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201604_my_date_idx on my_events_raw_201604 (cost=0.43..8.46 rows=1 width=32) (actual time=0.037..0.038 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201605_my_date_idx on my_events_raw_201605 (cost=0.43..8.46 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201606_my_date_idx on my_events_raw_201606 (cost=0.43..8.46 rows=1 width=32) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201612_my_date_idx on my_events_raw_201612 (cost=0.43..8.46 rows=1 width=32) (actual time=0.038..0.038 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201701_my_date_idx on my_events_raw_201701 (cost=0.43..8.46 rows=1 width=32) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201702_my_date_idx on my_events_raw_201702 (cost=0.43..8.46 rows=1 width=32) (actual time=0.041..0.041 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201703_my_date_idx on my_events_raw_201703 (cost=0.43..8.46 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201704_my_date_idx on my_events_raw_201704 (cost=0.43..8.46 rows=1 width=32) (actual time=0.040..0.041 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201705_my_date_idx on my_events_raw_201705 (cost=0.43..8.46 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201706_my_date_idx on my_events_raw_201706 (cost=0.43..8.46 rows=1 width=32) (actual time=0.027..0.027 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201707_my_date_idx on my_events_raw_201707 (cost=0.43..8.46 rows=1 width=32) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201708_my_date_idx on my_events_raw_201708 (cost=0.43..8.46 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201709_my_date_idx on my_events_raw_201709 (cost=0.43..8.46 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201710_my_date_idx on my_events_raw_201710 (cost=0.43..8.46 rows=1 width=32) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201711_my_date_idx on my_events_raw_201711 (cost=0.43..8.46 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201712_my_date_idx on my_events_raw_201712 (cost=0.43..8.46 rows=1 width=32) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201801_my_date_idx on my_events_raw_201801 (cost=0.43..8.46 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201802_my_date_idx on my_events_raw_201802 (cost=0.43..8.46 rows=1 width=32) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201803_my_date_idx on my_events_raw_201803 (cost=0.43..8.46 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201804_my_date_idx on my_events_raw_201804 (cost=0.43..8.46 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201805_my_date_idx on my_events_raw_201805 (cost=0.43..8.46 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201806_my_date_idx on my_events_raw_201806 (cost=0.43..8.46 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201809_my_date_idx on my_events_raw_201809 (cost=0.43..8.46 rows=1 width=32) (actual time=0.046..0.046 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201810_my_date_idx on my_events_raw_201810 (cost=0.43..8.46 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201607_my_date_idx on my_events_raw_201607 (cost=0.43..8.45 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201608_my_date_idx on my_events_raw_201608 (cost=0.43..8.45 rows=1 width=32) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201609_my_date_idx on my_events_raw_201609 (cost=0.43..8.45 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201610_my_date_idx on my_events_raw_201610 (cost=0.43..8.45 rows=1 width=32) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201611_my_date_idx on my_events_raw_201611 (cost=0.43..8.45 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201811_my_date_idx on my_events_raw_201811 (cost=0.14..8.17 rows=1 width=32) (actual time=0.011..0.012 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Index Scan using my_events_raw_201812_my_date_idx on my_events_raw_201812 (cost=0.14..8.17 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone))
Filter: (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[]))
-> Parallel Seq Scan on my_events_raw_201808 (cost=0.00..146775.63 rows=2170289 width=32) (actual time=0.759..30203.782 rows=54575 loops=3)
Filter: ((my_date >= '2018-08-01 00:00:00+10'::timestamp with time zone) AND (my_date < '2018-09-01 00:00:00+10'::timestamp with time zone) AND (type_id <> ALL ('{A,LIST,OF,NOTS}'::citext[])))
Rows Removed by Filter: 1806589
-> Materialize (cost=45919.42..47694.96 rows=355109 width=4) (never executed)
-> Sort (cost=45919.42..46807.19 rows=355109 width=4) (never executed)
Sort Key: my_lookup.my_int
-> Seq Scan on my_lookup (cost=0.00..8324.09 rows=355109 width=4) (never executed)
Planning Time: 203.442 ms
Execution Time: 30529.264 ms
(131 rows)

CHEERS
Paul

> On 2 Nov 2018, at 10:32, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>
>>>>>> "Paul" == Paul Schaap <ps(at)ipggroup(dot)com> writes:
>
> Paul> Hi Andrew,
>
> Paul> I agree it seems implausible to me too however it is 100%
> Paul> reproducible, see below track of it happening, I have changed the
> Paul> names of things to protect the innocent ;-) :
>
> Can you show the result of adding EXPLAIN ANALYZE in front of your
> select queries, both in the failing and successful cases?
>
> i.e.
>
> $ psql -c "EXPLAIN ANALYZE SELECT COUNT(*)
> FROM my_events
> WHERE my_date >= '2018-08-01'
> AND my_date < '2018-09-01'
> AND type_id NOT IN ('A','LIST','OF','NOTS');" my_databasename
>
> (and likewise for the other queries you showed)
>
> --
> Andrew (irc:RhodiumToad)

--

PRIVACY & CONFIDENTIALITY NOTICE

The information contained in this
email is intended for the named recipients  only.  It may contain
privileged and confidential information and if you are  not the intended
recipient, you should not copy it or disclose its contents  to any other
person, or take any action in reliance on it.  If you have  received this
email in error, please notify us immediately by return email.