Not optimal query plans when querying partitioned table with enable_parent set to true

[Envek]

Query:

SELECT "tracker_points".* FROM "tracker_points" WHERE "tracker_points"."ambulance_status_id" = 1147 AND "tracker_points"."evented_at" BETWEEN '2015-11-30 08:40:37.04006' and '2016-02-15 08:40:37.04006' ORDER BY "tracker_points"."evented_at" ASC;

Query plan (EXPLAIN):

 Sort  (cost=822974.40..822978.02 rows=1450 width=414)
   Sort Key: tracker_points.evented_at
   ->  Append  (cost=0.57..822898.26 rows=1450 width=414)
         ->  Index Scan using tracker_points_by_status_index on tracker_points  (cost=0.57..3944.45 rows=1051 width=414)
               Index Cond: ((ambulance_status_id = 1147) AND (evented_at >= '2015-11-30 08:40:37.04006'::timestamp without time zone) AND (evented_at <= '2016-02-15 08:40:37.04006'::timestamp without time zone))
         ->  Seq Scan on tracker_points_2015_11  (cost=0.00..20.65 rows=1 width=414)
               Filter: ((evented_at >= '2015-11-30 08:40:37.04006'::timestamp without time zone) AND (ambulance_status_id = 1147))
         ->  Seq Scan on tracker_points_2015_12  (cost=0.00..325808.35 rows=158 width=414)
               Filter: (ambulance_status_id = 1147)
         ->  Seq Scan on tracker_points_2016_01  (cost=0.00..493104.16 rows=239 width=414)
               Filter: (ambulance_status_id = 1147)
         ->  Seq Scan on tracker_points_2016_02  (cost=0.00..20.65 rows=1 width=414)
               Filter: ((evented_at <= '2016-02-15 08:40:37.04006'::timestamp without time zone) AND (ambulance_status_id = 1147))

Query plan for query only by single partition:

# EXPLAIN SELECT "tracker_points_2016_01".* FROM "tracker_points_2016_01" WHERE "tracker_points_2016_01"."ambulance_status_id" = 1147 AND "tracker_points_2016_01"."evented_at" BETWEEN '2015-11-30 08:40:37.04006' and '2016-02-15 08:40:37.04006' ORDER BY "tracker_points_2016_01"."evented_at" ASC;
                                                                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tracker_points_2016_01_ambulance_status_id_evented_at_idx on tracker_points_2016_01  (cost=0.56..1685.90 rows=839 width=414)
   Index Cond: ((ambulance_status_id = 1147) AND (evented_at >= '2015-11-30 08:40:37.04006'::timestamp without time zone) AND (evented_at <= '2016-02-15 08:40:37.04006'::timestamp without time zone))

But if set enable_parent to false, then indexes are being used (expected result):

# SELECT set_enable_parent('tracker_points', false);

                                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.43..4137.51 rows=2005 width=414)
   ->  Index Scan using tracker_points_2015_11_ambulance_status_id_evented_at_idx on tracker_points_2015_11  (cost=0.43..66.93 rows=30 width=414)
         Index Cond: ((ambulance_status_id = 1147) AND (evented_at >= '2015-11-30 08:40:37.04006'::timestamp without time zone))
   ->  Index Scan using tracker_points_2015_12_ambulance_status_id_evented_at_idx on tracker_points_2015_12  (cost=0.56..2237.79 rows=1101 width=414)
         Index Cond: (ambulance_status_id = 1147)
   ->  Index Scan using tracker_points_2016_01_ambulance_status_id_evented_at_idx on tracker_points_2016_01  (cost=0.56..1824.61 rows=873 width=414)
         Index Cond: (ambulance_status_id = 1147)
   ->  Index Scan using tracker_points_2016_02_ambulance_status_id_evented_at_idx on tracker_points_2016_02  (cost=0.15..8.17 rows=1 width=414)
         Index Cond: ((ambulance_status_id = 1147) AND (evented_at <= '2016-02-15 08:40:37.04006'::timestamp without time zone))

Expected result

Postgres will use tracker_points_2016_01_ambulance_status_id_evented_at_idx index when collecting data from partition (and similarly for other partitions) when enable_parent is set to true.

Actual result

Sequential scan is performed, as a result all quickly performed queries before partitioning became extremely slow and whole program's performance was effectively ruined :-)
And while data is being moved concurrently we can't disable parent table scanning.

Technical details

1. PostgreSQL 9.5.4 (from PGDG) on Ubuntu 14.04 with pg_pathman 1.0 and 1.1.
2. PostgreSQL 9.6.0 (built from source with Homebrew) on OS X with pg_pathman 1.1.

pg_pathman was installed like this:

git clone https://github.com/postgrespro/pg_pathman.git /var/tmp/pg_pathman
cd /var/tmp/pg_pathman
git checkout 1.1
make USE_PGXS=1
make install USE_PGXS=1
echo "shared_preload_libraries = pg_pathman" | sudo tee -a /etc/postgresql/9.5/main/postgresql.conf
sudo service postgresql restart
psql db -c 'CREATE EXTENSION pg_pathman'

Table was partitioned with script like this:

SELECT create_range_partitions('tracker_points', 'evented_at', '2015-11-01'::timestamp, '1 month'::interval, 0, false);
SELECT add_range_partition('tracker_points', '2015-11-01'::timestamp, '2015-11-01'::timestamp + '1 month'::interval, 'tracker_points_2015_11');
SELECT append_range_partition('tracker_points', 'tracker_points_2015_12');
SELECT append_range_partition('tracker_points', 'tracker_points_2016_01');
SELECT append_range_partition('tracker_points', 'tracker_points_2016_02');
SELECT partition_table_concurrently('tracker_points');

Output of psql's \d+ (fragment):

public | tracker_points                     | таблица            | smp      | 55 GB      |
public | tracker_points_2015_11             | таблица            | smp      | 8192 bytes |
public | tracker_points_2015_12             | таблица            | smp      | 2078 MB    |
public | tracker_points_2016_01             | таблица            | smp      | 3146 MB    |
public | tracker_points_2016_02             | таблица            | smp      | 8192 bytes |

Output of psql's \d+ tracker_points:

                                              Table "public.tracker_points"
       Column        |            Type             |              Modifiers              | Storage  | Stats target | Description
---------------------+-----------------------------+-------------------------------------+----------+--------------+-------------
 id                  | uuid                        | not null default uuid_generate_v4() | plain    |              |
 tracker_id          | uuid                        | not null                            | plain    |              |
 data                | jsonb                       | not null default '{}'::jsonb        | extended |              |
 evented_at          | timestamp without time zone | not null                            | plain    |              |
 created_at          | timestamp without time zone |                                     | plain    |              |
 ambulance_status_id | integer                     |                                     | plain    |              |
 in_zone             | boolean                     | not null default true               | plain    |              |
 is_stop             | boolean                     | default false                       | plain    |              |
Indexes:
    "tracker_points_pkey" PRIMARY KEY, btree (id)
    "index_tracker_points_on_evented_at" brin (evented_at)
    "index_tracker_points_on_tracker_id" btree (tracker_id)
    "main_tracker_points_search_index" btree (tracker_id, evented_at DESC)
    "tracker_points_by_status_index" btree (ambulance_status_id, evented_at)
Child tables: tracker_points_2015_06,
    tracker_points_2015_11,
    tracker_points_2015_12,
    tracker_points_2016_01,
    tracker_points_2016_02,

Output of psql's \d+ tracker_points_2016_01:

                                              Table "public.tracker_points_2016_01"
       Column        |            Type             |              Modifiers              | Storage  | Stats target | Description
---------------------+-----------------------------+-------------------------------------+----------+--------------+-------------
 id                  | uuid                        | not null default uuid_generate_v4() | plain    |              |
 tracker_id          | uuid                        | not null                            | plain    |              |
 data                | jsonb                       | not null default '{}'::jsonb        | extended |              |
 evented_at          | timestamp without time zone | not null                            | plain    |              |
 created_at          | timestamp without time zone |                                     | plain    |              |
 ambulance_status_id | integer                     |                                     | plain    |              |
 in_zone             | boolean                     | not null default true               | plain    |              |
 is_stop             | boolean                     | default false                       | plain    |              |
Indexes:
    "tracker_points_2016_01_pkey" PRIMARY KEY, btree (id)
    "tracker_points_2016_01_ambulance_status_id_evented_at_idx" btree (ambulance_status_id, evented_at)
    "tracker_points_2016_01_evented_at_idx" brin (evented_at)
    "tracker_points_2016_01_tracker_id_evented_at_idx" btree (tracker_id, evented_at DESC)
    "tracker_points_2016_01_tracker_id_idx" btree (tracker_id)
Check constraints:
    "pathman_tracker_points_2016_01_4_check" CHECK (evented_at >= '2016-01-01 00:00:00'::timestamp without time zone AND evented_at < '2016-02-01 00:00:00'::timestamp without time zone)
Inherits: tracker_points

Also I've tried to do VACUUM ANALYZE on table tracker_points_2016_01 with no effect.

Question

1. Can it be fixed on pg_pathman's side?
2. Any workarounds?

Thank you for pg_pathman!

[funbringer]

Hi @Envek,

We're investigating the issue, so there's a good chance that minor release 1.1.2 will contain the bugfix.

[funbringer]

Hi again, I pushed a branch called master_improved_planning. Could you test it? It's totally compatible with 1.1 since it's going to be a minor release, so you won't have to drop extension etc.

[Envek]

Yes, plans now are good either with or without parent table: index scan on all involved tables. 🎉

Thank you! Waiting for 1.1.2 release then :-)