Re: Special index for "like"-based query

Hello

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Job
> Sent: Freitag, 30. Dezember 2016 11:42
> To: Francisco Olarte <folarte(at)peoplecall(dot)com>
> Cc: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; pgsql-general(at)postgresql(dot)org
> Subject: R: [GENERAL] Special index for "like"-based query
>
> >>And, basically, if you need help with some queries you could try
> >>posting them whole, even redacted, along the table defs, this way
> >>perople can see the problem and not invent one based on a partial
> >>description
>
> Thank you very much, very kind from you.
>
> The index applied on the timestamp field is a btree("timestamp")
>
> The query is:
>
> select domain, sum(accessi) as c_count from TABLE where action='1' AND profile IN ('PROFILE_CODE') AND
> timestamp::date BETWEEN '2016-12-27' AND '2016-12-30' AND timestamp::time BETWEEN '00:00:00' AND '23:59:59' GROUP
> BY domain ORDER BY c_count DESC LIMIT 101
>
> The table format is:
> Column | Type | Modifiers
> -----------+--------------------------+-----------------------------------------------------------------------------
> ---
> id | numeric(1000,1) | not null default
> function_get_next_sequence('webtraffic_archive_id_seq'::text)
> timestamp | timestamp with time zone |
> domain | character varying(255) |
> action | character varying(5) |
> profile | character varying |
> accessi | bigint |
> url | text |
>
> Indexes:
> "webtraffic_archive_day_2016_04_15_action_wbidx" btree (action)
> "webtraffic_archive_day_2016_04_15_domain_wbidx" btree (domain)
> "webtraffic_archive_day_2016_04_15_profile_wbidx" btree (profile) CLUSTER
> "webtraffic_archive_day_2016_04_15_timestamp_wbidx" btree ("timestamp")

Is the index on timestamp used at all? The index on timestamp is built on timestamp, but you query using timestamp::date.
You can check this using EXPLAIN.

Bye
Charles

>
> Last question: the table is partitioned. I need to manually create index for every sub-tables or there is a way to
> create on every sub-tables once?
>
> THANK YOU!
> /F
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general