6

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?

  • Each question is supposed to be useful to the general public. The Postgres version must always be included with questions like that. How did you change column coll exactly? Did you VACUUM ANALYZE all involved tables before running the query afterwards? – Erwin Brandstetter Aug 14 '15 at 8:55
  • Follow up to this previous question. – Erwin Brandstetter Aug 14 '15 at 9:02
  • @Erwin Thank you for feedback. I edit my question and add them. – umut Aug 14 '15 at 10:32
4

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:

  • I try these scenarios; I add 10000 row to each child table. After that I run "VACUUM FULL ANALYZE". Result is same, db uses seq scan. I try it postgres 9.3 into sqlfiddle; sqlfiddle.com/#!15/307c0/4 . When coll character value 32, db use seq scan. If coll character value is changed to 15, db uses index. – umut Aug 14 '15 at 10:49
  • @umut: Your fiddle does not test what you describe. You only insert a hand full of rows, without giving Postgres the chance to update statistics. (Even with updated statistics, Postgres is not going to use an index for just a hand full of rows.) I added a proper fiddle and a link to more. – Erwin Brandstetter Aug 14 '15 at 13:53
  • thank you for your help. These anwers are very useful for me. I think it solve my sql problem :). – umut Aug 14 '15 at 14:15
  • I try your sqlfiddle with more rows (1 M data for each child table). Postgresql uses seq scan in this situation. sqlfidle new link sqlfiddle.com/#!15/53918/1 . – umut Aug 15 '15 at 12:55
  • @umut; I see bitmap index scans in your fiddle. The only seq. scan is on the parent table test2, which makes sense because that one's empty. – Erwin Brandstetter Aug 16 '15 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.