R: [GENERAL] Special index for "like"-based query

[Job]

Hi Alban,

I was wrong: i have only one column: tsrarnge.

Which index can i create to use this statement fastly:
 ... AND timestamp <@ '[2016-12-27 00:00:00, 2016-12-31 00:00:00)'::tsrange

Thank you again!

/F

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

R: [GENERAL] Special index for "like"-based query

[Job]

Dear Alban,

Regarding:

>>... AND timestamp BETWEEN '2016-12-27 00:00:00'::timestamp with time zone AND 
>>'2016-12-30 23:59:59'::timestamp with time zone ... 

I think it is a very good approach, and i would like to try.
My table has got two different field for "starting" and "ending" timestamp 
values.

Shall i create an index together with the two field (starting and ending) with 
the "tsrange" statement?

Thank you again!

/F

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

R: [GENERAL] Special index for "like"-based query

[Job]

>>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")

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

R: [GENERAL] Special index for "like"-based query

[Job]

I tried to create a GIST/GIN index on a timestamp without time zone field but 
it does not work.
Are there alternatives index types or timezone could speed query up?

Thank you
/F


Da: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] per 
conto di Job [j...@colliniconsulting.it]
Inviato: venerdì 30 dicembre 2016 10.55
A: David G. Johnston
Cc: pgsql-general@postgresql.org
Oggetto: R: [GENERAL] Special index for "like"-based query

>>GIST​
>>https://www.postgresql.org/docs/9.6/static/pgtrgm.html
>>​https://www.postgresql.org/docs/9.6/static/btree-gist.html

I tried with a GIST-like index and queries improves a lot, thank you!

Furthermore, this type of index is also suitable for a timestamp query, where 
we can mix date and time parameters?

Thank you again!
/F



Da: David G. Johnston [david.g.johns...@gmail.com]
Inviato: venerdì 30 dicembre 2016 0.33
A: Job
Cc: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:21 PM, Job 
<j...@colliniconsulting.it<mailto:j...@colliniconsulting.it>> wrote:
Hello,

in Postgresql 9.6 we have a query running on a very large table based, in some 
cases, on a like statement:

... WHERE FIELD='CODE' OR FIELD LIKE 'CODE_%'

Which type of index can i create to speed to the search when the "like" case 
happens?

​GIST​

https://www.postgresql.org/docs/9.6/static/pgtrgm.html

​https://www.postgresql.org/docs/9.6/static/btree-gist.html
​
David J.

R: [GENERAL] Special index for "like"-based query

[Job]

>>GIST​
>>https://www.postgresql.org/docs/9.6/static/pgtrgm.html
>>​https://www.postgresql.org/docs/9.6/static/btree-gist.html

I tried with a GIST-like index and queries improves a lot, thank you!

Furthermore, this type of index is also suitable for a timestamp query, where 
we can mix date and time parameters?

Thank you again!
/F



Da: David G. Johnston [david.g.johns...@gmail.com]
Inviato: venerdì 30 dicembre 2016 0.33
A: Job
Cc: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:21 PM, Job 
> wrote:
Hello,

in Postgresql 9.6 we have a query running on a very large table based, in some 
cases, on a like statement:

... WHERE FIELD='CODE' OR FIELD LIKE 'CODE_%'

Which type of index can i create to speed to the search when the "like" case 
happens?

​GIST​

https://www.postgresql.org/docs/9.6/static/pgtrgm.html

​https://www.postgresql.org/docs/9.6/static/btree-gist.html
​
David J.