Thread: 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@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:
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.
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
2010/3/22 Greg Smith <greg@2ndquadrant.com>
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.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 :-(
Hi,
the laptop that I use right now is Compaq 6710b, 4GB RAM, Ubuntu 64bit, kernel from distribution, hdd is new
szymon@ymon:~$ cat /proc/version
Linux version 2.6.31-20-generic (buildd@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@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/
2010/3/23 Filip Rembiałkowski <plk.zuber@gmail.com>
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.
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.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