Hello > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Job > Sent: Freitag, 30. Dezember 2016 11:42 > To: Francisco Olarte <folarte@xxxxxxxxxxxxxx> > Cc: David G. Johnston <david.g.johnston@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx > Subject: R: 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
