Index is not used with table inheritance

Question

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables:

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    coll character varying(15),
    ts timestamp without time zone
);
CREATE INDEX ON test2(ts);

CREATE TABLE test2_20150812 (
    CHECK ( ts >= timestamp '2015-08-12' AND ts < timestamp '2015-08-13' )
) INHERITS (test2);

CREATE TABLE test2_20150811 (
    CHECK ( ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12' )
) INHERITS (test2);

CREATE INDEX ON test2_20150812(ts);
CREATE INDEX ON test2_20150811(ts);
VACUUM FULL ANALYZE;

My select queries' explain result (There is not any rows in db):

EXPLAIN (ANALYZE, BUFFERS) select * from test2 WHERE ts >= '2015-08-11' ORDER BY ts DESC;

 Sort  (cost=89.87..92.09 rows=887 width=31) (actual time=0.245..0.245 rows=0 loops=1)
   Sort Key: public.test2.ts
   Sort Method:  quicksort  Memory: 17kB
   Buffers: shared read=2
   ->  Result  (cost=0.00..46.44 rows=887 width=31) (actual time=0.087..0.087 rows=0 loops=1)
         Buffers: shared read=2
         ->  Append  (cost=0.00..46.44 rows=887 width=31) (actual time=0.078..0.078 rows=0 loops=1)
               Buffers: shared read=2
               ->  Seq Scan on test2  (cost=0.00..0.00 rows=1 width=31) (actual time=0.007..0.007 rows=0 loops=1)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
               ->  Bitmap Heap Scan on test2_20150812 test2  (cost=7.68..23.22 rows=443 width=31) (actual time=0.024..0.024 rows=
0 loops=1)
                     Recheck Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                     Buffers: shared read=1
                     ->  Bitmap Index Scan on test2_20150812_ts_idx  (cost=0.00..7.57 rows=443 width=0) (actual time=0.016..0.016
 rows=0 loops=1)
                           Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                           Buffers: shared read=1
               ->  Bitmap Heap Scan on test2_20150811 test2  (cost=7.68..23.22 rows=443 width=31) (actual time=0.033..0.033 rows=
0 loops=1)
                     Recheck Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                     Buffers: shared read=1
                     ->  Bitmap Index Scan on test2_20150811_ts_idx  (cost=0.00..7.57 rows=443 width=0) (actual time=0.026..0.026
 rows=0 loops=1)
                           Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
                           Buffers: shared read=1
 Total runtime: 0.320 ms
(23 rows)

However if I change column coll from character varying(15) to character varying(255), and do these steps again;

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    coll character varying(255),
    ts timestamp without time zone
);

Explain output is (There is not any rows in db):

EXPLAIN (ANALYZE, BUFFERS) select * from test2 WHERE ts >= '2015-08-11' ORDER BY ts DESC;

 Sort  (cost=42.47..43.18 rows=287 width=157) (actual time=0.028..0.028 rows=0 loops=1)
   Sort Key: public.test2.ts
   Sort Method:  quicksort  Memory: 17kB
   ->  Result  (cost=0.00..30.75 rows=287 width=157) (actual time=0.020..0.020 rows=0 loops=1)
         ->  Append  (cost=0.00..30.75 rows=287 width=157) (actual time=0.015..0.015 rows=0 loops=1)
               ->  Seq Scan on test2  (cost=0.00..0.00 rows=1 width=157) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
               ->  Seq Scan on test2_20150812 test2  (cost=0.00..15.38 rows=143 width=157) (actual time=0.002..0.002 rows=0 loops
=1)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
               ->  Seq Scan on test2_20150811 test2  (cost=0.00..15.38 rows=143 width=157) (actual time=0.002..0.002 rows=0 loops
=1)
                     Filter: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)
 Total runtime: 0.063 ms
(12 rows)

Is there any way for using indexes on the child tables in this new condition?

score 5 · Accepted Answer · edited Jun 15 '20 at 09:05

All of this is unrelated to inheritance and partitioning. It's about indexing and query plans in general.

The row size is much bigger for your second try: width=157 vs. width=46. Postgres will even more readily use an index for wider rows. Possible reasons for the unexpected sequential scan include:

You have substantially fewer rows in your tables for the second test as indicated by planner estimates: rows=143 vs. rows=357. It does not pay to look up an index for only few rows to sort.
Or statistics are outdated leading to misguided planner estimates (Postgres only thinks there would be fewer rows).
The index size may have been bloated as a side-effect of rewriting the tables. REINDEX or VACUUM FULL would repair that.

Run ANALYZE on all involved tables and try again - with the same number of rows in all tables. You should see bitmap index scans again. If the phenomenon persists, provide the output of EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN.

After question update

As long as you read the whole table, an index is of limited use. If you query a single table with a matching index, so that readily sorted rows can be read from the index and Postgres can skip the sort step altogether, you'll see an index scan.

That's not possible when multiple tables have to be combined. This SQL fiddle with 10k rows per child and valid statistics shows bitmap index scans as expected. After repeating the query a couple of times (as soon as the whole table is cached), Postgres may skip the index and switch to sequential scans, which have become cheaper now.

Postgres is obviously not smart enough to understand the mutually excluding check constraints, which would allow to append readily sorted results from each table as is. You could force that by manually instructing it:

(SELECT * FROM test2_20150812 ORDER BY ts DESC)
UNION ALL
(SELECT * FROM test2_20150811 ORDER BY ts DESC);

However, Postgres should be smart enough to use Merge Append (cheap method to combine pre-sorted sets). In my local tests on PostgreSQL 9.4 I actually see index scans on each partition, combined with Merge Append. That plan is better, but it's not that much faster than sequential scans because, remember!, as long as you read the whole table, an index is of limited use.

'QUERY PLAN'
'Merge Append  (cost=0.73..16866.41 rows=200001 width=45)'
'  Sort Key: test.ts'
'  ->  Index Scan Backward using test_ts_idx on test  (cost=0.13..8.14 rows=1 width=528)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'
'  ->  Index Scan Backward using test_20150811_ts_idx on test_20150811  (cost=0.29..6594.01 rows=100000 width=45)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'
'  ->  Index Scan Backward using test_20150812_ts_idx on test_20150812  (cost=0.29..6594.29 rows=100000 width=45)'
'        Index Cond: (ts >= '2015-08-11 00:00:00'::timestamp without time zone)'

I don't get the same plan with Postgres 9.3 (testing on sqlfiddle). Must be a limitation of pg 9.3. (?)

But since you are using the outdated version 9.0, none of that is available to you.
Merge Append was introduced with 9.1.

You get more interesting results when limiting the result to few rows. varchar(15) or varchar(255) has very little impact on the query plan. The wider type favors indexes some more.

Your added fiddle with some more test queries.

About testing indexes on SQL Fiddle:

PostgreSQL partial index unused when created on a table with existing data

Index is not used with table inheritance

1 Answers1

After question update

Linked