From: | Job <Job(at)colliniconsulting(dot)it> |
---|---|
To: | Job <Job(at)colliniconsulting(dot)it>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | R: Special index for "like"-based query |
Date: | 2016-12-30 10:00:28 |
Message-ID: | 88EF58F000EC4B4684700C2AA3A73D7A08054EAEBAE9@W2008DC01.ColliniConsulting.lan |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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-owner(at)postgresql(dot)org [pgsql-general-owner(at)postgresql(dot)org] per conto di Job [Job(at)colliniconsulting(dot)it]
Inviato: venerdì 30 dicembre 2016 10.55
A: David G. Johnston
Cc: pgsql-general(at)postgresql(dot)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(dot)g(dot)johnston(at)gmail(dot)com]
Inviato: venerdì 30 dicembre 2016 0.33
A: Job
Cc: pgsql-general(at)postgresql(dot)org
Oggetto: Re: [GENERAL] Special index for "like"-based query
On Thu, Dec 29, 2016 at 4:21 PM, Job <Job(at)colliniconsulting(dot)it<mailto:Job(at)colliniconsulting(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2016-12-30 10:18:29 | Re: Special index for "like"-based query |
Previous Message | Job | 2016-12-30 09:55:34 | R: Special index for "like"-based query |