pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCalculating statistic via function rather than with query is slowing my query
8+ messages / 3 participants
[nested] [flat]
* Calculating statistic via function rather than with query is slowing my query
@ 2011-08-17 18:20 Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Anish Kejariwal @ 2011-08-17 18:20 UTC (permalink / raw)
To: pgsql-performance
Hi everyone,
I'm using postgres 9.0.3, and here's the OS I'm running this on:
Linux 2.6.18-238.12.1.el5xen #1 SMP Tue May 31 14:02:29 EDT 2011 x86_64
x86_64 x86_64 GNU/Linux
I have a fairly straight forward query. I'm doing a group by on an ID, and
then calculating some a statistic on the resulting data. The problem I'm
running into is that when I'm calculating the statistics via a function,
it's twice as slow as when I'm calculating the statistics directly in my
query. I want to be able to use a function, since I'll be using this
particular calculation in many places.
Any idea of what's going on? Below, I've included my function, and both
queries (I removed the type_ids, and just wrote …ids…
Here's my function (I also tried stable):
CREATE OR REPLACE FUNCTION calc_test(a double precision, b integer, c
integer)
RETURNS double precision AS $$
BEGIN
return a/b/c* 1000000000::double precision;
END;
$$ LANGUAGE plpgsql immutable;
The query that takes 7.6 seconds, when I calculate the statistic from within
the query:
explain analyze
select
agg.primary_id,
avg(agg.a / agg.b / agg.c * 1000000000::double precision) foo,
stddev(agg.a / agg.b / agg.c * 1000000000::double precision) bar
from mytable agg
where agg.type_id in (....ids....)
group by agg.primary_id;
The execution plan:
HashAggregate (cost=350380.58..350776.10 rows=9888 width=20) (actual
time=7300.414..7331.659 rows=20993 loops=1)
-> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
rows=1716127 width=20) (actual time=200.064..2861.600 rows=2309230 loops=1)
Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
-> Bitmap Index Scan on mytable_type_id_idx (cost=0.00..28238.87
rows=1716127 width=0) (actual time=192.725..192.725 rows=2309230 loops=1)
Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
Total runtime: 7358.337 ms
(6 rows)
The same query, but now I'm calling the function. When I call the function
it's taking 15.5 seconds.
explain analyze select
agg.primary_id,
avg(calc_test(agg.a,agg.b,agg.c)) foo,
stddev(calc_test(agg.a,agg.b,agg.c)) bar
from mytable agg
where agg.type_id in (....ids....)
group by agg.primary_id;
and, here's the execution plan:
HashAggregate (cost=350380.58..355472.90 rows=9888 width=20) (actual
time=13660.838..13686.618 rows=20993 loops=1)
-> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
rows=1716127 width=20) (actual time=170.385..2881.122 rows=2309230 loops=1)
Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
-> Bitmap Index Scan on mytable_type_id_idx (cost=0.00..28238.87
rows=1716127 width=0) (actual time=162.834..162.834 rows=2309230 loops=1)
Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
Total runtime: 13707.560 ms
Thanks!
Anish
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Calculating statistic via function rather than with query is slowing my query
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
@ 2011-08-17 18:27 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-08-17 19:00 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2011-08-17 18:27 UTC (permalink / raw)
To: Anish Kejariwal <anishkej@gmail.com>; +Cc: pgsql-performance
Hello
2011/8/17 Anish Kejariwal <anishkej@gmail.com>:
> Hi everyone,
> I'm using postgres 9.0.3, and here's the OS I'm running this on:
> Linux 2.6.18-238.12.1.el5xen #1 SMP Tue May 31 14:02:29 EDT 2011 x86_64
> x86_64 x86_64 GNU/Linux
> I have a fairly straight forward query. I'm doing a group by on an ID, and
> then calculating some a statistic on the resulting data. The problem I'm
> running into is that when I'm calculating the statistics via a function,
> it's twice as slow as when I'm calculating the statistics directly in my
> query. I want to be able to use a function, since I'll be using this
> particular calculation in many places.
> Any idea of what's going on? Below, I've included my function, and both
> queries (I removed the type_ids, and just wrote …ids…
> Here's my function (I also tried stable):
> CREATE OR REPLACE FUNCTION calc_test(a double precision, b integer, c
> integer)
> RETURNS double precision AS $$
> BEGIN
> return a/b/c* 1000000000::double precision;
> END;
> $$ LANGUAGE plpgsql immutable;
>
this is overhead of plpgsql call. For this simple functions use a SQL
functions instead
CREATE OR REPLACE FUNCTION calc_test(a double precision, b integer, c
integer)
RETURNS double precision AS $$
> SELECT $1/$2/$3* 1000000000::double precision;
> $$ LANGUAGE sql;
Regards
Pavel Stehule
> The query that takes 7.6 seconds, when I calculate the statistic from within
> the query:
> explain analyze
> select
> agg.primary_id,
> avg(agg.a / agg.b / agg.c * 1000000000::double precision) foo,
> stddev(agg.a / agg.b / agg.c * 1000000000::double precision) bar
> from mytable agg
> where agg.type_id in (....ids....)
> group by agg.primary_id;
> The execution plan:
> HashAggregate (cost=350380.58..350776.10 rows=9888 width=20) (actual
> time=7300.414..7331.659 rows=20993 loops=1)
> -> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
> rows=1716127 width=20) (actual time=200.064..2861.600 rows=2309230 loops=1)
> Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
> -> Bitmap Index Scan on mytable_type_id_idx (cost=0.00..28238.87
> rows=1716127 width=0) (actual time=192.725..192.725 rows=2309230 loops=1)
> Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
> Total runtime: 7358.337 ms
> (6 rows)
>
>
>
> The same query, but now I'm calling the function. When I call the function
> it's taking 15.5 seconds.
> explain analyze select
> agg.primary_id,
> avg(calc_test(agg.a,agg.b,agg.c)) foo,
> stddev(calc_test(agg.a,agg.b,agg.c)) bar
> from mytable agg
> where agg.type_id in (....ids....)
> group by agg.primary_id;
> and, here's the execution plan:
> HashAggregate (cost=350380.58..355472.90 rows=9888 width=20) (actual
> time=13660.838..13686.618 rows=20993 loops=1)
> -> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
> rows=1716127 width=20) (actual time=170.385..2881.122 rows=2309230 loops=1)
> Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
> -> Bitmap Index Scan on mytable_type_id_idx (cost=0.00..28238.87
> rows=1716127 width=0) (actual time=162.834..162.834 rows=2309230 loops=1)
> Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
> Total runtime: 13707.560 ms
>
> Thanks!
> Anish
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Calculating statistic via function rather than with query is slowing my query
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
@ 2011-08-17 19:00 ` Anish Kejariwal <anishkej@gmail.com>
2011-08-17 19:08 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
2011-08-18 00:05 ` Re: Calculating statistic via function rather than with query is slowing my query Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 2 replies; 8+ messages in thread
From: Anish Kejariwal @ 2011-08-17 19:00 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
Thanks Pavel! that definitely solved it.
Unfortunately, the function I gave you was a simple/short version of what
the actual function is going to be. The actual function is going to get
parameters passed to it, and based on the parameters will go through some
if...else conditions, and maybe even call another function. Based on that,
I was definitely hoping to use plpgsql, and the overhead is unfortunate.
Is there any way to get around this overhead? Will I still have the same
overhead if I use plperl, plpython, pljava, or write the function in C?
Anish
On Wed, Aug 17, 2011 at 11:27 AM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
> Hello
>
> 2011/8/17 Anish Kejariwal <anishkej@gmail.com>:
> > Hi everyone,
> > I'm using postgres 9.0.3, and here's the OS I'm running this on:
> > Linux 2.6.18-238.12.1.el5xen #1 SMP Tue May 31 14:02:29 EDT 2011 x86_64
> > x86_64 x86_64 GNU/Linux
> > I have a fairly straight forward query. I'm doing a group by on an ID,
> and
> > then calculating some a statistic on the resulting data. The problem I'm
> > running into is that when I'm calculating the statistics via a function,
> > it's twice as slow as when I'm calculating the statistics directly in my
> > query. I want to be able to use a function, since I'll be using this
> > particular calculation in many places.
> > Any idea of what's going on? Below, I've included my function, and both
> > queries (I removed the type_ids, and just wrote …ids…
> > Here's my function (I also tried stable):
> > CREATE OR REPLACE FUNCTION calc_test(a double precision, b integer, c
> > integer)
> > RETURNS double precision AS $$
> > BEGIN
> > return a/b/c* 1000000000::double precision;
> > END;
> > $$ LANGUAGE plpgsql immutable;
> >
>
> this is overhead of plpgsql call. For this simple functions use a SQL
> functions instead
>
> CREATE OR REPLACE FUNCTION calc_test(a double precision, b integer, c
> integer)
> RETURNS double precision AS $$
> > SELECT $1/$2/$3* 1000000000::double precision;
> > $$ LANGUAGE sql;
>
> Regards
>
> Pavel Stehule
>
> > The query that takes 7.6 seconds, when I calculate the statistic from
> within
> > the query:
> > explain analyze
> > select
> > agg.primary_id,
> > avg(agg.a / agg.b / agg.c * 1000000000::double precision) foo,
> > stddev(agg.a / agg.b / agg.c * 1000000000::double precision) bar
> > from mytable agg
> > where agg.type_id in (....ids....)
> > group by agg.primary_id;
> > The execution plan:
> > HashAggregate (cost=350380.58..350776.10 rows=9888 width=20) (actual
> > time=7300.414..7331.659 rows=20993 loops=1)
> > -> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
> > rows=1716127 width=20) (actual time=200.064..2861.600 rows=2309230
> loops=1)
> > Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
> > -> Bitmap Index Scan on mytable_type_id_idx
> (cost=0.00..28238.87
> > rows=1716127 width=0) (actual time=192.725..192.725 rows=2309230 loops=1)
> > Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
> > Total runtime: 7358.337 ms
> > (6 rows)
> >
> >
> >
> > The same query, but now I'm calling the function. When I call the
> function
> > it's taking 15.5 seconds.
> > explain analyze select
> > agg.primary_id,
> > avg(calc_test(agg.a,agg.b,agg.c)) foo,
> > stddev(calc_test(agg.a,agg.b,agg.c)) bar
> > from mytable agg
> > where agg.type_id in (....ids....)
> > group by agg.primary_id;
> > and, here's the execution plan:
> > HashAggregate (cost=350380.58..355472.90 rows=9888 width=20) (actual
> > time=13660.838..13686.618 rows=20993 loops=1)
> > -> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
> > rows=1716127 width=20) (actual time=170.385..2881.122 rows=2309230
> loops=1)
> > Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
> > -> Bitmap Index Scan on mytable_type_id_idx
> (cost=0.00..28238.87
> > rows=1716127 width=0) (actual time=162.834..162.834 rows=2309230 loops=1)
> > Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
> > Total runtime: 13707.560 ms
> >
> > Thanks!
> > Anish
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Calculating statistic via function rather than with query is slowing my query
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
2011-08-17 19:00 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
@ 2011-08-17 19:08 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2011-08-17 19:08 UTC (permalink / raw)
To: Anish Kejariwal <anishkej@gmail.com>; +Cc: pgsql-performance
2011/8/17 Anish Kejariwal <anishkej@gmail.com>:
> Thanks Pavel! that definitely solved it.
> Unfortunately, the function I gave you was a simple/short version of what
> the actual function is going to be. The actual function is going to get
> parameters passed to it, and based on the parameters will go through some
> if...else conditions, and maybe even call another function. Based on that,
> I was definitely hoping to use plpgsql, and the overhead is unfortunate.
> Is there any way to get around this overhead? Will I still have the same
> overhead if I use plperl, plpython, pljava, or write the function in C?
only SQL and C has zero overhead - SQL because uses inlining and C is
just readable assambler.
I am thinking, overhead of PL/pgSQL is minimal from languages from your list.
Regards
Pavel
>
> Anish
>
> On Wed, Aug 17, 2011 at 11:27 AM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> Hello
>>
>> 2011/8/17 Anish Kejariwal <anishkej@gmail.com>:
>> > Hi everyone,
>> > I'm using postgres 9.0.3, and here's the OS I'm running this on:
>> > Linux 2.6.18-238.12.1.el5xen #1 SMP Tue May 31 14:02:29 EDT 2011 x86_64
>> > x86_64 x86_64 GNU/Linux
>> > I have a fairly straight forward query. I'm doing a group by on an ID,
>> > and
>> > then calculating some a statistic on the resulting data. The problem
>> > I'm
>> > running into is that when I'm calculating the statistics via a function,
>> > it's twice as slow as when I'm calculating the statistics directly in my
>> > query. I want to be able to use a function, since I'll be using this
>> > particular calculation in many places.
>> > Any idea of what's going on? Below, I've included my function, and both
>> > queries (I removed the type_ids, and just wrote …ids…
>> > Here's my function (I also tried stable):
>> > CREATE OR REPLACE FUNCTION calc_test(a double precision, b integer, c
>> > integer)
>> > RETURNS double precision AS $$
>> > BEGIN
>> > return a/b/c* 1000000000::double precision;
>> > END;
>> > $$ LANGUAGE plpgsql immutable;
>> >
>>
>> this is overhead of plpgsql call. For this simple functions use a SQL
>> functions instead
>>
>> CREATE OR REPLACE FUNCTION calc_test(a double precision, b integer, c
>> integer)
>> RETURNS double precision AS $$
>> > SELECT $1/$2/$3* 1000000000::double precision;
>> > $$ LANGUAGE sql;
>>
>> Regards
>>
>> Pavel Stehule
>>
>> > The query that takes 7.6 seconds, when I calculate the statistic from
>> > within
>> > the query:
>> > explain analyze
>> > select
>> > agg.primary_id,
>> > avg(agg.a / agg.b / agg.c * 1000000000::double precision) foo,
>> > stddev(agg.a / agg.b / agg.c * 1000000000::double precision) bar
>> > from mytable agg
>> > where agg.type_id in (....ids....)
>> > group by agg.primary_id;
>> > The execution plan:
>> > HashAggregate (cost=350380.58..350776.10 rows=9888 width=20) (actual
>> > time=7300.414..7331.659 rows=20993 loops=1)
>> > -> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
>> > rows=1716127 width=20) (actual time=200.064..2861.600 rows=2309230
>> > loops=1)
>> > Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
>> > -> Bitmap Index Scan on mytable_type_id_idx
>> > (cost=0.00..28238.87
>> > rows=1716127 width=0) (actual time=192.725..192.725 rows=2309230
>> > loops=1)
>> > Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
>> > Total runtime: 7358.337 ms
>> > (6 rows)
>> >
>> >
>> >
>> > The same query, but now I'm calling the function. When I call the
>> > function
>> > it's taking 15.5 seconds.
>> > explain analyze select
>> > agg.primary_id,
>> > avg(calc_test(agg.a,agg.b,agg.c)) foo,
>> > stddev(calc_test(agg.a,agg.b,agg.c)) bar
>> > from mytable agg
>> > where agg.type_id in (....ids....)
>> > group by agg.primary_id;
>> > and, here's the execution plan:
>> > HashAggregate (cost=350380.58..355472.90 rows=9888 width=20) (actual
>> > time=13660.838..13686.618 rows=20993 loops=1)
>> > -> Bitmap Heap Scan on mytable agg (cost=28667.90..337509.63
>> > rows=1716127 width=20) (actual time=170.385..2881.122 rows=2309230
>> > loops=1)
>> > Recheck Cond: (type_id = ANY ('{....ids....}'::integer[]))
>> > -> Bitmap Index Scan on mytable_type_id_idx
>> > (cost=0.00..28238.87
>> > rows=1716127 width=0) (actual time=162.834..162.834 rows=2309230
>> > loops=1)
>> > Index Cond: (type_id = ANY ('{....ids....}'::integer[]))
>> > Total runtime: 13707.560 ms
>> >
>> > Thanks!
>> > Anish
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Calculating statistic via function rather than with query is slowing my query
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
2011-08-17 19:00 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
@ 2011-08-18 00:05 ` Craig Ringer <ringerc@ringerc.id.au>
2011-08-18 01:03 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Craig Ringer @ 2011-08-18 00:05 UTC (permalink / raw)
To: Anish Kejariwal <anishkej@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
On 18/08/2011 3:00 AM, Anish Kejariwal wrote:
> Thanks Pavel! that definitely solved it.
>
> Unfortunately, the function I gave you was a simple/short version of
> what the actual function is going to be. The actual function is going
> to get parameters passed to it, and based on the parameters will go
> through some if...else conditions, and maybe even call another
> function. Based on that, I was definitely hoping to use plpgsql, and
> the overhead is unfortunate.
>
> Is there any way to get around this overhead? Will I still have the
> same overhead if I use plperl, plpython, pljava, or write the function
> in C?
You can probably still write it as an SQL function if you use CASE WHEN
appropriately.
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Calculating statistic via function rather than with query is slowing my query
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
2011-08-17 19:00 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-18 00:05 ` Re: Calculating statistic via function rather than with query is slowing my query Craig Ringer <ringerc@ringerc.id.au>
@ 2011-08-18 01:03 ` Anish Kejariwal <anishkej@gmail.com>
2011-08-18 05:32 ` Re: Calculating statistic via function rather than with query is slowing my query Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 8+ messages in thread
From: Anish Kejariwal @ 2011-08-18 01:03 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
Thanks for the help Pavel and Craig. I really appreciate it. I'm going to
try a couple of these different options (write a c function, use a sql
function with case statements, and use plperl), so I can see which gives me
the realtime performance that I need, and works best for clean code in my
particular case.
thanks!
Anish
On Wed, Aug 17, 2011 at 5:05 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 18/08/2011 3:00 AM, Anish Kejariwal wrote:
>
> Thanks Pavel! that definitely solved it.
>
> Unfortunately, the function I gave you was a simple/short version of what
> the actual function is going to be. The actual function is going to get
> parameters passed to it, and based on the parameters will go through some
> if...else conditions, and maybe even call another function. Based on that,
> I was definitely hoping to use plpgsql, and the overhead is unfortunate.
>
> Is there any way to get around this overhead? Will I still have the same
> overhead if I use plperl, plpython, pljava, or write the function in C?
>
>
> You can probably still write it as an SQL function if you use CASE WHEN
> appropriately.
>
> --
> Craig Ringer
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Calculating statistic via function rather than with query is slowing my query
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
2011-08-17 19:00 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-18 00:05 ` Re: Calculating statistic via function rather than with query is slowing my query Craig Ringer <ringerc@ringerc.id.au>
2011-08-18 01:03 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
@ 2011-08-18 05:32 ` Craig Ringer <ringerc@ringerc.id.au>
2011-08-18 18:46 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Craig Ringer @ 2011-08-18 05:32 UTC (permalink / raw)
To: Anish Kejariwal <anishkej@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
On 18/08/2011 9:03 AM, Anish Kejariwal wrote:
> Thanks for the help Pavel and Craig. I really appreciate it. I'm
> going to try a couple of these different options (write a c function,
> use a sql function with case statements, and use plperl), so I can see
> which gives me the realtime performance that I need, and works best
> for clean code in my particular case.
Do you really mean "realtime"? Or just "fast"?
If you have strongly bounded latency requirements, any SQL-based,
disk-based system is probably not for you. Especially not one that
relies on a statics-based query planner, caching, and periodic
checkpoints. I'd be looking into in-memory databases designed for
realtime environments where latency is critical.
Hard realtime: If this system fails to respond within <x> milliseconds,
all the time, every time, then something will go "smash" or "boom"
expensively and unrecoverably.
Soft realtime: If this system responds late, the late response is
expensive or less useful. Frequent late responses are unacceptable but
the occasional one might be endurable.
Just needs to be fast: If it responds late, the user gets irritated
because they're sitting and waiting for a response. Regular long stalls
are unacceptable, but otherwise the user can put up with it. You're more
concerned with average latency than maximum latency.
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Calculating statistic via function rather than with query is slowing my query
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Re: Calculating statistic via function rather than with query is slowing my query Pavel Stehule <pavel.stehule@gmail.com>
2011-08-17 19:00 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-18 00:05 ` Re: Calculating statistic via function rather than with query is slowing my query Craig Ringer <ringerc@ringerc.id.au>
2011-08-18 01:03 ` Re: Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-18 05:32 ` Re: Calculating statistic via function rather than with query is slowing my query Craig Ringer <ringerc@ringerc.id.au>
@ 2011-08-18 18:46 ` Anish Kejariwal <anishkej@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Anish Kejariwal @ 2011-08-18 18:46 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
Hi Craig,
Fair point. For now, I mean "just fast" - which is 5-15 seconds, but I'd
like to get it down to the 1-2 second range.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-08-18 18:46 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-17 18:20 Calculating statistic via function rather than with query is slowing my query Anish Kejariwal <anishkej@gmail.com>
2011-08-17 18:27 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-08-17 19:00 ` Anish Kejariwal <anishkej@gmail.com>
2011-08-17 19:08 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-08-18 00:05 ` Craig Ringer <ringerc@ringerc.id.au>
2011-08-18 01:03 ` Anish Kejariwal <anishkej@gmail.com>
2011-08-18 05:32 ` Craig Ringer <ringerc@ringerc.id.au>
2011-08-18 18:46 ` Anish Kejariwal <anishkej@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox