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

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

> Joe Van Dyk <joe(at)tanga(dot)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)