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
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 (ran
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
>>> 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
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 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 ind
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
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 materialize
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
CTM&D Business Unit
972-52-4286-513
[EMAIL PROTECTED]
---(end of broadcast)-
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
> >
>
>> 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 @@
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 bod
24 matches
Mail list logo
