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

Re: Inefficient query plan

From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: roederja(at)ethz(dot)ch, pgsql-performance(at)postgresql(dot)org
Subject: Re: Inefficient query plan
Date: 2010-08-23 14:11:55
Message-ID: AANLkTi=iBxk7s0jD5biB58LQRGWMNJy6E7cQrw3DyHwO@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Aug 23, 2010 at 2:47 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Grzegorz Jaœkiewicz<gryzman(at)gmail(dot)com> wrote:
>
>> joining on varchars is always going to be very expensive. Longer
>> the value is, more expensive it will be. Consider going for
>> surrogate keys.
>
> Surrogate keys come with their own set of costs and introduce quite
> a few problems of their own.  I don't want to start a flame war or
> go into an overly long diatribe on the evils of surrogate keys on
> this thread; suffice it to say that it's not the first thing to try
> here.
>
> As an example of the performance we get using natural keys, with
> compound keys on almost every table, check out this 1.3TB database,
> being updated live by 3000 users as you view it:
>
> http://wcca.wicourts.gov/
>
> Some tables have hundreds of millions of rows.  No partitioning.
>

True, but as far as joining is concerned, joining on single column
fixed length fields is always going to be a win. Hence why surrogate
keys make sens in this particular example, or the guy here should at
least test it to see, rather than believe in one or the other.


-- 
GJ

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-08-23 14:21:09
Subject: Re: Inefficient query plan
Previous:From: Kevin GrittnerDate: 2010-08-23 13:47:25
Subject: Re: Inefficient query plan

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