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

On Apr 7, 2014, at 9:00 PM, bricklen <bricklen@gmail.com> wrote:

>=20
> Does the following query change your plan noticeably?
>=20
> 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;=20
>=20

yes, but it does not give the correct answer - since I want the total =
results for paging purposes.=20
the current workaround (which is similar) I=92ve 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:=20
 Limit  (cost=3D59677.01..59677.13 rows=3D50 width=3D12) (actual =
time=3D58.586..58.593 rows=3D50 loops=3D1)
   ->  Sort  (cost=3D59677.01..59824.72 rows=3D59084 width=3D12) (actual =
time=3D58.585..58.587 rows=3D50 loops=3D1)
         Sort Key: e.received
         Sort Method: top-N heapsort  Memory: 28kB
         ->  WindowAgg  (cost=3D689.51..57714.28 rows=3D59084 width=3D12) =
(actual time=3D36.687..46.940 rows=3D55949 loops=3D1)
               ->  Limit  (cost=3D689.51..56384.89 rows=3D59084 =
width=3D12) (actual time=3D6.571..26.143 rows=3D55949 loops=3D1)
                     ->  Bitmap Heap Scan on edgar e  =
(cost=3D689.51..56384.89 rows=3D59084 width=3D12) (actual =
time=3D6.570..20.912 rows=3D55949 loops=3D1)
                           Recheck Cond: (datefiled > '2014-03-09 =
00:00:00'::timestamp without time zone)
                           ->  Bitmap Index Scan on edgar_datefiled_idx  =
(cost=3D0.00..674.74 rows=3D59084 width=3D0) (actual time=3D6.217..6.217 =
rows=3D55949 loops=3D1)
                                 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@jefftrout.com>