Re: Combination of partial and full indexes

Поиск
Список
Период
Сортировка
От Gerardo Herzig
Тема Re: Combination of partial and full indexes
Дата
Msg-id 713978055.948687.1465320973563.JavaMail.root@fmed.uba.ar
обсуждение исходный текст
Ответ на Combination of partial and full indexes  (Rafał Gutkowski <goodkowski@gmail.com>)
Ответы Re: Combination of partial and full indexes  (Rafał Gutkowski <goodkowski@gmail.com>)
Список pgsql-performance
I dont think offers_source_id_o_key_idx will be used at all. It is a UNIQUE index on (source_id, o_key), but your query
doesnot filter for any "o_key", so reading that index does not provide the pointers needed to fetch the actual data in
thetable. 

I will try an index on source_id, offer_next_update(offers.update_ts, offers.update_freq) and see what happens

HTH
Gerardo

----- Mensaje original -----
> De: "Rafał Gutkowski" <goodkowski@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Martes, 7 de Junio 2016 10:39:14
> Asunto: [PERFORM] Combination of partial and full indexes
>
>
> Hi.
>
>
> I had a fight with a query planner because it doesn’t listen.
>
>
> There are two indexes:
>
>
> - with expression in descending order:
> "offers_offer_next_update_idx" btree (offer_next_update(update_ts,
> update_freq) DESC) WHERE o_archived = false
> - unique with two columns:
> "offers_source_id_o_key_idx" UNIQUE, btree (source_id, o_key)
>
>
> Here's the query with filter for offers.source_id columns which
> is pretty slow because "offers_source_id_o_key_idx" is not used:
>
>
> EXPLAIN ANALYZE
> SELECT offers.o_url AS offers_o_url
> FROM offers
> WHERE offers.source_id = 1 AND offers.o_archived = false AND now() >
> offer_next_update(offers.update_ts, offers.update_freq)
> ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
> LIMIT 1000;
>
>
> Limit (cost=0.68..23403.77 rows=1000 width=116) (actual
> time=143.544..147.870 rows=1000 loops=1)
> -> Index Scan using offers_offer_next_update_idx on offers
> (cost=0.68..1017824.69 rows=43491 width=116) (actual
> time=143.542..147.615 rows=1000 loops=1)
> Index Cond: (now() > offer_next_update(update_ts, update_freq))
> Filter: (source_id = 1)
> Rows Removed by Filter: 121376
> Total runtime: 148.023 ms
>
>
>
>
> When I remove filter on offers.source_id, query plan looks like this:
>
>
> EXPLAIN ANALYZE
> SELECT offers.o_url AS offers_o_url
> FROM offers
> WHERE offers.o_archived = false AND now() >
> offer_next_update(offers.update_ts, offers.update_freq)
> ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
> LIMIT 1000;
>
>
> Limit (cost=0.68..4238.27 rows=1000 width=116) (actual
> time=0.060..3.877 rows=1000 loops=1)
> -> Index Scan using offers_offer_next_update_idx on offers
> (cost=0.68..1069411.78 rows=252363 width=116) (actual
> time=0.058..3.577 rows=1000 loops=1)
> Index Cond: (now() > offer_next_update(update_ts, update_freq))
> Total runtime: 4.031 ms
>
>
>
>
> I even tried to change orders of conditions in second query but it
> doesn't seem
> to make a difference for a planner.
>
>
> Shouldn't query planner use offers_source_id_o_key_idx to speed up
> query above?
>
>
>
>
> PostgreSQL version: PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu,
> compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
>
>
> Configuration:
> name | current_setting | source
> ------------------------------+----------------------------------------+----------------------
> application_name | psql | client
> checkpoint_completion_target | 0.9 | configuration file
> checkpoint_segments | 3 | configuration file
> client_encoding | UTF8 | client
> DateStyle | ISO, MDY | configuration file
> default_text_search_config | pg_catalog.english | configuration file
> effective_cache_size | 128MB | configuration file
> external_pid_file | /var/run/postgresql/9.3-main.pid | configuration
> file
> lc_messages | en_US.UTF-8 | configuration file
> lc_monetary | en_US.UTF-8 | configuration file
> lc_numeric | en_US.UTF-8 | configuration file
> lc_time | en_US.UTF-8 | configuration file
> max_connections | 100 | configuration file
> max_locks_per_transaction | 168 | configuration file
> max_stack_depth | 2MB | environment variable
> port | 5432 | configuration file
> shared_buffers | 4GB | configuration file
> temp_buffers | 12MB | configuration file
> unix_socket_directories | /var/run/postgresql | configuration file
> work_mem | 16MB | configuration file
>
>
>
>
> Definitions:
>
>
>
> CREATE OR REPLACE FUNCTION public.offer_next_update(last timestamp
> without time zone, minutes smallint)
> RETURNS timestamp without time zone
> LANGUAGE plpgsql
> IMMUTABLE
> AS $function$
> BEGIN
> RETURN last + (minutes || ' min')::interval;
> END
> $function$
>
>
>
>


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

Предыдущее
От: Rafał Gutkowski
Дата:
Сообщение: Combination of partial and full indexes
Следующее
От: Nicolas Paris
Дата:
Сообщение: Re: array size exceeds the maximum allowed (1073741823) when building a json