2009/7/18 Hitoshi Harada :
> If I understand exlain analyze correctly and it tells us the fact,
> WindowAgg without ORDER BY clause gets unreasonably slow. Let me see.
>
I haven't determined the difference between with and without ORDER BY
clause in OVER(), but I took a benchmark that throws an in
2009/7/18 Kevin Grittner :
> Pavel Stehule wrote:
>
>> table was filled with random numbers and analyzed - you can simple
>> check it - look on begin of the thread. This table wasn't updated.
>
> Confirmed. The ORDER BY consistently speeds up the query. Odd
>
> Sort speed varied based on ran
Pavel Stehule wrote:
> table was filled with random numbers and analyzed - you can simple
> check it - look on begin of the thread. This table wasn't updated.
Confirmed. The ORDER BY consistently speeds up the query. Odd
Sort speed varied based on random sequence generated, but typical
2009/7/17 Hitoshi Harada :
> 2009/7/17 Pavel Stehule :
>> Hello
>>
>> look on:
>> postgres=# explain select count(*) over () from x;
>> QUERY PLAN
>> -
>> WindowAgg (cost=0.00..265.00 rows=1 width=0)
>> ->
2009/7/17 Kevin Grittner :
> Pavel Stehule wrote:
>
>> postgres=# explain select count(*) over () from x;
>
>> WindowAgg (cost=0.00..265.00 rows=1 width=0)
>> -> Seq Scan on x (cost=0.00..140.00 rows=1 width=0)
>
>> postgres=# explain select count(*) over (order by a) from x;
>
>>
Pavel Stehule wrote:
> postgres=# explain select count(*) over () from x;
> WindowAgg (cost=0.00..265.00 rows=1 width=0)
>-> Seq Scan on x (cost=0.00..140.00 rows=1 width=0)
> postgres=# explain select count(*) over (order by a) from x;
> WindowAgg (cost=0.00..556.25 rows
2009/7/17 Pavel Stehule :
> Hello
>
> look on:
> postgres=# explain select count(*) over () from x;
> QUERY PLAN
> -
> WindowAgg (cost=0.00..265.00 rows=1 width=0)
> -> Seq Scan on x (cost=0.00..140.00 row
Hello
look on:
postgres=# explain select count(*) over () from x;
QUERY PLAN
-
WindowAgg (cost=0.00..265.00 rows=1 width=0)
-> Seq Scan on x (cost=0.00..140.00 rows=1 width=0)
(2 rows)
Time: 1,473
2009/7/16 Hitoshi Harada :
> 2009/7/16 Greg Stark :
>> On Wed, Jul 15, 2009 at 11:18 AM, Pavel Stehule
>> wrote:
>>> postgres=# select avg(a) from (select a, row_number() over (order by
>>> a) as r, count(*) over () as rc from x ) p where r in
>>> ((rc+1)/2,(rc+2)/2) ;
>>
>> How does this compare
2009/7/16 Pavel Stehule :
>> I'm also not sure how to handle this if the set has to be spooled to
>> disk. Quicksort and Quickselect do a lot of scans throught he data and
>> wouldn't perform well on disk.
>
> I thing, so problem is in aggregate func used as window func - or some
> missing optimali
2009/7/16 Greg Stark :
> On Wed, Jul 15, 2009 at 11:18 AM, Pavel Stehule
> wrote:
>> postgres=# select avg(a) from (select a, row_number() over (order by
>> a) as r, count(*) over () as rc from x ) p where r in
>> ((rc+1)/2,(rc+2)/2) ;
>
> How does this compare to the plain non-windowing SQL imple
> I'm also not sure how to handle this if the set has to be spooled to
> disk. Quicksort and Quickselect do a lot of scans throught he data and
> wouldn't perform well on disk.
I thing, so problem is in aggregate func used as window func - or some
missing optimalisation.
when I replaced count(*)
On Wed, Jul 15, 2009 at 11:18 AM, Pavel Stehule wrote:
> postgres=# select avg(a) from (select a, row_number() over (order by
> a) as r, count(*) over () as rc from x ) p where r in
> ((rc+1)/2,(rc+2)/2) ;
How does this compare to the plain non-windowing SQL implementation:
select a from x order
13 matches
Mail list logo
