Thread: strange

strange

From
Szymon Guz
Date:
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

Re: strange

From
Tom Lane
Date:
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

Re: strange

From
Greg Smith
Date:
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

Re: strange

From
Tom Lane
Date:
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

Re: strange

From
Szymon Guz
Date:
2010/3/22 Greg Smith <greg@2ndquadrant.com>
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.



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

Re: strange

From
Filip Rembiałkowski
Date:
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/

Re: strange

From
Grzegorz Jaśkiewicz
Date:


2010/3/23 Filip Rembiałkowski <plk.zuber@gmail.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.
 

Re: strange

From
Tom Lane
Date:
=?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