Re: BUG #9898: WindowAgg's causing horrific plans

On Apr 7, 2014, at 9:00 PM, bricklen <bricklen(at)gmail(dot)com> wrote:

>
> Does the following query change your plan noticeably?
>
> explain analyze
> select xx, count(*) over ()
> from (
> select xx
> from mytable e
> where
> e.datefiled > current_day() - '30 days'::interval
> order by received desc
> limit 50) y;
>

yes, but it does not give the correct answer - since I want the total results for paging purposes.
the current workaround (which is similar) I’ve been toying with is:

explain analyze
select ss.*, count(*) over ()
from (
select iacc, received
from mytable e
where
e.datefiled > current_day() - '30 days'::interval
offset 0 -- optimization barrier,
) ss
order by received desc
limit 50;

which gives me the plan:
Limit (cost=59677.01..59677.13 rows=50 width=12) (actual time=58.586..58.593 rows=50 loops=1)
-> Sort (cost=59677.01..59824.72 rows=59084 width=12) (actual time=58.585..58.587 rows=50 loops=1)
Sort Key: e.received
Sort Method: top-N heapsort Memory: 28kB
-> WindowAgg (cost=689.51..57714.28 rows=59084 width=12) (actual time=36.687..46.940 rows=55949 loops=1)
-> Limit (cost=689.51..56384.89 rows=59084 width=12) (actual time=6.571..26.143 rows=55949 loops=1)
-> Bitmap Heap Scan on edgar e (cost=689.51..56384.89 rows=59084 width=12) (actual time=6.570..20.912 rows=55949 loops=1)
Recheck Cond: (datefiled > '2014-03-09 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on edgar_datefiled_idx (cost=0.00..674.74 rows=59084 width=0) (actual time=6.217..6.217 rows=55949 loops=1)
Index Cond: (datefiled > '2014-03-09 00:00:00'::timestamp without time zone)
Total runtime: 58.740 ms

which is quite a bit better. trick now is plugging it into reality.

--
Jeff Trout <jeff(at)jefftrout(dot)com>