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

Re: Inefficient query plan

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: Grzegorz Ja*kiewicz <gryzman(at)gmail(dot)com>
Cc: <roederja(at)ethz(dot)ch>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Inefficient query plan
Date: 2010-08-23 14:21:09
Message-ID: 4C723D850200002500034A56@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Grzegorz Jaœkiewicz<gryzman(at)gmail(dot)com> wrote:
 
> 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.
 
How about we start by just having him use the same data type in both
tables?
 
If you insist on getting into a discussion of the merits of
surrogate keys, you need to look at not just this one query and its
response time, where surrogate keys might give a percentage point or
two increase in performance, but at the integrity challenges they
introduce, and at what happens when you've got dozens of other
tables which would be containing the natural data, but which now
need to navigate through particular linkage paths to get to it to
generate summary reports and such.  It's easy to construct a narrow
case where a surrogate key is a short-term marginal win; it's just
about as easy to show data corruption vulnerabilities and huge
performance hits on complex queries when surrogate keys are used.
They have a place, but it's a pretty narrow set of use-cases in my
book.  For every place they're not used where they should be, there
are at least 100 places they are used where they shouldn't be.
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2010-08-23 14:38:35
Subject: Re: Inefficient query plan
Previous:From: Grzegorz JaśkiewiczDate: 2010-08-23 14:11:55
Subject: Re: Inefficient query plan

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