Search Postgresql Archives

Estimated rows question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I was wondering why it seems that the query planner can't "see", based
on the histograms, that two join-columns have a very small
intersection, and adjust its row estimation accordingly.  Clearly the
below query returns 1001 rows.  It appears as if much or all of the
necessary machinery exists in mergejoinscansel, and indeed if you
inspect
  leftstartsel, leftendsel, rightstartsel, rightendsel during execution
they are respectively  0.98, 1.00, 0.00, 0.020, which I believe makes sense.

Am I missing something obvious?
Thanks
Sam

create table table_a as select * from generate_series(1,61000) as pkey;
create table table_b as select * from generate_series(60000,110000) as pkey;
create unique index idx_a on table_a(pkey);
create unique index idx_b on table_b(pkey);
analyse table_a;
analyse table_b;

explain select * from table_a a inner join table_b b on a.pkey = b.pkey;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Merge Join  (cost=1984.88..2550.42 rows=50001 width=8)
   Merge Cond: (a.pkey = b.pkey)
   ->  Index Only Scan using idx_a on table_a a  (cost=0.00..1864.32
rows=61000 width=4)
   ->  Index Only Scan using idx_b on table_b b  (cost=0.00..1531.32
rows=50001 width=4)


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux