Обсуждение: strange

Hi,

Szymon Guz <mabewlun@gmail.com> writes:
> I've got a simple query. When I use explain analyze it lasts 7 times slower.
> Why?

You've got a machine where gettimeofday() is really slow.  This is
common on cheap PC hardware :-(

            regards, tom lane

Tom Lane wrote:

Szymon Guz <mabewlun@gmail.com> writes: 

I've got a simple query. When I use explain analyze it lasts 7 times slower.
Why?   

You've got a machine where gettimeofday() is really slow.  This is
common on cheap PC hardware :-( 

I'd be curious to know more about the hardware and operating system Szymon is using if you suspect this is the case.  I keep hearing about systems where this is slow, but despite claims that they're common I've never actually seen one.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Greg Smith <greg@2ndquadrant.com> writes:
> Tom Lane wrote:
>> You've got a machine where gettimeofday() is really slow.  This is
>> common on cheap PC hardware :-(

> I'd be curious to know more about the hardware and operating system
> Szymon is using if you suspect this is the case.  I keep hearing about
> systems where this is slow, but despite claims that they're common I've
> never actually seen one.

Well, they're not as common as they used to be.  My understanding is
that there are two independent issues:

* If you have to call into the kernel to read the RTC, you're already
hurting.  Modern Unixen avoid this, but I think I've read that it's
generally only fixed on x86_64 hardware not i386.

* The original specs for reading the RTC on PC hardware did not foresee
the desire of being able to read it out in a small fraction of a
microsecond.  I don't know the details on this exactly, but some
googling turned up this:
http://linux.derkeiler.com/Mailing-Lists/Kernel/2006-07/msg07415.html

The OP's example involved almost 21 seconds added by approximately
2*10000000 gettimeofday probes, or right about 1 microsecond per
probe...

            regards, tom lane

For the record, I've recently observed such behaviour on non-cheap
64bit server harware.

That was Pg 8.4.0. hardware specs available on request.

EXPLAIN ANALYZE SELECT was over 2 times slower that SELECT. repeatedly.

Answering an obligatory question: NO virtualization (vmware/xen/other) there.

Question:
Is there anything as normal, accepted level of performance degradation
when using EXPLAIN ANALYZE compared to plain query?




2010/3/22 Tom Lane <tgl@sss.pgh.pa.us>:
> Szymon Guz <mabewlun@gmail.com> writes:
>> I've got a simple query. When I use explain analyze it lasts 7 times slower.
>> Why?
>
> You've got a machine where gettimeofday() is really slow.  This is
> common on cheap PC hardware :-(
>
>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

=?UTF-8?Q?Filip_Rembia=C5=82kowski?= <plk.zuber@gmail.com> writes:
> For the record, I've recently observed such behaviour on non-cheap
> 64bit server harware.

> That was Pg 8.4.0. hardware specs available on request.

> EXPLAIN ANALYZE SELECT was over 2 times slower that SELECT. repeatedly.

> Answering an obligatory question: NO virtualization (vmware/xen/other) there.

> Question:
> Is there anything as normal, accepted level of performance degradation
> when using EXPLAIN ANALYZE compared to plain query?

You should certainly not expect it to be free, if that's what you mean.
2X penalty on a very cheap plan node (such as a seqscan with no filter)
doesn't surprise me much.

BTW, it occurs to me that gettimeofday's microsecond resolution doesn't
really get the job done anymore for such cheap plan nodes.  I wonder if
we should be trying to use clock_gettime() where available.

            regards, tom lane