Estimated rows question

From: Sam Ross <elliptic(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Estimated rows question
Date: 2012-08-21 06:27:09
Message-ID: CAG+An1qwrWwQ_J5x3Vhdi6HU4Otc+bqT91cMfq09hJRo9yKy5Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2012-08-21 07:01:00 Re: How hard would a "path" operator be to implement in PostgreSQL
Previous Message Thom Brown 2012-08-21 06:24:13 Re: Grant problem and how to prevent users to execute OS commands?

Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2012-08-21 06:31:29 Raise a WARNING if a REVOKE affects nothing?
Previous Message Craig Ringer 2012-08-21 05:22:55 Re: temporal support patch