Re: query performance, though it was timestamps,maybe just table size?

On Sun, Dec 2, 2012 at 12:44 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)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(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Nov 30, 2012 at 12:22 PM, Henry Drexler <alonup8tb(at)gmail(dot)com>
> wrote:
> > On Fri, Nov 30, 2012 at 1:42 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)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
>