2009/7/18 Hitoshi Harada umi.tan...@gmail.com:
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
2009/7/17 Pavel Stehule pavel.steh...@gmail.com:
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
Pavel Stehule pavel.steh...@gmail.com 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
2009/7/17 Kevin Grittner kevin.gritt...@wicourts.gov:
Pavel Stehule pavel.steh...@gmail.com 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
2009/7/17 Hitoshi Harada umi.tan...@gmail.com:
2009/7/17 Pavel Stehule pavel.steh...@gmail.com:
Hello
look on:
postgres=# explain select count(*) over () from x;
QUERY PLAN
-
WindowAgg (cost=0.00..265.00
Pavel Stehule pavel.steh...@gmail.com 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
2009/7/18 Kevin Grittner kevin.gritt...@wicourts.gov:
Pavel Stehule pavel.steh...@gmail.com 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.
On Wed, Jul 15, 2009 at 11:18 AM, Pavel Stehulepavel.steh...@gmail.com 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:
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(*)
2009/7/16 Greg Stark gsst...@mit.edu:
On Wed, Jul 15, 2009 at 11:18 AM, Pavel Stehulepavel.steh...@gmail.com
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
2009/7/16 Pavel Stehule pavel.steh...@gmail.com:
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
2009/7/16 Hitoshi Harada umi.tan...@gmail.com:
2009/7/16 Greg Stark gsst...@mit.edu:
On Wed, Jul 15, 2009 at 11:18 AM, Pavel Stehulepavel.steh...@gmail.com
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
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
Hello,
I did some test - median via window function - I found probably some
bad optimised code. I found two methods - Celko and Itzik Ben-Gan.
Ben-Gan methoud should to be faster - there is one sort less, but in
practice - it is 2 times slower.
create table x(a integer);
insert into x select
14 matches
Mail list logo
