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.
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 |