Partial index does not make query faster

Поиск
Список
Период
Сортировка
От Ruben Blanco
Тема Partial index does not make query faster
Дата
Msg-id CAP4Xq2G_gvhh1D+E7yGKPSJuNT3yfniisdMu+8Qp+jFG3=x0dQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Partial index does not make query faster  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Atul Goel
Дата:
Сообщение: Re: On duplicate ignore
Следующее
От: "Gnanakumar"
Дата:
Сообщение: Re: On duplicate ignore