Why are all partitioned tables scanned when they should be excluded by query criteria from a join?

Question

I have two tables: One is a partitioned table based on a valid time, and the second is a state table that has a valid time and an expiration time. I also have a view that joins the state table to join to the partitioned table and attempts to narrow down the search of the partitioned table to only those partitions that have not expired.

I have tried using separate validtime columns so the one in the where clause is not tied to the one in the partition, but the query planner shows index scans on the geometry only in those cases, and still does index scans on partitions that cannot contain valid results based on the expiration.

Partitioned tables look like this:

CREATE TABLE public.poly__20190609__171000
(
-- Inherited from table poly:  ogc_featureid bigint NOT NULL DEFAULT nextval('poly_ogc_featureid_seq'::regclass),
-- Inherited from table poly:  validtime timestamp without time zone,
-- Inherited from table poly:  geometry_4326 geometry(Polygon,4326) NOT NULL,
  CONSTRAINT poly_20190609_171000_pkey PRIMARY KEY (ogc_featureid),
  CONSTRAINT poly_20190609_171000_check CHECK (validtime >= '2019-06-09 17:10:00'::timestamp without time zone AND validtime <= '2019-06-09 17:14:59'::timestamp without time zone)
);
CREATE INDEX poly__20190609__171000_4326_gist
  ON public.poly__20190609__171000
  USING gist
  (geometry_4326);
CREATE INDEX poly__20190609__171000_validtime_idx
  ON public.poly__20190609__171000
  USING btree
  (validtime);

State table looks like this:

CREATE TABLE public.poly_current_state
(
  validtime timestamp without time zone NOT NULL,
  current_expiration timestamp without time zone NOT NULL,
  CONSTRAINT poly_current_state_pkey PRIMARY KEY (validtime)
);

View looks like this:

CREATE VIEW public.example_view AS
 SELECT p.ogc_featureid,
    pcs.validtime,
    pcs.current_expiration AS expiration,
    p.geometry_4326
   FROM poly_current_state pcs
   INNER JOIN poly p ON p.validtime = pcs.validtime;

Example query looks like this:

SELECT "expiration","validtime" 
  FROM "public"."example_view" 
 WHERE (
   "validtime" < '2019-06-09T18:30:00Z' 
   AND "expiration" > '2019-06-09T18:00:00Z' 
   AND ST_DWithin("geometry_4326",ST_GeomFromText('MULTILINESTRING ((-130 35, -65 35))', 4326),3.3333333333333335) 
   ) LIMIT 1;

Explain of the query (binary geometries truncated to protect the innocent) looks like this (Note the inclusion of the first two rows that are valid long before the expiration, which is at most 15 minutes after validtime):

Limit  (cost=0.05..11.25 rows=1 width=16)
  ->  Nested Loop  (cost=0.05..274278.59 rows=24506 width=16)
        ->  Append  (cost=0.00..268347.87 rows=92105 width=11)
              ->  Seq Scan on poly p  (cost=0.00..0.00 rows=1 width=20)
                    Filter: ((validtime < '2019-06-09 18:30:00'::timestamp without time zone) AND (geometry_4326 && '010300002...'::geometry) AND ('01050000...'::geometry && st_expand(geometry_4326, 3.33333333333333::double precision)) AND
              ->  Bitmap Heap Scan on poly__20190609__151500 p_1  (cost=845.99..6160.40 rows=2037 width=11)
                    Recheck Cond: (geometry_4326 && '010300002...'::geometry)
                    Filter: ((validtime < '2019-06-09 18:30:00'::timestamp without time zone) AND ('01050000...'::geometry && st_expand(geometry_4326, 3.33333333333333::double precision)) AND _st_dwithin(geometry_4326, '01050000...'::geomet
                    ->  Bitmap Index Scan on poly__20190609__151500_4326_gist  (cost=0.00..845.89 rows=30553 width=0)
                          Index Cond: (geometry_4326 && '010300002...'::geometry)
              ->  Bitmap Heap Scan on poly__20190609__152000 p_2  (cost=870.03..6217.87 rows=2039 width=11)
                    Recheck Cond: (geometry_4326 && '010300002...'::geometry)
                    Filter: ((validtime < '2019-06-09 18:30:00'::timestamp without time zone) AND ('01050000...'::geometry && st_expand(geometry_4326, 3.33333333333333::double precision)) AND _st_dwithin(geometry_4326, '01050000...'::geomet
                    ->  Bitmap Index Scan on poly__20190609__152000_4326_gist  (cost=0.00..869.92 rows=30579 width=0)
                          Index Cond: (geometry_4326 && '010300002...'::geometry)
              ...
              ->  Bitmap Heap Scan on poly__20190609__182500 p_39  (cost=1159.25..7559.40 rows=2803 width=11)
                    Recheck Cond: (geometry_4326 && '010300002...'::geometry)
                    Filter: ((validtime < '2019-06-09 18:30:00'::timestamp without time zone) AND ('01050000...'::geometry && st_expand(geometry_4326, 3.33333333333333::double precision)) AND _st_dwithin(geometry_4326, '01050000...'::geomet
                    ->  Bitmap Index Scan on poly__20190609__182500_4326_gist  (cost=0.00..1159.11 rows=42039 width=0)
                          Index Cond: (geometry_4326 && '010300002...'::geometry)
        ->  Index Only Scan using poly_current_state_a_idx on poly_current_state pcs  (cost=0.05..0.06 rows=1 width=19)
              Index Cond: ((validtime = p.validtime) AND (current_expiration > '2019-06-09 18:00:00'::timestamp without time zone))

I am using Postgres 9.3 (I know, but upgrading is not an immediate option).

Update: Here is an sql script that recreates the issue on PG 11. Should final two explains result in basically the same set of tables being scanned (other than the join)?

CREATE TABLE foo
(
    foo_id integer NOT NULL,
    foo_name VARCHAR(10),
    CONSTRAINT foo_pkey PRIMARY KEY (foo_id) 
);      

INSERT INTO foo (foo_id, foo_name) SELECT 1, 'eeny';
INSERT INTO foo (foo_id, foo_name) SELECT 2, 'meeny';
INSERT INTO foo (foo_id, foo_name) SELECT 3, 'miny';
INSERT INTO foo (foo_id, foo_name) SELECT 4, 'moe';
INSERT INTO foo (foo_id, foo_name) SELECT 5, 'tiger'; 
INSERT INTO foo (foo_id, foo_name) SELECT 6, 'toe';

CREATE TABLE foo_bar_baz
(
    foo_id integer NOT NULL,
    bar_id integer NOT NULL,
    baz    integer NOT NULL,
    CONSTRAINT foo_bar_baz_pkey PRIMARY KEY (foo_id, bar_id, baz),
    CONSTRAINT foo_bar_baz_fkey1 FOREIGN KEY (foo_id)
        REFERENCES foo (foo_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
) PARTITION BY RANGE (foo_id) 
;

CREATE TABLE IF NOT EXISTS foo_bar_baz_0 PARTITION OF foo_bar_baz FOR VALUES FROM (0) TO (1);
CREATE TABLE IF NOT EXISTS foo_bar_baz_1 PARTITION OF foo_bar_baz FOR VALUES FROM (1) TO (2);
CREATE TABLE IF NOT EXISTS foo_bar_baz_2 PARTITION OF foo_bar_baz FOR VALUES FROM (2) TO (3);
CREATE TABLE IF NOT EXISTS foo_bar_baz_3 PARTITION OF foo_bar_baz FOR VALUES FROM (3) TO (4);
CREATE TABLE IF NOT EXISTS foo_bar_baz_4 PARTITION OF foo_bar_baz FOR VALUES FROM (4) TO (5);
CREATE TABLE IF NOT EXISTS foo_bar_baz_5 PARTITION OF foo_bar_baz FOR VALUES FROM (5) TO (6);

WITH foos_and_bars AS
(
    SELECT ((random() * 4) + 1)::int as foo_id, generate_series(0, 1499)::int as bar_id                                                                                                                                                      
),
bazzes AS   
(
    SELECT generate_series(1, 1500)::int as baz
)
INSERT INTO foo_bar_baz (foo_id, bar_id, baz) SELECT foo_id, bar_id, baz FROM bazzes as bz JOIN foos_and_bars as fab ON mod(bz.baz, fab.foo_id) = 0

explain select count(*) from foo_bar_baz where foo_id = 1

explain select count(*) from foo_bar_baz as fbb join foo on fbb.foo_id = foo.foo_id where foo.foo_name = 'eeny'

Which Postgres version are you using? As you are using inheritance based partitioning, I guess some old version like 9.6 or 9.5 - if you really need partitioning, then the best option you have is to upgrade to Postgres 11 and use declarative partitioning. — , Jun 10 '19 at 00:50
I have tried a similar query against partitioned tables (using declarative partitioning) in postgres 11.x, and it still does a table scan on all partitions. According to [this stackoverflow answer](https://stackoverflow.com/questions/21964412/partitioned-table-query-still-scanning-all-partitions/21964683#21964683), it looks like constraint exclusion happens during planning, thus before the join. — MasterOfEntropy, Jul 26 '19 at 19:59
Postgres 11 does the right thing. However runtime pruning of partitions will not show up in a simple `explain`. If you use `explain (analyze, verbose)` you can see that the partitions show up in the plan, but with "never executed": https://explain.depesz.com/s/FmZx — , Aug 01 '19 at 18:36
I don't see any "never executed" when I do an explain (analyze, verbose): https://explain.depesz.com/s/W8Cy — MasterOfEntropy, Aug 01 '19 at 19:23

Why are all partitioned tables scanned when they should be excluded by query criteria from a join?

0 Answers0