Dave Cramer wrote:
> Interesting it works now, and the good news is it is *WAY* faster, this
> might be able to speed up marc's doc search by orders of magnitude
>
> this is searching 100536 rows
>
> select * from url where fn_strrev(url) like fn_strrev('%beta12.html');
> 1.57ms
>
>
> explain
Interesting it works now, and the good news is it is *WAY* faster, this
might be able to speed up marc's doc search by orders of magnitude
this is searching 100536 rows
select * from url where fn_strrev(url) like fn_strrev('%beta12.html');
1.57ms
explain select * from url where url like '%beta
Dave Cramer <[EMAIL PROTECTED]> writes:
> I'm using 7.4.1, the db was initdb --locale='C'
> and no I don't get them on plain indexes
Oh? If it's 7.4 then you can confirm the locale selection with
"show lc_collate" and "show lc_ctype" (I think the first of these
is what the LIKE optimization
I'm using 7.4.1, the db was initdb --locale='C'
and no I don't get them on plain indexes
Dave
On Tue, 2004-01-27 at 13:28, Tom Lane wrote:
> Dave Cramer <[EMAIL PROTECTED]> writes:
> > Tried, all the suggestions
>
> Mph. It works for me... what PG version are you using exactly,
> and are y
Dave Cramer <[EMAIL PROTECTED]> writes:
> Tried, all the suggestions
Mph. It works for me... what PG version are you using exactly,
and are you certain you've selected C locale? (Do you get LIKE
optimization on plain indexes?)
regards, tom lane
-
Tried, all the suggestions
--dc--
davec=# explain analyze select * from url where fn_strrev(url) like
fn_strrev('%beta12.html');
QUERY PLAN
---
Dave Cramer <[EMAIL PROTECTED]> writes:
> davec=# explain analyze select * from url where fn_strrev(url) like
> '%beta12.html';
Don't you need the % at the right end to have an indexable plan?
I suspect that both of your tries so far are actually semantically
wrong, and that what you intend is
se
On Tue, Jan 27, 2004 at 12:41:41PM -0500, Dave Cramer wrote:
> davec=# explain analyze select * from url where fn_strrev(url) like
> '%beta12.html';
Reverse the constant too:
davec=# explain analyze select * from url where fn_strrev(url) like
fn_strrev('%beta12.html');
You won't get an indexsca
On Tue, 27 Jan 2004, Dave Cramer wrote:
> same answer
>
> davec=# show enable_seqscan;
> enable_seqscan
>
> off
> (1 row)
>
> davec=# explain analyze select * from url where fn_strrev(url) like
> '%beta12.html';
That's still an unanchored like clause, besides I think that woul
same answer
davec=# show enable_seqscan;
enable_seqscan
off
(1 row)
davec=# explain analyze select * from url where fn_strrev(url) like
'%beta12.html';
QUERY PLAN
Dave Cramer <[EMAIL PROTECTED]> writes:
> create index r_url_idx on url( fn_strrev(url));
> explain select * from url where url like fn_strrev('%beta12.html');
>QUERY PLAN
> -
> Seq Scan on url (cost=0.00..13281.70 r
I'm curious what the result of a reverse index does on a table with url
like data, so I did the following
create function fn_strrev(text) returns text as 'return reverse($_[0])'
language 'plperl' with (iscachable);
create index r_url_idx on url( fn_strrev(url));
vacuum analyze;
explain select
12 matches
Mail list logo
