pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedEasy question
8+ messages / 7 participants
[nested] [flat]
* Easy question
@ 2006-04-22 21:34 clemens.bertschler@gmail.com
2006-04-25 15:55 ` Re: Easy question codeWarrior <gpatnude@hotmail.com>
2006-05-02 17:14 ` Re: Easy question Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 8+ messages in thread
From: clemens.bertschler@gmail.com @ 2006-04-22 21:34 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Easy question
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
@ 2006-04-25 15:55 ` codeWarrior <gpatnude@hotmail.com>
2006-04-27 01:26 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: codeWarrior @ 2006-04-25 15:55 UTC (permalink / raw)
To: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Easy question
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
2006-04-25 15:55 ` Re: Easy question codeWarrior <gpatnude@hotmail.com>
@ 2006-04-27 01:26 ` Bert <clemens.bertschler@gmail.com>
2006-04-29 21:53 ` Re: Easy question Michael Artz <mlartz@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bert @ 2006-04-27 01:26 UTC (permalink / raw)
To: pgsql-performance
Thanks,
But the performance is the same just the formating is more simple.
Greets,
Bert
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Easy question
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
2006-04-25 15:55 ` Re: Easy question codeWarrior <gpatnude@hotmail.com>
2006-04-27 01:26 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
@ 2006-04-29 21:53 ` Michael Artz <mlartz@gmail.com>
2006-04-30 00:07 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Artz @ 2006-04-29 21:53 UTC (permalink / raw)
To: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Easy question
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
2006-04-25 15:55 ` Re: Easy question codeWarrior <gpatnude@hotmail.com>
2006-04-27 01:26 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
2006-04-29 21:53 ` Re: Easy question Michael Artz <mlartz@gmail.com>
@ 2006-04-30 00:07 ` Bert <clemens.bertschler@gmail.com>
2006-05-01 11:35 ` Re: Easy question mlartz@gmail.com <mlartz@gmail.com>
2006-05-03 06:21 ` Re: Easy question Chris <dmagick@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Bert @ 2006-04-30 00:07 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Easy question
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
2006-04-25 15:55 ` Re: Easy question codeWarrior <gpatnude@hotmail.com>
2006-04-27 01:26 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
2006-04-29 21:53 ` Re: Easy question Michael Artz <mlartz@gmail.com>
2006-04-30 00:07 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
@ 2006-05-01 11:35 ` mlartz@gmail.com <mlartz@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: mlartz@gmail.com @ 2006-05-01 11:35 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Easy question
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
2006-04-25 15:55 ` Re: Easy question codeWarrior <gpatnude@hotmail.com>
2006-04-27 01:26 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
2006-04-29 21:53 ` Re: Easy question Michael Artz <mlartz@gmail.com>
2006-04-30 00:07 ` Re: Easy question Bert <clemens.bertschler@gmail.com>
@ 2006-05-03 06:21 ` Chris <dmagick@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Chris @ 2006-05-03 06:21 UTC (permalink / raw)
To: Bert <clemens.bertschler@gmail.com>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Easy question
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
@ 2006-05-02 17:14 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-05-02 17:14 UTC (permalink / raw)
To: clemens.bertschler@gmail.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-05-03 06:21 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-22 21:34 Easy question clemens.bertschler@gmail.com
2006-04-25 15:55 ` codeWarrior <gpatnude@hotmail.com>
2006-04-27 01:26 ` Bert <clemens.bertschler@gmail.com>
2006-04-29 21:53 ` Michael Artz <mlartz@gmail.com>
2006-04-30 00:07 ` Bert <clemens.bertschler@gmail.com>
2006-05-01 11:35 ` mlartz@gmail.com <mlartz@gmail.com>
2006-05-03 06:21 ` Chris <dmagick@gmail.com>
2006-05-02 17:14 ` Jim C. Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox