Bruce Momjian wrote:
> I am timing small queries, and found that a PREPARE/EXECUTE of "SELECT
> 1" takes about 1.2ms on my machine.  A normal SELECT doesn't take much
> longer, so I am wondering why a simpler query isn't faster.
> 
> Looking at log_executor_stats, I see the following.  Execute shows
> nothing taking much time, mostly < .2ms, but the total seems high.  I
> wonder if one of our standard query start/stop functions is taking too
> long and can be optimized.

There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case
conditions which limits the amount of queries you can fire off in 1
second, no matter how simple.  In certain rare cases this is something
of a bottleneck.  In my personal case it would be nice to see that time
lower because converted COBOL applications tend to generate a lot of
'simple' queries.

Merlin

Wed, 10 Mar 2004 10:00:55 -0500
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: Timing of 'SELECT 1'
Merlin Moncure wrote:
> Bruce Momjian wrote:
> > I am timing small queries, and found that a PREPARE/EXECUTE of "SELECT
> > 1" takes about 1.2ms on my machine.  A normal SELECT doesn't take much
> > longer, so I am wondering why a simpler query isn't faster.
> > 
> > Looking at log_executor_stats, I see the following.  Execute shows
> > nothing taking much time, mostly < .2ms, but the total seems high.  I
> > wonder if one of our standard query start/stop functions is taking too
> > long and can be optimized.
> 
> There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case
> conditions which limits the amount of queries you can fire off in 1
> second, no matter how simple.  In certain rare cases this is something
> of a bottleneck.  In my personal case it would be nice to see that time
> lower because converted COBOL applications tend to generate a lot of
> 'simple' queries.

Yes, most of that might be network time.  I am using log_duration, which
I think just tests backend time, not network transfer time, but I might
be wrong.  I want to look into this as it seems no one knows the answer.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 10 Mar 2004 11:42:57 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Timing of 'SELECT 1'
Bruce Momjian wrote:

>>There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case
>>conditions which limits the amount of queries you can fire off in 1
>>second, no matter how simple.  In certain rare cases this is something
>>of a bottleneck.  In my personal case it would be nice to see that time
>>lower because converted COBOL applications tend to generate a lot of
>>'simple' queries.
>>    
>>
>
>Yes, most of that might be network time.  I am using log_duration, which
>I think just tests backend time, not network transfer time, but I might
>be wrong.  I want to look into this as it seems no one knows the answer.
>
>  
>
That's easy to verify with standard ping. In my switched 100MBit 
network, roundtrip for small packets is about 0.2 ms, and 0.5ms for 1kb 
packets. How about context switch latency?

Regards,
Andreas




Wed, 10 Mar 2004 20:06:38 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Timing of 'SELECT 1'
Andreas Pflug wrote:
> Bruce Momjian wrote:
> 
> >>There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case
> >>conditions which limits the amount of queries you can fire off in 1
> >>second, no matter how simple.  In certain rare cases this is something
> >>of a bottleneck.  In my personal case it would be nice to see that time
> >>lower because converted COBOL applications tend to generate a lot of
> >>'simple' queries.
> >>    
> >>
> >
> >Yes, most of that might be network time.  I am using log_duration, which
> >I think just tests backend time, not network transfer time, but I might
> >be wrong.  I want to look into this as it seems no one knows the answer.
> >
> >  
> >
> That's easy to verify with standard ping. In my switched 100MBit 
> network, roundtrip for small packets is about 0.2 ms, and 0.5ms for 1kb 
> packets. How about context switch latency?

I am on a dual Xeon. I wouldn't think there was that much of a context
switch overhead, except for kernel calls.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 10 Mar 2004 14:18:17 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Timing of 'SELECT 1'
Merlin Moncure kirjutas K, 10.03.2004 kell 17:00:
> Bruce Momjian wrote:
> > I am timing small queries, and found that a PREPARE/EXECUTE of "SELECT
> > 1" takes about 1.2ms on my machine.  A normal SELECT doesn't take much
> > longer, so I am wondering why a simpler query isn't faster.
> > 
> > Looking at log_executor_stats, I see the following.  Execute shows
> > nothing taking much time, mostly < .2ms, but the total seems high.  I
> > wonder if one of our standard query start/stop functions is taking too
> > long and can be optimized.
> 
> There seems to be a 'PostgreSQL ping' time of about 1-2 ms in best case
> conditions which limits the amount of queries you can fire off in 1
> second, no matter how simple.

Have you checked if this is a per-backend or per-connection limit?  

> In certain rare cases this is something
> of a bottleneck.  In my personal case it would be nice to see that time
> lower because converted COBOL applications tend to generate a lot of
> 'simple' queries.
> 
> Merlin
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html

Wed, 10 Mar 2004 22:36:50 +0200
Hannu Krosing <hannu@tm.ee>
Re: Timing of 'SELECT 1'