pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance w/ multiple WHERE clauses
6+ messages / 4 participants
[nested] [flat]
* Performance w/ multiple WHERE clauses
@ 2002-09-18 21:17 Aaron Held <aaron@metrony.com>
0 siblings, 2 replies; 6+ messages in thread
From: Aaron Held @ 2002-09-18 21:17 UTC (permalink / raw)
To: pgsql-sql
I am running into a serious performance issue with some basic queries.
If I run something like
select * from "Calls" WHERE
( ("CallType" = 'LONG DIST' ))
The search takes about 15 seconds
if I run
select * from "Calls" WHERE
( ( "DateOfCall"='06/19/02') )
AND ( ( "CallType" = 'LONG DIST' ))
[DateOfCall is a DateTime field]
it takes 15 minutes!
both columns are indexed individually and expalain reports and Index scan for both
queries.
Any pointers where to look next?
Running pg 7.2 on RH
Thanks,
-Aaron Held
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance w/ multiple WHERE clauses
@ 2002-09-18 21:58 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Aaron Held <aaron@metrony.com>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2002-09-18 21:58 UTC (permalink / raw)
To: Aaron Held <aaron@metrony.com>; +Cc: pgsql-sql
On Wed, 18 Sep 2002, Aaron Held wrote:
> I am running into a serious performance issue with some basic queries.
>
> If I run something like
>
> select * from "Calls" WHERE
> ( ("CallType" = 'LONG DIST' ))
>
> The search takes about 15 seconds
>
> if I run
> select * from "Calls" WHERE
> ( ( "DateOfCall"='06/19/02') )
> AND ( ( "CallType" = 'LONG DIST' ))
> [DateOfCall is a DateTime field]
>
> it takes 15 minutes!
>
> both columns are indexed individually and expalain reports and Index scan for both
> queries.
>
> Any pointers where to look next?
Are they using the same index? Have you done analyze
recently?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance w/ multiple WHERE clauses
@ 2002-09-20 14:09 Aaron Held <aaron@MetroNY.com>
parent: Aaron Held <aaron@metrony.com>
1 sibling, 1 reply; 6+ messages in thread
From: Aaron Held @ 2002-09-20 14:09 UTC (permalink / raw)
To: Chris Ruprecht <chrup@earthlink.net>; pgsql-sql
Thanks,
Changing '0/19/01' to '0/19/01'::date gave me a subjective 50% speedup.
A ran a bunch of queries w/ explain and I noticed that some
combinations did not use the indexes and went right to seq scan. All of
the where clause args are indexed.
# SET enable_seqscan to FALSE ;
forced the use of an Index and sped things up greatly.
I am not sure why it made the switch. The load on the server seems to
affect the performance, but I am seeing it more on the production server
with 100 million rows as opposed to the development server with only
about 6 million. I need to buy more drives and develop on a larger data
set.
Thanks for the help,
-Aaron Held
Chris Ruprecht wrote:
> Aaron,
>
> On Wed September 18 2002 17:17, Aaron Held wrote:
>
>>I am running into a serious performance issue with some basic queries.
>>
>>If I run something like
>>
>> select * from "Calls" WHERE
>> ( ("CallType" = 'LONG DIST' ))
>>
>>The search takes about 15 seconds
>>
>>if I run
>> select * from "Calls" WHERE
>> ( ( "DateOfCall"='06/19/02') )
>> AND ( ( "CallType" = 'LONG DIST' ))
>> [DateOfCall is a DateTime field]
>
>
> try ... "DateOfCall" = '2002-06-19'::date ...
>
> Best regards,
> Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance w/ multiple WHERE clauses
@ 2002-09-20 21:55 Josh Berkus <josh@agliodbs.com>
parent: Aaron Held <aaron@MetroNY.com>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2002-09-20 21:55 UTC (permalink / raw)
To: Aaron Held <aaron@MetroNY.com>; Chris Ruprecht <chrup@earthlink.net>; pgsql-sql
Aaron,
> # SET enable_seqscan to FALSE ;
> forced the use of an Index and sped things up greatly.
>
> I am not sure why it made the switch. The load on the server seems to
> affect the performance, but I am seeing it more on the production server
> with 100 million rows as opposed to the development server with only
> about 6 million. I need to buy more drives and develop on a larger data
> set.
What version are you using?
I'd have 3 suggestions:
1) ANALYZE, ANALYZE, ANALYZE. Then check if the row estimates made by EXPLAIN
seem accurate.
2) Modify your postgresql.conf file to raise the cost of seq_scans for parser
estimates.
3) Test this all again when 7.3 comes out, as parser estimate improves all the
time.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance w/ multiple WHERE clauses
@ 2002-09-23 13:31 Aaron Held <aaron@MetroNY.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Aaron Held @ 2002-09-23 13:31 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Chris Ruprecht <chrup@earthlink.net>; pgsql-sql
I am running pg 7.2 the PG reference build.
Thanks for the ANALYZE tip, it led me to a answer.
This database gets a monthly update and it read only until the next
update. I ANALYZE once after each update. Since the data does not
change I should not need to ANALYZE again afterwards.
I mentioned this to the dbadmin that manages the data and found out one
of the other users UPDATED some of the columns the morning that I was
seeing this behavior.
I'll reANALYZE and see what happens.
Thanks,
-Aaron Held
Josh Berkus wrote:
> Aaron,
>
>
>> # SET enable_seqscan to FALSE ;
>> forced the use of an Index and sped things up greatly.
>>
>>I am not sure why it made the switch. The load on the server seems to
>>affect the performance, but I am seeing it more on the production server
>>with 100 million rows as opposed to the development server with only
>>about 6 million. I need to buy more drives and develop on a larger data
>>set.
>
>
> What version are you using?
>
> I'd have 3 suggestions:
> 1) ANALYZE, ANALYZE, ANALYZE. Then check if the row estimates made by EXPLAIN
> seem accurate.
> 2) Modify your postgresql.conf file to raise the cost of seq_scans for parser
> estimates.
> 3) Test this all again when 7.3 comes out, as parser estimate improves all the
> time.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance w/ multiple WHERE clauses
@ 2002-09-24 02:14 Josh Berkus <josh@agliodbs.com>
parent: Aaron Held <aaron@MetroNY.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2002-09-24 02:14 UTC (permalink / raw)
To: Aaron Held <aaron@MetroNY.com>; +Cc: Chris Ruprecht <chrup@earthlink.net>; pgsql-sql
Aaron,
> This database gets a monthly update and it read only until the next
> update. I ANALYZE once after each update. Since the data does not
> change I should not need to ANALYZE again afterwards.
Actually, if the database is read-only between updates, you should:
ANALYZE
VACUUM FULL FREEZE
... between data updates, which will give you the best performance possible.
But not if the data is actually going to be edited, even slightly.
See the online manual entries about VACUUM for an explanation.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-09-24 02:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-18 21:17 Performance w/ multiple WHERE clauses Aaron Held <aaron@metrony.com>
2002-09-18 21:58 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-20 14:09 ` Aaron Held <aaron@MetroNY.com>
2002-09-20 21:55 ` Josh Berkus <josh@agliodbs.com>
2002-09-23 13:31 ` Aaron Held <aaron@MetroNY.com>
2002-09-24 02:14 ` Josh Berkus <josh@agliodbs.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