Re: Inefficient query plan - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Inefficient query plan
Date
Msg-id 4C723D850200002500034A56@gw.wicourts.gov
Whole thread Raw
In response to Re: Inefficient query plan  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Responses Re: Inefficient query plan  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Re: Inefficient query plan  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
List pgsql-performance
Grzegorz Jaœkiewicz<gryzman@gmail.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

pgsql-performance by date:

Previous
From: Grzegorz Jaśkiewicz
Date:
Subject: Re: Inefficient query plan
Next
From: Alvaro Herrera
Date:
Subject: Re: Inefficient query plan