Re: Problem with planner - Mailing list pgsql-general

From hubert depesz lubaczewski
Subject Re: Problem with planner
Date
Msg-id 20110808165317.GA23581@depesz.com
Whole thread Raw
In response to Re: Problem with planner  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Mon, Aug 08, 2011 at 12:51:40PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.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/

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Problem with planner
Next
From: Merlin Moncure
Date:
Subject: Re: table / query as a prameter for PL/pgSQL function