CODEWITHSUNDEEP
postgresqlindexingpartitioningpostgresql-9.3postgresql-performance
hudarsono
hudarsono

Reputation: 389

Postgres not using index for range query in partitioned table

I found that Postgres is not using an index for a range query on a partitioned table.

The parent table and its partitions have their date column indexed using btree.

A query like this:

select * from parent_table where date >= '2015-07-01';

does not use indexes.

EXPLAIN result: 

Append  (cost=0.00..106557.52 rows=3263963 width=128)
->  Seq Scan on parent_table  (cost=0.00..0.00 rows=1 width=640)
    Filter: (date >= '2015-07-01'::date)
->  Seq Scan on z_partition_2015_07  (cost=0.00..106546.02 rows=3263922 width=128)
    Filter: (date >= '2015-07-01'::date)
->  Seq Scan on z_partition_2015_08  (cost=0.00..11.50 rows=40 width=640)
    Filter: (date >= '2015-07-01'::date)

But a query like this:

select * from parent_table where date = '2015-07-01'

uses an index.

EXPLAIN result:

    Append  (cost=0.00..30400.95 rows=107602 width=128)
->  Seq Scan on parent_table  (cost=0.00..0.00 rows=1 width=640)
    Filter: (date = '2015-07-01'::date)
->  Index Scan using z_partition_2015_07_date on z_partition_2015_07  (cost=0.43..30400.95 rows=107601 width=128)
    Index Cond: (date = '2015-07-01'::date)

When I run the query on a different normal table with date indexed, both queries use the index.

Anything particular that we should do on partitioned table index?

Upvotes: 1

Views: 3061

Answers (2)

murison
murison

Reputation: 3983

maybe that's just faster that way? run your query, then do this:

SET enable_seqscan=false

And run it again.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657022

I assume you are aware that "partitions" are separate tables in Postgres. Indexes are typically not used when retrieving large parts of a table (more than ~ 5 %, it depends on many details), because it's typically faster to just scan the table sequentially in such cases.

What's more, it seems like you select all rows from the involved partitions in your first query. No use for indexes ...

Generally, an equality predicate with = is more selective than a predicate with >=.Think about it:

Your first query with date >= '2015-07-01' retrieves all rows from the partition (guessing, I would need to see the exact definition). Using the index would just add overhead cost. But your second query with date = '2015-07-01' only fetches a small percentage. Postgres expects an index scan to be faster.

Upvotes: 3

Related Questions