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
On Wed, Jan 2, 2008 at 9:29 AM, in message
[EMAIL PROTECTED], Abraham,
Danny [EMAIL PROTECTED] wrote:
We are looking for a patch that will help us count using the indexes.
As others have mentioned, that's not currently possible for a count
of all rows in a table, because there can be many
Kevin Grittner wrote:
If you really are doing proper maintenance, and you don't need exact
counts, you might be able to use the approximation stored in the
system tables:
Also, if you're using count(*) as an existance test (common in Mysql
code), it's better to use exists instead. Using
Hi,
We are looking for a patch that will help us count using the indexes.
Our product is about 20 times slower on Postgres compared to MS SQL
Server.
Any ideas?
Danny Abraham
BMC Software
CTMD Business Unit
972-52-4286-513
[EMAIL PROTECTED]
---(end of
On 02/01/2008, Abraham, Danny [EMAIL PROTECTED] wrote:
Hi,
We are looking for a patch that will help us count using the indexes.
Our product is about 20 times slower on Postgres compared to MS SQL
Server.
Any ideas?
There isn't any similar patch and will not be.
Use materialized views
am Wed, dem 02.01.2008, um 9:29:24 -0600 mailte Abraham, Danny folgendes:
Hi,
We are looking for a patch that will help us count using the indexes.
Our product is about 20 times slower on Postgres compared to MS SQL
Server.
Any ideas?
Please show us your SQL and the execution plan
On Wed, Jan 02, 2008 at 09:29:24AM -0600, Abraham, Danny wrote:
We are looking for a patch that will help us count using the indexes.
Is this for
SELECT count(*) FROM table;
or
SELECT count(1) FROM table WHERE. . .
The latter _will_ use an index, if the
On Wed, 2008-01-02 at 09:29 -0600, Abraham, Danny wrote:
Our product is about 20 times slower on Postgres compared to MS SQL
Server.
If you want to have a cross-platform product then you must consider how
to access multiple systems both accurately and quickly. Not much point
catering for the
On Fri, 1 Oct 2004, Kris Kiger wrote:
Hey all, its me again. If I do not do a count(product_id) on my
tsearch2 queries, its actually really fast, for example;
Hmm, I also really want to know what's the difference ?
Postgresql 8.0beta3 on Linux 2.4.25
tsearchd=# explain analyze select body
Hey all, its me again. If I do not do a count(product_id) on my
tsearch2 queries, its actually really fast, for example;
Hmm, I also really want to know what's the difference ?
Postgresql 8.0beta3 on Linux 2.4.25
tsearchd=# explain analyze select body from txt where
fts_index @@
Magnus
On Sun, 3 Oct 2004, Magnus Hagander wrote:
Hey all, its me again. If I do not do a count(product_id) on my
tsearch2 queries, its actually really fast, for example;
Hmm, I also really want to know what's the difference ?
Postgresql 8.0beta3 on Linux 2.4.25
tsearchd=#
24 matches
Mail list logo
