On Sat, Jun 8, 2013 at 8:06 AM, Enrico Pirozzi <sscott...@gmail.com> wrote:
> Hi all, > > I have 2 tables: > > table1 (field1,field2,.....) > table2 (field1,field2,field3,.....) > > field1 is an uuid type > Is it a primary key? Is there a foreign key constraint between them? > field2 is a timestamp with time zone type. > > If I execute: > > explain (analyze on, timing off) > select B.field3,A.field1,A.field2 > FROM table1 A INNER JOIN table2 B > using (field1,field2) > > the query plan is > > > QUERY > PLAN > > > ---------------------------------------------------------------------------------------------------------------- > Hash Join (cost=137324.20..247200.77 rows=1 width=31) (actual > rows=1136175 loops=1) > Hash Cond: ((b.field1 = a.field1) AND (b.field2 = a.field2)) > -> Seq Scan on table2 b (cost=0.00..49694.75 rows=1136175 width=31) > (actual rows=1136175 loops > =1) > -> Hash (cost=89610.68..89610.68 rows=2287368 width=24) (actual > rows=1143684 loops=1) > Buckets: 65536 Batches: 4 Memory Usage: 15699kB > -> Seq Scan on table1 a (cost=0.00..89610.68 rows=2287368 > width=24) (actual rows=1143684 loops=1 > ) > Total runtime: 5055.118 ms > (7 rows) > > My question is > Why Have I a rows=1 in the explain and rows=1136175 in the explain analyze? > Yes, that seems quite strange. Did you analyze the tables (not explain analyze, but analyze itself)? > > I tried to tune Planner Cost Constants as > > default_statistics_target (integer) > Did you re-analyze the tables after changing that? and / or > > from_collapse_limit (integer) > join_collapse_limit (integer) > I would not expect those to matter much for your query. Cheers, Jeff
