Partial index does not make query faster

From: Ruben Blanco <rubenblan(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Partial index does not make query faster
Date: 2012-01-18 12:57:20
Message-ID: CAP4Xq2G_gvhh1D+E7yGKPSJuNT3yfniisdMu+8Qp+jFG3=x0dQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, folks:

I'm trying to reduce execution time on a query using a partial index,
but Postgres doesn't make a significant improvement, even when the
partial index is 30 times smaller than the index used currently. Query
plan returns a slightly higher cost (cost=0.00..327952.12) for the
partial index than the one used instead (cost=0.00..327446.61).

The table is a partitioned table, holding telephone calls for one
month. The partial index holds the calls for just one day.

The table:

\d calls_201109
...
Indexes:
"calls_201109_index_1" UNIQUE, btree (company, call_date,
caller_cli, receiver_cli, call_time, caller_cli_whs, outgoing_call)
"calls_201109_index_2" btree (company, call_date, caller_cli)
"calls_201109_index_partial" btree (company, call_date,
caller_cli) WHERE call_date = '2011-09-01'::date

Using partial index "calls_201109_index_partial":

REINDEX TABLE calls_201109;
ANALYZE calls_201109;

EXPLAIN ANALYZE
SELECT *
FROM calls_201109
WHERE company = 1
AND call_date = '20110901'
AND outgoing_call!='I'
;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using calls_201109_index_2 on calls_201109
(cost=0.00..327952.12 rows=225604 width=866) (actual
time=0.061..456.512 rows=225784 loops=1)
Index Cond: ((company = 1) AND (call_date = '2011-09-01'::date))
Filter: (outgoing_call <> 'I'::bpchar)
Total runtime: 643.349 ms

Size of the (partial) index used:

SELECT pg_size_pretty(pg_total_relation_size('calls_201109_index_partial'));
pg_size_pretty
----------------
11 MB

Without using partial index ("calls_201109_index_2" is used instead):

DROP INDEX calls_201109_index_partial;
REINDEX TABLE calls_201109;
ANALYZE calls_201109;

EXPLAIN ANALYZE
SELECT *
FROM calls_201109
WHERE company = 1
AND call_date = '20110901'
AND outgoing_call!='I'
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using calls_201109_index_2 on calls_201109
(cost=0.00..327446.61 rows=225015 width=865) (actual
time=0.103..468.209 rows=225784 loops=1)
Index Cond: ((company = 1) AND (call_date = '2011-09-01'::date))
Filter: (outgoing_call <> 'I'::bpchar)
Total runtime: 656.103 ms

Size of the index used:

SELECT pg_size_pretty(pg_total_relation_size('calls_201109_index_2'));
pg_size_pretty
----------------
330 MB

Any idea on how to make partial index effective?

Thanks in advance.
Ruben.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gnanakumar 2012-01-18 12:58:52 Re: On duplicate ignore
Previous Message Atul Goel 2012-01-18 12:23:35 Re: On duplicate ignore