Re: 8.3devel slower than 8.2 under read-only load

>>> On Mon, Nov 26, 2007 at  1:04 PM, in message
<1d4e0c10711261104h63d6ced7i8a8c731ef9f93c18@mail.gmail.com>, "Guillaume Smet"
<guillaume.smet@gmail.com> wrote:
> On Nov 26, 2007 6:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> In the test case Guillame provided, every single WHERE clause happens
>> to be of the form
>>         varchar_column = 'unknown-type literal'
>> and there are no other operators used in the SELECT lists; but I can
>> hardly believe that this is representative of any significant number
>> of real-world applications.  Even pgbench uses more than one operator.
>
> Sure. The application uses a lot of other operators (timestamp
> comparison, cube operators, LIKE, boolean comparison...). But as the
> primary keys are all varchar in this application, the operator varchar
> = unknown is by far the most used.
>
> If we can't find a solution for upstream 8.3, I'll be happy to apply a
> local patch for this customer (if I'm sure it doesn't do any harm in
> their case).
>
> Usually, people use integer flavoured primary keys so they probably
> won't be hit by this problem as strong as we are.
I've been watching this with some concern, as we only use synthetic
keys when there is no naturally occurring set of columns which
uniquely identify a row.  In our circuit court software (where we
have the most users), there are 377 permanent tables, mostly with
compound keys:
keycolcount | count
-------------+-------          1 |    91          2 |   129          3 |    88          4 |    46          5 |    20
     6 |     3 
(6 rows)
We have a lot of varchar columns within those keys, although almost
all are actually declared via a domain:basetype | count
----------+-------date     |    34int      |   146numeric  |     8smallint |   365varchar  |   362
(5 rows)
Complex queries usually join on at least one varchar column and one
smallint column.  Selection criteria usually involve comparing to a
character string literal, sometimes a date literal, and almost
always a smallint.
Are we likely to see the 3% or the 7% performance degradation with
version 8.3?  Is the small patch likely to help with this usage
pattern?
I'm still trying to get 8.3 performance testing onto our schedule
before the end (fast approaching, I know) of the beta.  Is there
anything in particular that would be helpful for me to look at
regarding this particular issue in those tests?
-Kevin