Re: Avoiding duplication of code via views -- slower? How do people typically do this? - Mailing list pgsql-general

On Fri, Feb 15, 2013 at 7:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Joe Van Dyk <joe@tanga.com> writes:
> > Perhaps I fat-fingered something somewhere... I tried that and I got
> this:
> >
> https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt
>
> Try without the useless "is true" bits.
>
>                         regards, tom lane
>


Huh, that did do the trick. Why does "is true" affect the plan?

without "is true" in the conditions:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16676.66..16676.67 rows=1 width=0) (actual time=
95.648..95.648 rows=1 loops=1)
   ->  Bitmap Heap Scan on promotions p  (cost=868.37..16619.49 rows=22868
width=0) (actual time=11.031..95.294 rows=2720 loops=1)
         Recheck Cond: (end_at > (now() - '30 days'::interval))
         Filter: ((quantity = 1) AND (SubPlan 1))
         Rows Removed by Filter: 43073
         ->  Bitmap Index Scan on index_promotions_on_end_at
 (cost=0.00..862.65 rows=46093 width=0) (actual time=10.783..10.783
rows=73234 loops=1)
               Index Cond: (end_at > (now() - '30 days'::interval))
         SubPlan 1
           ->  Index Only Scan using index_promotion_usages_on_promotion_id
on promotion_usages pu  (cost=0.00..20.54 rows=178 width=0) (actual
time=0.001..0.001 rows=0 loops=44998)
                 Index Cond: (promotion_id = p.id)
                 Heap Fetches: 2720
 Total runtime: 95.739 ms
(12 rows)


with "is true" in the conditions:

          QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=94430.93..94430.94 rows=1 width=0) (actual
time=534.568..534.569 rows=1 loops=1)
   ->  Seq Scan on promotions p  (cost=0.00..94373.76 rows=22868 width=0)
(actual time=0.306..534.165 rows=2720 loops=1)
         Filter: (((quantity = 1) IS TRUE) AND ((end_at > (now() - '30
days'::interval)) IS TRUE) AND ((SubPlan 1) IS TRUE))
         Rows Removed by Filter: 600105
         SubPlan 1
           ->  Index Only Scan using index_promotion_usages_on_promotion_id
on promotion_usages pu  (cost=0.00..20.54 rows=178 width=0) (actual
time=0.001..0.001 rows=0 loops=44998)
                 Index Cond: (promotion_id = p.id)
                 Heap Fetches: 2720
 Total runtime: 534.627 ms
(9 rows)