Re: ATTN: Clodaldo was Performance problem. Could it be related to 8.3-beta4?

2008/1/10, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
> On Jan 10, 2008 9:50 AM, Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> wrote:
>
> > I have seen performance degradation at every new version since 7.3.
> > But now 8.3 is a complete disaster. It could be that my most expensive
> > query is just a corner case, but I don't believe it. I posted about it
> > but the whole thread disappeared from the archives. It can still be
> > found here:
>
> OK, I was looking at the previous thread that you thought had
> disappeared, and with the explain analyze output from 8.3 I noticed
> something odd.
>
> For 8.2 you had something like this:
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Subquery Scan "*SELECT*" (cost=326089.49..350310.28 rows=880756
> width=20) (actual time=11444.566..13114.365 rows=880691 loops=1)
> -> HashAggregate (cost=326089.49..339300.83 rows=880756 width=12)
> (actual time=11444.554..12438.188 rows=880691 loops=1)
> ... SNIP ...
> Time: 357750.531 ms
>
> And for 8.3 you had something like this:
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Subquery Scan "*SELECT*" (cost=316145.48..340289.33 rows=877958
> width=20) (actual time=10650.036..12997.377 rows=877895 loops=1)
> -> HashAggregate (cost=316145.48..329314.85 rows=877958 width=12)
> (actual time=10650.023..12193.890 rows=877895 loops=1)
> ... SNIP ...
> Time: 9547801.116 ms
>
> In both of those instances, the actual time used is WAY larger than
> the time listed in the explain analyze, which has usually pointed to a
> very expensive OS level get time of day call.

If you read that thread you will notice my experience with
xlog_seg_size of 1GB which makes the time goes down to 1,300 sec,
still much more than 8.2. Could the bigger xlog_seg_size be
alleviating the expensive "OS level get time of day call", whatever it
means? With the standard xlog_seg_size, 16MB, I can see lots of file
activity in the pg_xlog directory. This fact made me guess
xlog_seg_size could be used to make things faster.
>
> How fast do these two queries run if you just run them. i.e. do
>
> \timing
> select ....

Without the explain analyze it ran in 7,642 sec although the second
time i ran it with explain analyze it ran in 7,683 sec. Yes, I did
analyze it before the first run of 9,547 sec. with explain analyze.

Regards, Clodoaldo Pinto Neto