On Friday, May 10, 2013, Robert Haas wrote: > On Sat, Mar 23, 2013 at 8:12 PM, Misa Simic > <misa.si...@gmail.com<javascript:;>> > wrote: > > but problem is - we don't know the thing id - we know calc_id: > > > > EXPLAIN ANALYZE > > SELECT * FROM t2_left_t3_volatile v INNER JOIN t1 USING (thing_id) > > WHERE calc_id = 20 > > With this query you've got to scan all three tables. The calc_id qual > can only be pushed down into the scan on t1, so you need the whole > t2/t3 join product. > > > EXPLAIN ANALYZE > > SELECT v.no_index FROM t2_left_t3 v INNER JOIN t1 USING (thing_id) > > WHERE calc_id = 20 > > With this query you only need to scan 2 tables. The join between t2 > and t3 is eliminated by the join removal code in favor of scanning > only t2, as shown in the plan you included: > > > "Nested Loop (cost=437.49..13047.74 rows=12111 width=4) (actual > > time=6.360..71.818 rows=12038 loops=1)" > > " -> Seq Scan on t1 (cost=0.00..2.25 rows=1 width=4) (actual > > time=0.016..0.024 rows=1 loops=1)" > > " Filter: (calc_id = 20)" > > " Rows Removed by Filter: 99" > > " -> Bitmap Heap Scan on t2 (cost=437.49..12924.38 rows=12111 > width=12) > > (actual time=6.330..69.063 rows=12038 loops=1)" > > " Recheck Cond: (thing_id = t1.thing_id)" > > " -> Bitmap Index Scan on t5_c2_idx (cost=0.00..434.46 > rows=12111 > > width=0) (actual time=4.372..4.372 rows=12038 loops=1)" > > " Index Cond: (thing_id = t1.thing_id)" > > "Total runtime: 72.461 ms" > > The difference is that this query has only one column in its target list, > not *. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Thanks Robert, That is a bit "old" problem to us... Solution for that kind of problems is: "rephrase" the question. So we have added one more layer to transform input query to "better" query for postgres - very wierd... However, there are no differences... Planer use the same bad plan for: SELECT v.no_index FROM t2_left_t3_volatile v INNER JOIN t1 USING (thing_id) WHERE calc_id = 20 In that is one column as well... We basicaly above query transform to: SELECT v.no_index FROM t2_left_t3_volatile v WHERE v.thing_id = ( SELECT thing_id FROM t1 WHERE calc_id = 20 ) What give us good result... Very wierd.... Thanks, Misa
