2009/7/17 Kevin Grittner <[email protected]>:
> Pavel Stehule <[email protected]> wrote:
>
>> postgres=# explain select count(*) over () from x;
>
>> WindowAgg (cost=0.00..265.00 rows=10000 width=0)
>> -> Seq Scan on x (cost=0.00..140.00 rows=10000 width=0)
>
>> postgres=# explain select count(*) over (order by a) from x;
>
>> WindowAgg (cost=0.00..556.25 rows=10000 width=4)
>> -> Index Scan using gg on x (cost=0.00..406.25 rows=10000
> width=4)
>
>> query1: 160ms
>> query2: 72ms
>
> EXPLAIN ANALYZE is more telling than just EXPLAIN.
Query1
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=931.50..931.51 rows=1 width=4) (actual
time=274.423..274.425 rows=1 loops=1)
-> Subquery Scan p (cost=0.00..931.25 rows=100 width=4) (actual
time=220.220..274.388 rows=2 loops=1)
Filter: ((p.r = ((p.rc + 1) / 2)) OR (p.r = ((p.rc + 2) / 2)))
-> WindowAgg (cost=0.00..681.25 rows=10000 width=4) (actual
time=120.622..247.618 rows=10000 loops=1)
-> WindowAgg (cost=0.00..556.25 rows=10000 width=4)
(actual time=0.088..89.848 rows=10000 loops=1)
-> Index Scan using gg on x (cost=0.00..406.25
rows=10000 width=4) (actual time=0.066..33.962 rows=10000 loops
Total runtime: 274.934 ms
(7 rows)
query2:
postgres=# explain analyze select avg(a) from (select a, row_number()
over (order by a asc) as hi, row_number() over (order by a desc) as lo
from x) s where hi in (lo-1,lo+1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.89..1595.90 rows=1 width=4) (actual
time=215.101..215.103 rows=1 loops=1)
-> Subquery Scan s (cost=1220.63..1595.63 rows=100 width=4)
(actual time=175.159..215.073 rows=1 loops=1)
Filter: ((s.hi = (s.lo - 1)) OR (s.hi = (s.lo + 1)))
-> WindowAgg (cost=1220.63..1395.63 rows=10000 width=4)
(actual time=136.985..191.231 rows=10000 loops=1)
-> Sort (cost=1220.63..1245.63 rows=10000 width=4)
(actual time=136.970..151.905 rows=10000 loops=1)
Sort Key: x.a
Sort Method: quicksort Memory: 686kB
-> WindowAgg (cost=0.00..556.25 rows=10000
width=4) (actual time=0.078..106.927 rows=10000 loops=1)
-> Index Scan using gg on x
(cost=0.00..406.25 rows=10000 width=4) (actual time=0.058..33.594
rows=10000
Total runtime: 215.845 ms
(10 rows)
>
> Did you run both several times or flush caches carefully between the
> runs to eliminate caching effects?
yes, - in both variants data was read from cache.
>
> Is it possible that there are a lot of dead rows in the table (from
> UPDATEs or DELETEs), and the table has been vacuumed? (Output from
> VACUUM VERBOSE on the table would show that.)
>
table was filled with random numbers and analyzed - you can simple
check it - look on begin of the thread. This table wasn't updated.
Pavel
> -Kevin
>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
