Re: strange

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

test_counters=# SELECT COUNT(*), xtype FROM test GROUP BY xtype ORDER BY
xtype;
count | xtype
---------+-------
669000 | A
84000 | B
63000 | D
15000 | E
159000 | G
7866000 | H
1000000 | N
144000 | NI
(8 rows)

Time: 3343,376 ms
test_counters=# EXPLAIN ANALYZE SELECT COUNT(*), xtype FROM test GROUP BY
xtype ORDER BY xtype;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------
Sort (cost=243136.22..243136.24 rows=8 width=2) (actual
time=24306.075..24306.083 rows=8 loops=1)
Sort Key: xtype
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=243136.00..243136.10 rows=8 width=2) (actual
time=24306.030..24306.038 rows=8 loops=1)
-> Seq Scan on test (cost=0.00..193136.00 rows=10000000 width=2)
(actual time=0.013..11365.414 rows=10000000 loops=1)
Total runtime: 24306.173 ms
(6 rows)

Time: 24306,944 ms

regards
Szymon

Szymon Guz <mabewlun(at)gmail(dot)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(at)gmail(dot)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(at)2ndQuadrant(dot)com www.2ndQuadrant.us

Greg Smith <greg(at)2ndquadrant(dot)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

2010/3/22 Greg Smith <greg(at)2ndquadrant(dot)com>

> Tom Lane wrote:
>
> Szymon Guz <mabewlun(at)gmail(dot)com> <mabewlun(at)gmail(dot)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.
>
>
>
Hi,
the laptop that I use right now is Compaq 6710b, 4GB RAM, Ubuntu 64bit,
kernel from distribution, hdd is new

szymon(at)ymon:~$ cat /proc/version
Linux version 2.6.31-20-generic (buildd(at)crested) (gcc version 4.4.1 (Ubuntu
4.4.1-4ubuntu8) ) #58-Ubuntu SMP Fri Mar 12 04:38:19 UTC 2010

regards
Szymon

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(at)sss(dot)pgh(dot)pa(dot)us>:
> Szymon Guz <mabewlun(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/

2010/3/23 Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>

> 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?
>
>
>
Apparently you can force linux kernel to consider different time source, as
it sort of guess-probes which one would be the best when it boots.
I don't remember the exact option, but it is easy to find on the net.

=?UTF-8?Q?Filip_Rembia=C5=82kowski?= <plk(dot)zuber(at)gmail(dot)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