Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time

Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
> Wednesday 12 January 2011 17:07:53 / Tom Lane :
>> Right offhand I'd wonder whether that was more bound by gettimeofday or
>> by printf. Please try it without printf in the loop.

> Changed that to smth like: micros_total = micros_total + (double) micros;
> instead of the printf to beat any compiler optimization, and still linux runs at light speed:
> FBSD_TEST : user 0.089s, sys 1.4s
> FBSD_DEV : user 0.183s, sys 3.8s
> LINUX_PROD : user 0.168s, sys 0s

Well, there's your problem all right: the FBSD_DEV system takes 22X
longer to execute gettimeofday() than the LINUX_PROD system. The
particular plan that 9.0 is choosing is especially vulnerable to this
because it involves a whole lot of calls of the Materialize plan node:

> -> Nested Loop (cost=3390.49..9185.73 rows=1 width=4) (actual time=279.916..93280.499 rows=1824 loops=1)
> Join Filter: (msold.marinerid = mold.id)
> -> Index Scan using mariner_pkey on mariner mold (cost=0.00..1957.89 rows=15914 width=4) (actual time=0.009..38.449 rows=15914 loops=1)
> Filter: ((marinertype)::text = 'Mariner'::text)
> -> Materialize (cost=3390.49..6989.13 rows=1 width=8) (actual time=0.013..2.881 rows=1888 loops=15914)

You've got 1888 * 15914 * 2 gettimeofday() calls needed to count up the
time spent in Materialize, which at 3.8usec apiece adds up to ... hmm
... I get 228 seconds, which is more than the reported runtime. So I
think there's something wrong with your measurement of the gettimeofday
cost. But I wouldn't be a bit surprised to hear that the true cost of
gettimeofday on that machine is a microsecond or so, in which case all
of the EXPLAIN ANALYZE time bloat is indeed due to that.

This problem has been discussed repeatedly on the pgsql-performance
list, which is where your question really belongs, not here. The short
of it is that cheap motherboards tend to provide cheap clock hardware
that takes about a microsecond to read. It also helps to be running
an OS that is able to read the clock in userspace without invoking a
kernel call. It looks like your Linux box is winning on both counts
compared to your BSD boxes.

regards, tom lane