Re: Question on explain - Mailing list pgsql-general

From Jeff Janes
Subject Re: Question on explain
Date
Msg-id CAMkU=1zTxVxZe3s6KXAj4EMB-Ue3mV9_DdsfMHcgOzpgLSSpVw@mail.gmail.com
Whole thread Raw
In response to Question on explain  (Enrico Pirozzi <sscotty71@gmail.com>)
Responses Re: Question on explain  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
On Sat, Jun 8, 2013 at 8:06 AM, Enrico Pirozzi <sscotty71@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

pgsql-general by date:

Previous
From: Vincent Veyron
Date:
Subject: Re: Open bytea files (images, docs, excel, PDF) stored in Postgresql using a Microsoft Access form
Next
From: c k
Date:
Subject: Re: compiling postgresql 9.2.4 on fedora 17 64 bit takes very long time