Thank you all. Both the double index & pg_trgm would be good solutions.
On Oct 14, 2013, at 3:40 PM, Merlin Moncure wrote:
> On Sat, Oct 12, 2013 at 4:28 PM, Torsten Förtsch
> wrote:
>> On 12/10/13 20:08, Scott Ribe wrote:
>>> select * from test where tz >= start and tz < end and colb like '%fo
On Sat, Oct 12, 2013 at 4:28 PM, Torsten Förtsch
wrote:
> On 12/10/13 20:08, Scott Ribe wrote:
>> select * from test where tz >= start and tz < end and colb like '%foobar%'
>
> I think you can use an index only for wildcard expressions that are
> anchored at the beginning. So,
>
> select * from
On Oct 12, 2013, at 4:21 PM, Tom Lane wrote:
> The reason you're losing on this is that the "select *" command eliminates
> the possibility of an index-only scan (I'm assuming that that selects some
> columns that aren't in the index). Given that a plain indexscan will
> always involve fetching
Scott Ribe writes:
> PG 9.3, consider a table test like:
> tz timestamp not null,
> cola varchar not null,
> colb varchar not null
> 2 compound indexes:
> tz_cola on (tz, cola)
> tz_colb on (tz, colb varchar_pattern_ops)
> now a query, for some start & end timestamps:
> select * from test wher
On Sat, Oct 12, 2013 at 11:08 AM, Scott Ribe
wrote:
[skipped]
> select * from test where tz >= start and tz < end and colb like '%foobar%'
>
> Assume that the tz restriction is somewhat selective, say 1% of the table,
> and the colb restriction is extremely selective, say less than 0.1%.
[s
On 12/10/13 20:08, Scott Ribe wrote:
> select * from test where tz >= start and tz < end and colb like '%foobar%'
I think you can use an index only for wildcard expressions that are
anchored at the beginning. So,
select * from test where tz >= start and tz < end
and colb like 'foobar%'
ca
PG 9.3, consider a table test like:
tz timestamp not null,
cola varchar not null,
colb varchar not null
2 compound indexes:
tz_cola on (tz, cola)
tz_colb on (tz, colb varchar_pattern_ops)
now a query, for some start & end timestamps:
select * from test where tz >= start and tz < end and colb l
