Postgres Pro
Downloads
Home   >   mailing lists

Re: query performance, though it was timestamps,maybe just table size? - Mailing list pgsql-general

From Henry Drexler
Subject Re: query performance, though it was timestamps,maybe just table size?
Date
Msg-id CAAtgU9RYrRC4BAPig5Lr+S_B1FNXynT_EtASA=7sDWk0eAnaRg@mail.gmail.com
Whole thread Raw
In response to Re: query performance, though it was timestamps,maybe just table size?  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
Tree view
On Sun, Dec 2, 2012 at 12:44 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
Could you do it for the recursive
SQL (the one inside the function) like you had previously done for the
regular explain?

Cheers,

Jeff

Here they are:

for the 65 million row table:
"Index Scan using ctn_source on massive  (cost=0.00..189.38 rows=1 width=28) (actual time=85.802..85.806 rows=1 loops=1)"
"  Index Cond: (customer_id = <some customer id>::bigint)"
"  Filter: (dateof <@ '["2012-07-03 14:00:00","2012-07-10 14:00:00"]'::tsrange)"
"  Buffers: shared read=6"
"Total runtime: 85.891 ms"



for the 30 million row table:
"Index Scan using ctn_dateof on massive  (cost=0.00..80.24 rows=1 width=24) (actual time=0.018..0.020 rows=1 loops=1)"
"  Index Cond: (customer_id = <some customer id>::bigint)"
"  Filter: (dateof <@ '[2012-07-03,2012-07-11)'::
daterange)"
"  Buffers: shared hit=5"
"Total runtime: 0.046 ms"


Thank you.


On Sun, Dec 2, 2012 at 12:44 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Fri, Nov 30, 2012 at 12:22 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
> On Fri, Nov 30, 2012 at 1:42 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>
>> Can you report the EXPLAIN (ANALYZE, BUFFERS) instead?
>
>
> Thanks, here they are:
>
> for the approx 65 million row approx 50 min version:
>
> EXPLAIN (ANALYZE, BUFFERS)
> select
> massive_expansion(ctn,the_range)
> from
> critical_visitors;
>
> "Seq Scan on critical_visitors  (cost=0.00..168722.28 rows=628778 width=40)
> (actual time=0.655..3003921.066 rows=628778 loops=1)"
> "  Buffers: shared hit=4513040 read=1591722 dirtied=5234 written=10"
> "Total runtime: 3004478.053 ms"
>
>
> for the approx 30 million row approx 4 min version:
>
> EXPLAIN (ANALYZE, BUFFERS)
> select
> massive_expansion(ctn,the_range)
> from
> critical_visitors;
>
> "Seq Scan on critical_visitors  (cost=0.00..746587.90 rows=2782315 width=40)
> (actual time=393.001..277108.379 rows=2782315 loops=1)"
> "  Buffers: shared hit=26370078 read=400301 dirtied=33772 written=1030"
> "Total runtime: 278988.544 ms"
>

I can't much sense out of those.  Could you do it for the recursive
SQL (the one inside the function) like you had previously done for the
regular explain?

Cheers,

Jeff

pgsql-general by date:

Previous
From: David Fetter
Date:
Subject: Re: MODERATOR WARNING Re: Exception Handling in C-Language Functions?
Next
From: Merlin Moncure
Date:
Subject: Re: High SYS CPU - need advise