Thread: Easy question

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

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
>

Thanks,
But the performance is the same just the formating is more simple.
Greets,
Bert

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.

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

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.

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/