>>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_countDESC 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")
Last question: the table is partitioned. I need to manually create index for every sub-tables or there is a way to
createon every sub-tables once?
THANK YOU!
/F
