On Sat, Jun 8, 2013 at 8:06 AM, Enrico Pirozzi <sscotty71@xxxxxxxxx> 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
