On Thu, 10 Jul 2008 10:46:53 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> This sub-select is non optimizable because you've got an outer
> reference in it, which compels re-evaluating it at every row of
> the outer query. Try recasting as
> explain select i1.brands, i1.name, i1.dataPub, i1.datains
On Thu, 10 Jul 2008 10:46:53 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> > Now I try this:
>
> > explain select i1.brands, i1.name, i1.dataPub, i1.datainserimento
> > from catalog_items i1
> > inner join catalog_brands b1 on upper(i1.brands)
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> Now I try this:
> explain select i1.brands, i1.name, i1.dataPub, i1.datainserimento
> from catalog_items i1
> inner join catalog_brands b1 on upper(i1.brands)=upper(b1.name)
> where i1.ItemID in (
> select i2.ItemID from catalog_items i2
On Thu, Jul 10, 2008 at 11:40:40AM +0200, Ivan Sergio Borgonovo wrote:
> I've this:
What's basically killing you is this condition:
> select i2.ItemID from catalog_items i2
> inner join catalog_brands b2 on upper(i2.brands)=upper(b2.name)
> where i1.brands=i2.brands <*
> an
I've this:
CREATE TABLE catalog_brands
(
brandid serial NOT NULL,
"name" character varying(64) NOT NULL,
delivery smallint NOT NULL DEFAULT (24 * 15),
deliverymessage character varying(64),
brandtypeid integer,
brandgroupid integer,
CONSTRAINT catalog_brands_pkey PRIMARY KEY (brandid
On Thu, Jul 03, 2008 at 11:11:26AM -0400, Tom Lane wrote:
> Sam Mason <[EMAIL PROTECTED]> writes:
> > Not entirely sure why, but it'll probably have
> > something to do with avoiding ambiguity in the grammar.
>
> Right. The problem is the Berkeley-era decision to put index opclasses
> into the sy
Sam Mason <[EMAIL PROTECTED]> writes:
> You just want an extra set of brackets; i.e.:
> CREATE INDEX token_position_func ON token ((position+1));
> Should do the trick. Not entirely sure why, but it'll probably have
> something to do with avoiding ambiguity in the grammar.
Right. The problem
"Gregory Stark" <[EMAIL PROTECTED]> writes:
> CREATE INDEX token_position_func on (token(position+1))
Ooops, I misread that as if "token" were a function and not the table. Sam
Mason had the right syntax. Sorry.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me ab
<[EMAIL PROTECTED]> writes:
> Hi all:
>
> I'm trying to create a functional index into column position of token
> table (see below). I want to make something like:
>
> CREATE INDEX token_position_func
> ON token (position+1);
>
> but I get:
>
> test=# CREATE INDEX token_position_func
> test-# ON t
On Thu, Jul 03, 2008 at 11:50:39AM +0200, [EMAIL PROTECTED] wrote:
> test=# CREATE INDEX token_position_func
> test-# ON token (position+1);
> ERROR: syntax error at or near "+"
> LINE 2: ON token (position+1);
>
> I read that I can do "ON function(column)" but, is there a built-in
> function in
am Thu, dem 03.07.2008, um 11:50:39 +0200 mailte [EMAIL PROTECTED] folgendes:
> Hi all:
>
> I'm trying to create a functional index into column position of token
> table (see below). I want to make something like:
>
> CREATE INDEX token_position_func
> ON token (position+1);
>
> but I get:
>
>
Hi all:
I'm trying to create a functional index into column position of token
table (see below). I want to make something like:
CREATE INDEX token_position_func
ON token (position+1);
but I get:
test=# CREATE INDEX token_position_func
test-# ON token (position+1);
ERROR: syntax error at or nea
James B. Byrne wrote:
I am considering moving
theses sorts of purification routines into the DBMS because I feel that is
where they really belong. However, the prevailing sentiment of the community
surrounding Rails seems to hold otherwise so I wonder if this is really the
right thing to do.
On Thu, Mar 13, 2008 at 09:04:28AM -0400, James B. Byrne wrote:
> To return to my first question. Is doing this sort of thing considered good a
> practice?
for me - yes. keeping it in database is the best and safest option.
but you might get "issues" with orms.
> where they really belong. Howev
On Wed, March 12, 2008 17:02, hubert depesz lubaczewski wrote:
> correct way:
> select lower(trim(both ' ' from(regexp_replace(' depeSz hub ', E'\\s+', ' ',
> 'g' ;
>
> now. i would suggest *not* to use this as base for index.
>
> make a wrapper function instead:
>
> create function cleaned(
On Wed, Mar 12, 2008 at 11:46:12AM -0400, James B. Byrne wrote:
> CREATE UNIQUE INDEX idxUF_table_column ON table
> (lower(trim(both ' ' from(regexp_replace(, /( ){2,}/g," " )
> What I intend this to do is to squeeze out excess whitespace, strip off
> leading and trailing blanks, and then for
I am considering the utility value of creating a functional index on a name
field. To minimize the number of invalid searches caused by spacing errors
and mis-matched lettercase I am contemplating doing something like this:
CREATE UNIQUE INDEX idxUF_table_column ON table
(lower(trim(both ' ' fr
On Wed, Nov 22, 2006 at 11:24:33AM -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Wed, 22 Nov 2006, Alexander Presber wrote:
> >> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
> >> using varchar_ops);
>
> > IIRC, unless you're
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Wed, 22 Nov 2006, Alexander Presber wrote:
>> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
>> using varchar_ops);
> IIRC, unless you're in C locale, you'll want varchar_pattern_ops rather
> than varchar_ops on the index to make
On Wed, 22 Nov 2006, Alexander Presber wrote:
> Hello everybody,
>
> I am trying to speed up a query on an integer column by defining an
> index as follows
>
> > CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
> using varchar_ops);
>
> on column "main_subject".
>
> I had hope
use varchar_pattern_ops operator class, LIKE cannot use varchar_ops for non-C
locales.
Alexander Presber wrote:
Hello everybody,
I am trying to speed up a query on an integer column by defining an
index as follows
> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
usin
Hello everybody,
I am trying to speed up a query on an integer column by defining an
index as follows
> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
using varchar_ops);
on column "main_subject".
I had hoped to get speedups for right-fuzzy LIKE-searches, but EXPLAIN
I want to know how functional indexes are used "in the real world". Here
are the common uses:
* non-unique index on the first parts of a longish text field
(SUBSTRING(field)) to save disk space, while still allowing faster
searches than a sequential scan.
* indexing on LOWER(field)/UPPER(field
I want to know how functional indexes are used "in the real world". Here
are the common uses:
* non-unique index on the first parts of a longish text field
(SUBSTRING(field)) to save disk space, while still allowing faster
searches than a sequential scan.
* indexing on LOWER(field)/UPPER(field
Arguile <[EMAIL PROTECTED]> writes:
> On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
> [snip]
> > CREATE INDEX i_employees ON employees(lower(name));
> >
> > Let's also assume that the lower() function is computationally
> > expensive. Now if I have a query like:
> >
> > SELECT lower(name)
> >
On Tue, 2003-09-30 at 09:54, Mike Mascari wrote:
> Arguile wrote:
>
> > On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
> >
> >>CREATE INDEX i_employees ON employees(lower(name));
> >>
> >>Let's also assume that the lower() function is computationally
> >>expensive. Now if I have a query like:
>
Arguile wrote:
> On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
>
>>CREATE INDEX i_employees ON employees(lower(name));
>>
>>Let's also assume that the lower() function is computationally
>>expensive. Now if I have a query like:
>>
>>SELECT lower(name)
>>FROM employees
>>WHERE lower(name) = 'mik
Arguile <[EMAIL PROTECTED]> writes:
> On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
>> SELECT lower(name)
>> FROM employees
>> WHERE lower(name) = 'mike'
>>
>> will PostgreSQL re-evaluate lower(name)? Is it necessary?
> No, it won't re-evaluate.
I think he's asking whether the lower(name) app
On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
[snip]
> CREATE INDEX i_employees ON employees(lower(name));
>
> Let's also assume that the lower() function is computationally
> expensive. Now if I have a query like:
>
> SELECT lower(name)
> FROM employees
> WHERE lower(name) = 'mike'
>
> will
Let's assume I have a table like so:
CREATE TABLE employees (
employeeid text not null,
name text not null
);
CREATE INDEX i_employees ON employees(lower(name));
Let's also assume that the lower() function is computationally
expensive. Now if I have a query like:
SELECT lower(name)
FROM emplo
30 matches
Mail list logo
