Weird behavior with selects and composite data types

[lists]

When using the (tbl).field notation for selecting a specific field from a
composite field then the query returning the field is executed once per
field. An example is giving below. The runtime is directly related to the
number of fields accessed.

The following tests is done with seqscan disabled - but I have confirmed the
exact same behavior with seqscan enabled.

Another side effect of this is the case where f_all_acl() actually modifies or
adds data, then it would actually be done once per column selected. This is
definately not what people expect (or the standard tells if there's any).

Any bright heads here. I confirmed the exact same behavior on both 7.4.5 and
8.0-beta-2. A workaround would also be gladly accepted.

---
Nicolai Petri

---------------- EXPLAINS ---------------------------------------------
dmon2=# explain analyse
SELECT guid, (acl).read, (acl).write,(ac l).list FROM (select guid,
f_all_acl(guid) as acl from rt_host) as i;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on rt_host (cost=100000000 .00..100000001. 37 rows=21 width=4)
(actual time=4.466..9.8 66 rows=
21 loops=1)
Total runtime: 10.017 ms
(2 rows)

dmon2=# explain analyse
dmon2-# SELECT guid, (acl).read, (acl).write FROM (select guid,
f_all_acl(guid) as acl from rt_host) as i;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on rt_host (cost=100000000 .00..100000001. 31 rows=21 width=4)
(actual time=2.833..6.4 89 rows=
21 loops=1)
Total runtime: 6.637 ms
(2 rows)
dmon2=#

dmon2=# explain analyse
dmon2-# SELECT guid, (acl).read FROM (select guid, f_all_acl(guid) as acl
from rt_host) as i;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
------------
Seq Scan on rt_host (cost=100000000 .00..100000001. 26 rows=21 width=4)
(actual time=1.381..3.3 10 rows=
21 loops=1)
Total runtime: 3.444 ms
(2 rows)
----------- (table defs) -----------------
CREATE TABLE acl_cache (
aclobj int4 not null,
modified timestamptz not null default now()
) WITHOUT OIDS;

CREATE TABLE rt_host (
hostname text NOT NULL,
"domain" text NOT NULL,
parent INTEGER,
location INTEGER,
alias text DEFAULT 'hostname'::tex t NOT NULL,
os text,
is_scan boolean DEFAULT 'f' NOT NULL
) INHERITS (object) WITHOUT OIDS;
ALTER TABLE rt_host ALTER type SET DEFAULT 3;
CREATE INDEX rt_host_idx_par ent ON rt_host(parent) ;
CREATE INDEX rt_host_idx_gui d ON rt_host(guid);
CREATE INDEX rt_host_idx_act ive ON rt_host (guid) WHERE (deleted IS NULL) AND
(is_scan = false);

CREATE TABLE object (
guid integer NOT NULL DEFAULT nextval('guid_s eq'),
type integer NOT NULL DEFAULT 0,
-- l_version integer DEFAULT 1 NOT NULL,
created timestamptz DEFAULT now() NOT NULL,
modified timestamptz DEFAULT now() NOT NULL,
deleted timestamptz,
systemobj boolean DEFAULT false NOT NULL,
clu_map integer[] DEFAULT ARRAY[f_get_my_cluste r_id()] NOT NULL,
clu_rnode integer DEFAULT f_get_my_cluste r_id() NOT NULL
) WITHOUT OIDS;
CREATE UNIQUE INDEX object_guid_idx ON object(guid);

CREATE OR REPLACE FUNCTION f_all_acl(INTEG ER) RETURNS t_acl_rwl AS '
SELECT COALESCE(read,f alse),COALESCE( write,false),CO ALESCE(list,fal se) FROM
acl_cache WHERE aclobj=$1
AND userid=f_get_se ss_user() LIMIT 1;
' LANGUAGE SQL STABLE STRICT;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

li***@petri.cc writes:

When using the (tbl).field notation for selecting a specific field from a
composite field then the query returning the field is executed once per
field.
You mean that the function yielding the field is executed repeatedly:
SELECT guid, (acl).read, (acl).write,(ac l).list FROM (select guid,
f_all_acl(guid) as acl from rt_host) as i;

This will be flattened into

SELECT guid, (f_all_acl(guid )).read, (f_all_acl(guid )).write,
(f_all_acl(guid )).list
FROM rt_host;

which is generally a good thing, but perhaps not if f_all_acl() is
expensive to compute. The standard hack at present for preventing
such flattening without changing the query semantics is to insert
an OFFSET 0 clause into the subselect:

SELECT guid, (acl).read, (acl).write,(ac l).list FROM (select guid,
f_all_acl(guid) as acl from rt_host offset 0) as i;

Note that this has nothing to do with field selection in particular,
it's just a general property of the subselect-in-FROM implementation.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org