DO ?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[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
>> It is bitmap-OR on multiple index(PK) lookups.
> Describing it doesn't help. We need an *actual* EXPLAIN ANALYZE.
Sure, why not..
6ms for
Bitmap Heap Scan on objects_hier (cost=60.29..179.57 rows=80 width=600)
(actual time=0.835..1.115 rows=138 loops=1)
Recheck Cond: ((id = 1) OR (id =
>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?
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
> 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.
-
> 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
>> 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
f
>> 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 soluti
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 us
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 =
I want to write a selectivity function for GIST
indexes.
The select condition is somefield &&
array[1,2,3],
But when I’m 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
12 matches
Mail list logo
