2008/1/10, Scott Marlowe <scott.marlowe@xxxxxxxxx>: > On Jan 10, 2008 9:50 AM, Clodoaldo <clodoaldo.pinto.neto@xxxxxxxxx> 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 ---------------------------(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