On Fri, Feb 15, 2013 at 7:43 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Joe Van Dyk <joe@xxxxxxxxx> writes:Try without the useless "is true" bits.
> Perhaps I fat-fingered something somewhere... I tried that and I got this:
> https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt
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)
