Thread: Easy question

Easy question

From
clemens.bertschler@gmail.com
Date:
Hi List
I have maybe an easy question but i do not find an answer, i have this
SQL query:

SELECT geom,group,production_facs FROM south_america
        WHERE municipio = ''
            OR municipio = 'ACRE'
            OR municipio = 'ADJUNTAS'
            OR municipio = 'AGUADA'

The performance of this query is quite worse as longer it gets, its
possible that this query gets over 20 to 30 OR comparisons, but then
the performance is really worse, is it possible to speed it up?
Thanks
Clemens


Re: Easy question

From
"codeWarrior"
Date:
SELECT geom, group, production_facs FROM south_america

WHERE UPPER(municipio) IN ('ACRE', 'ADJUNTAS', 'AGUADA');


<clemens.bertschler@gmail.com> wrote in message
news:1145741653.759727.38970@e56g2000cwe.googlegroups.com...
> Hi List
> I have maybe an easy question but i do not find an answer, i have this
> SQL query:
>
> SELECT geom,group,production_facs FROM south_america
> WHERE municipio = ''
> OR municipio = 'ACRE'
> OR municipio = 'ADJUNTAS'
> OR municipio = 'AGUADA'
>
> The performance of this query is quite worse as longer it gets, its
> possible that this query gets over 20 to 30 OR comparisons, but then
> the performance is really worse, is it possible to speed it up?
> Thanks
> Clemens
>



Re: Easy question

From
"Bert"
Date:
Thanks,
But the performance is the same just the formating is more simple.
Greets,
Bert


Re: Easy question

From
"Michael Artz"
Date:
You have a functional index on UPPER(municipo), right?  How large is the table?

On 26 Apr 2006 18:26:07 -0700, Bert < clemens.bertschler@gmail.com> wrote:
Thanks,
But the performance is the same just the formating is more simple.
Greets,
Bert


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Easy question

From
"mlartz@gmail.com"
Date:
I can't speak to "the access mode of the SQL statement" but it looks
like the index that you are looking for is an index on an expression,
as shown in:

http://www.postgresql.org/docs/8.0/static/indexes-expressional.html

You probably want a btree on UPPER(municipo), if that is the primary
query method for the column.


Re: Easy question

From
"Jim C. Nasby"
Date:
You didn't mention version, but 8.1.x has bitmap index scans that might
greatly speed this up...

On Sat, Apr 22, 2006 at 02:34:13PM -0700, clemens.bertschler@gmail.com wrote:
> Hi List
> I have maybe an easy question but i do not find an answer, i have this
> SQL query:
>
> SELECT geom,group,production_facs FROM south_america
>         WHERE municipio = ''
>             OR municipio = 'ACRE'
>             OR municipio = 'ADJUNTAS'
>             OR municipio = 'AGUADA'
>
> The performance of this query is quite worse as longer it gets, its
> possible that this query gets over 20 to 30 OR comparisons, but then
> the performance is really worse, is it possible to speed it up?
> Thanks
> Clemens
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Easy question

From
"Bert"
Date:
No i didn't defined any indexes for the table, I know the performance
will increase with an index, but this was not my question. My question
furthermore belongs to the access mode of the SQL statement.
Furthermore i do not understand why the Upper function should increase
the performance.
The table has approximately 20.000 entries.
Is it the best way to use a B-Tree index on the municipio column in
this case or are there better solution to do this.


Re: Easy question

From
Chris
Date:
Bert wrote:
> No i didn't defined any indexes for the table, I know the performance
> will increase with an index, but this was not my question. My question
> furthermore belongs to the access mode of the SQL statement.
> Furthermore i do not understand why the Upper function should increase
> the performance.

The index will have entries like:

CHRIS
BERT
JOE

and so on.

If you run a query like:

select * from table where UPPER(name) = 'CHRIS';

It's an easy match.

If you don't create an UPPER index, it has to do a comparison with each
row - so the index can't be used because postgres has to convert the
field to upper and then do the comparison.

--
Postgresql & php tutorials
http://www.designmagick.com/