:[EMAIL PROTECTED] On Behalf Of Andrew - Supernews
Sent: Wednesday, October 12, 2005 1:41 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] slow IN() clause for many cases
On 2005-10-11, Ilia Kantor [EMAIL PROTECTED] wrote:
When in clause becomes large enough (20-30 cases),
It is much
Is there a simple, user-accessible mechanism to schedule a function to
be run at query commit ?
CONSTRAINT TRIGGER (DEFERRABLE)
It is kinda hack, because CONSTRAINT TRIGGERs are not indended for such use,
But there are no other ON COMMIT triggers in postgresql.
When in clause becomes large enough (20-30 cases),
It is much better to use join way of processing..
I mean,
SELECT * FROM table WHERE field IN (1,2...30) will be slower than
SELECT * FROM table JOIN (SRF returning 1...30) USING(field)
I'm not quite sure, where the difference starts, but
Please post an explain analyze on your query with a 20-30 item IN clause so
that we can see what plan is being generated.
It is bitmap-OR on multiple index(PK) lookups.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
Maybe new constraint_exclusion staff could help to exclude non-matching
tables from inheritance query ?
Yes, that's exactly what it's for. Your testing is welcome. Download 8.1
and try it today.
Great, I'm developing on 8.1b2 now...
But could you be more particular about the solution ?
Let table A be inherited by A1, A2, A3.
How to select from A records where actual relations are A1, A2 ?
Why not just select directly from the child tables? I can't get excited
about optimizing the case you propose.
Because WHERE concrete_class IN (a,b,c) is much more convenient and
flexible
If you treat the sub-class Discriminator as a data item rather than some
additional syntax for class membership then you will find this works
very straightforwardly for you.
Your suggestion is essentially the same as mine..
There exists tableoid, pretty much suited to tell between tables in
Let table A be inherited by A1, A2, A3.
How to select from A records where actual relations
are A1, A2 ?
I found a way somewhere, it sounds like SELECT
WHERE tableoid IN (a1.oid, a2.oid),
but tableoid checks actually do seq scan.
Like: SELECT * FROM sometable WHERE tableoid
Common problem with inheritance is that references work ONLY with top table
and are not propagated to children.
Is it possible to write triggers (like utils/adt/ri_triggers.c) which will
act on INSERT/UPDATE/DELETE and check/cascade/restrict properly ?
pg_attribute, pg_inherits inherits can be
I want to write a selectivity function for GIST
indexes.
The select condition is somefield
array[1,2,3],
But when Im inside selectivity function I get
args: T_Var, T_Const.
So function needs to get the actual array contents
from T_Const .
How to do it ?
P.S
T_Const is like
1)
I want to test my own selectivity function against
future stats collector.
Is this a right way to update statistic used by
planner ?
update pg_statistic set
stadistinct=4,stakind3=1,stanumbers3=array[0.8,0.2],stavalues3=array[1,10001]
where starelid=950855 and staattnum =
11 matches
Mail list logo
