Обсуждение: Performance

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

I expect that you will need to post and explain analyze results of this query so that members of
the list can give you better feedback.

--- Bert <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 2: Don't 'kill -9' the postmaster
>

My first thought would be to consider changing the municipio column to an
int and listing the full names in another table.

In this scenario would you be able to search something like
where municipio>1 and municipio <20

If not you may still get improvements from an int search instead of a string
search.

On 23/4/2006 10:23, "Richard Broersma Jr" <rabroersma@yahoo.com> wrote:

> I expect that you will need to post and explain analyze results of this query
> so that members of
> the list can give you better feedback.
>
> --- Bert <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 2: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>      choose an index scan if your joining column's datatypes do not
>      match
>

Bert --

You don't include some necessary details:

What version of postgres ? (8.x is noticably better at that OR / IN stuff I think, at least to some degree)

Please include some info about your setup -- what are the settings in the postgres config file (effective_cache_size,
random_page_cost,work_mem [for postgres 8.x) or sort_mem in 7.x) 

Perhaps some info about the server in question (OS, amount of RAM, # of disks and their setup)

Please also post the results of several EXPLAIN ANALYZE <sql here>; this will help people see what decisions the
planneris making. Perhaps best to show one that is small, an intermdediate size and one that is painfully slow (the
explainanalyze has to run the actual query in question so I realize that this may be too slow, but some examples will
help).

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:    pgsql-general-owner@postgresql.org on behalf of Bert
Sent:    Sat 4/22/2006 4:18 PM
To:    pgsql-general@postgresql.org
Cc:
Subject:    [GENERAL] Performance

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 2: Don't 'kill -9' the postmaster

!DSPAM:444aba30189631465223968!