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>
