Re: Problem with planner

On Mon, Aug 08, 2011 at 12:51:40PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> > and we have a query:
> > select count(*) from objects where state='active'::text and ending_tsz <= ( select now() - '1 day'::interval );
>
> Try getting rid of the sub-select. There might have been a reason to do
> it like that ten years ago, but these days it's a guaranteed
> pessimization.

looks like even worse plan:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9168023.89..9168023.90 rows=1 width=0)
-> Bitmap Heap Scan on objects (cost=336429.41..9147670.19 rows=8141478 width=0)
Recheck Cond: (state = 'active'::text)
Filter: (ending_tsz <= (now() - '1 day'::interval))
-> Bitmap Index Scan on objects_stat_user_id_creation_tsz (cost=0.00..334394.04 rows=10064868 width=0)
Index Cond: (state = 'active'::text)
(6 rows)

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/