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

Re: Tuning single row operations

From: Jim Nasby <decibel(at)decibel(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: "Matthew O'Connor" <matthew(at)zeut(dot)net>, "Richard Huxton" <dev(at)archonet(dot)com>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Tuning single row operations
Date: 2007-01-03 16:10:08
Message-ID: 0809E5FC-2808-4FB3-A7D3-2ACE3D7101B2@decibel.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Dec 21, 2006, at 9:56 AM, Simon Riggs wrote:
> On Thu, 2006-12-21 at 09:36 -0500, Matthew O'Connor wrote:
>> Richard Huxton wrote:
>>> Simon Riggs wrote:
>>>> - improve RI check perf by caching small, static tables in each  
>>>> backend
>>>> - apply index filter conditions on index scan to avoid heap lookup
>>>
>>> For fkey checks against a basically static table could you get  
>>> away with
>>> just checking the index and not the table?
>>
>> I'm not sure that would fly, there is always the possibility it could
>> change, I think the ENUMs patch will solve this performance problem.
>
> Not using SQL Standard syntax it won't.
>
> I'd be happier if it worked with DOMAINs and happier still if we  
> can get
> it to optimise just bare datatypes. My objective is to tune a database
> without needing to reload any of the tables and to ensure that RI is
> effective in both directions (from referencing and referenced tables).
>
> Perhaps there's a way to make that happen...

A thought I've had is that if we allowed users to control the  
thresholds for compressing and toasting a field, we could essentially  
produce the effects of small, static 'lookup' tables by just using a  
text field in the base table and forcing it to always toast (you'd  
need a check constraint, too). If we detected that situation, we  
might be able to optimize for it...

As for the other items, it would be useful to have some idea what  
kind of performance gains are to be had... maybe it would be  
worthwhile to put together quick-hack patches just for performance  
testing.
--
Jim Nasby                                            jim(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



In response to

pgsql-hackers by date

Next:From: Markus SchiltknechtDate: 2007-01-03 16:20:41
Subject: Re: InitPostgres and flatfiles question
Previous:From: Tom LaneDate: 2007-01-03 16:07:35
Subject: Re: InitPostgres and flatfiles question

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