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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
