Skip site navigation (1) Skip section navigation (2)

why hash on the primary key?

From: "Robert Haas" <robertmhaas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: why hash on the primary key?
Date: 2008-11-28 19:55:22
Message-ID: 603c8f070811281155u50b1edb9i85e4045677f729e3@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
I'm seeing a lot of plans in my database that look like this:

portal=# explain select * from foo i, foo j where i.id = j.id;
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=769.87..2159.36 rows=13283 width=264)
   Hash Cond: (i.id = j.id)
   ->  Seq Scan on foo i  (cost=0.00..343.83 rows=13283 width=132)
   ->  Hash  (cost=343.83..343.83 rows=13283 width=132)
         ->  Seq Scan on foo j  (cost=0.00..343.83 rows=13283 width=132)

It seems very strange for the planner to decide to build an in-memory
hash table on a column that is already indexed (the primary key, no
less!).  But this is happening A LOT - I often see plans where a
majority of the joins are executed this way (and they're not all
self-joins either...).  It seems like the planner is concluding that
it's going to need most or all of the pages in the table anyway, and
that building a hash table as it goes is quicker than reading the
index pages in from disk.  On a simple query like the above, setting
enable_seqscan to off or random_page_cost to 1 generates the expected
plan:

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Merge Join  (cost=0.00..2534.24 rows=13283 width=264)
   Merge Cond: (i.id = j.id)
   ->  Index Scan using foo_pkey on foo i  (cost=0.00..1167.50
rows=13283 width=132)
   ->  Index Scan using foo_pkey on foo j  (cost=0.00..1167.50
rows=13283 width=132)
(4 rows)

Experimentation shows this is actually about 25% faster.  But, this is
kind of a blunt instrument, and my attempts to fiddle with various
parameters have not been real succesful in generating better plans for
more complicated examples.

Any suggestions/explanations?

...Robert

Responses

pgsql-general by date

Next:From: Adam RichDate: 2008-11-28 20:04:58
Subject: Re: why hash on the primary key?
Previous:From: Ron MayerDate: 2008-11-28 19:43:31
Subject: Re: Very large tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group