Re: [PERFORM] Weird performance drop after VACUUM

2005-08-29 Thread asif ali
Michael The database is on the same system. What I am doing is only VACUUM analyze conversion_table I did the the same thing on a newly created database. And got the same result. So after VACUUM analyze performance dropped. Please see this. Runtime changes from 7755.115 to 14859.291 ms explain

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-29 Thread Michael Fuhr
On Mon, Aug 29, 2005 at 11:07:17AM -0700, asif ali wrote: The database is on the same system. What I am doing is only VACUUM analyze conversion_table I did the the same thing on a newly created database. And got the same result. So after VACUUM analyze performance dropped. Please see

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-29 Thread asif ali
Michael, The effective_cache_size, random_page_cost, work_mem were set to default. (commented). I have changed the setting of these and now the performance is better see below. HashAggregate (cost=42573.89..42925.52 rows=20093 width=37) (actual time=5273.984..5430.733 rows=55717 loops=1) -

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-27 Thread Umit Oztosun
On Cum, 2005-08-26 at 19:31 -0400, Tom Lane wrote: I think the problem is that the planner is underestimating the cost of evaluating this complicated filter condition: - Seq Scan on scf_irsaliye irs (cost=0.00..30.00 rows=1 width=20) (actual time=0.290..0.290 rows=0

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-27 Thread Steinar H. Gunderson
On Fri, Aug 26, 2005 at 07:31:51PM -0400, Tom Lane wrote: Or you could just play with the order of the filter conditions ... for example, the date condition at the end is probably far cheaper to test than the text comparisons, so if that's fairly selective it'd be worth putting it first.

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-27 Thread Steinar H. Gunderson
On Sat, Aug 27, 2005 at 11:05:01AM -0400, Tom Lane wrote: It could, but it doesn't really have enough information. We don't currently have any model that some operators are more expensive than others. IIRC the only sort of reordering the current code will do in a filter condition list is to

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-27 Thread Tom Lane
Steinar H. Gunderson [EMAIL PROTECTED] writes: On Sat, Aug 27, 2005 at 11:05:01AM -0400, Tom Lane wrote: It could, but it doesn't really have enough information. We don't currently have any model that some operators are more expensive than others. IIRC the only sort of reordering the current

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-27 Thread Tom Lane
Steinar H. Gunderson [EMAIL PROTECTED] writes: On Fri, Aug 26, 2005 at 07:31:51PM -0400, Tom Lane wrote: Or you could just play with the order of the filter conditions ... for example, the date condition at the end is probably far cheaper to test than the text comparisons, so if that's fairly

[PERFORM] Weird performance drop after VACUUM

2005-08-26 Thread Ümit Öztosun
Hello, We are using PostgreSQL for our business application. Recently, during testing of our application with large volumes of data, we faced a weird problem. Our query performance dropped *dramatically* after VACUUM FULL ANALYZE command. We have encountered a similar problem listed on mailing

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-26 Thread asif ali
Hi, I have the same issue. After doing VACCUME ANALYZE performance of the query dropped. Here is the query explain select * from conversion_table c where c.conversion_date BETWEEN '2005-06-07' and '2005-08-17' Before VACCUME ANALYZE Index Scan using conversion_table_pk on

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-26 Thread Philip Hallstrom
Hi, I have the same issue. After doing VACCUME ANALYZE performance of the query dropped. Here is the query explain select * from conversion_table c where c.conversion_date BETWEEN '2005-06-07' and '2005-08-17' Before VACCUME ANALYZE Index Scan using conversion_table_pk on

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-26 Thread Michael Fuhr
On Fri, Aug 26, 2005 at 03:52:24PM -0700, asif ali wrote: I have the same issue. After doing VACCUME ANALYZE performance of the query dropped. Your EXPLAIN output doesn't show the actual query times -- could you post the EXPLAIN ANALYZE output? That'll also show how accurate the planner's row

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-26 Thread Tom Lane
=?ISO-8859-1?Q?=DCmit_=D6ztosun?= [EMAIL PROTECTED] writes: We are using PostgreSQL for our business application. Recently, during testing of our application with large volumes of data, we faced a weird problem. Our query performance dropped *dramatically* after VACUUM FULL ANALYZE command. I

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-26 Thread asif ali
Thanks Michael For your reply. Here is performance on the database on which i did VACUUM ANALYZE explain analyze select keyword_id ,sum(daily_impressions) as daily_impressions ,sum(daily_actions)as daily_actions from conversion_table c wherec.conversion_date BETWEEN

Re: [PERFORM] Weird performance drop after VACUUM

2005-08-26 Thread Michael Fuhr
On Fri, Aug 26, 2005 at 05:10:49PM -0700, asif ali wrote: GroupAggregate (cost=195623.66..206672.52 rows=20132 width=16) (actual time=8205.283..10139.369 rows=55291 loops=1) - Sort (cost=195623.66..198360.71 rows=1094820 width=16) (actual time=8205.114..9029.501 rows=863883 loops=1)