On Thu, 1 Apr 2010, Robert Haas wrote:
On Thu, Apr 1, 2010 at 2:15 PM, Faheem Mitha <faheem@xxxxxxxxxxxxx> wrote:
I had set the foreign keys in question (on the geno table) to be
primary keys. This is because this setup is basically a glorified
spreadsheet, and I don't want more than one cell corresponding to a
particular tuple of idlink.id and anno.id (the conceptual rows and
cols). Since a primary key defines an index, I thought putting indexes
on idlink_id and anno_id was redundant. However, it looks like
(unsurprisingly) the index corresponding to the primary key is across
both columns, which may not be what is wanted for the aforesaid join
Actually it is what is wanted - that is good.
I see.
So, should I add indexes on the individual foreign key cols idlink_id
and anno_id after all?
I doubt that would help.
You're sure of this?
The bottom line may be that you're dealing with hundreds of millions of
rows here, so things are going to take a long time. Of course you can
always get more/faster memory, a bigger I/O subsystem, faster
processors... and it could be that with detailed study there are
optimizations that could be done even without spending money, but I
think I'm about tapped out on what I can do over an Internet mailing
list.
Thanks for your assistance, Robert. It's been educational.
Regards, Faheem.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
