Weird behavior with selects and composite data types

From: lists(at)petri(dot)cc
To: pgsql-general(at)postgresql(dot)org
Subject: Weird behavior with selects and composite data types
Date: 2004-09-20 13:55:17
Message-ID: 200409201555.17742.lists@petri.cc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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,(acl).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.866 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.489 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.310 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'::text 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_parent ON rt_host(parent);
CREATE INDEX rt_host_idx_guid ON rt_host(guid);
CREATE INDEX rt_host_idx_active ON rt_host (guid) WHERE (deleted IS NULL) AND
(is_scan = false);

CREATE TABLE object (
guid integer NOT NULL DEFAULT nextval('guid_seq'),
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_cluster_id()] NOT NULL,
clu_rnode integer DEFAULT f_get_my_cluster_id() NOT NULL
) WITHOUT OIDS;
CREATE UNIQUE INDEX object_guid_idx ON object(guid);

CREATE OR REPLACE FUNCTION f_all_acl(INTEGER) RETURNS t_acl_rwl AS '
SELECT COALESCE(read,false),COALESCE(write,false),COALESCE(list,false) FROM
acl_cache WHERE aclobj=$1
AND userid=f_get_sess_user() LIMIT 1;
' LANGUAGE SQL STABLE STRICT;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-09-20 14:25:18 Re: order of row processing affects updates
Previous Message Karel Zak 2004-09-20 09:20:34 Re: Problem in converting int to timestamp value - why?