Re: master/detail: master rows without details

Peter Eisentraut <peter_e(at)gmx(dot)net>Peter Eisentraut schrieb:
> Thomas Guettler wrote:
>> My naive first solution was quite slow. Why is it so slow?
>> I guess (select d.master_id from detail as d) gets executed for every
>> master-row. But why? Shouldn't
>> it be possible to calculate it once and then reuse it?
>
> Please show exact schema dumps and your PostgreSQL version.
>
>
Version:
PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.2.1 (SUSE Linux)

schema dump of both tables:

historytransaction is the master und historystatement is the detail table.

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE foo_historytransaction (
id integer NOT NULL,
date timestamp with time zone NOT NULL,
changedby_id integer NOT NULL
);

ALTER TABLE public.foo_historytransaction OWNER TO user;

CREATE SEQUENCE foo_historytransaction_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.foo_historytransaction_id_seq OWNER TO user;
ALTER SEQUENCE foo_historytransaction_id_seq OWNED BY
foo_historytransaction.id;
ALTER TABLE foo_historytransaction ALTER COLUMN id SET DEFAULT
nextval('foo_historytransaction_id_seq'::regclass);
ALTER TABLE ONLY foo_historytransaction
ADD CONSTRAINT foo_historytransaction_pkey PRIMARY KEY (id);
CREATE INDEX foo_historytransaction_changedby_id ON
foo_historytransaction USING btree (changedby_id);
ALTER TABLE ONLY foo_historytransaction
ADD CONSTRAINT foo_historytransaction_changedby_id_fkey FOREIGN KEY
(changedby_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE foo_historystatement (
id integer NOT NULL,
transaction_id integer NOT NULL,
beleg_id integer NOT NULL,
operation character varying(7) NOT NULL,
tablename character varying(40),
message character varying(1024)
);

ALTER TABLE public.foo_historystatement OWNER TO user;

CREATE SEQUENCE foo_historystatement_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.foo_historystatement_id_seq OWNER TO user;

ALTER SEQUENCE foo_historystatement_id_seq OWNED BY foo_historystatement.id;

ALTER TABLE foo_historystatement ALTER COLUMN id SET DEFAULT
nextval('foo_historystatement_id_seq'::regclass);

ALTER TABLE ONLY foo_historystatement
ADD CONSTRAINT foo_historystatement_pkey PRIMARY KEY (id);

CREATE INDEX foo_historystatement_beleg_id ON foo_historystatement USING
btree (beleg_id);
CREATE INDEX foo_historystatement_transaction_id ON foo_historystatement
USING btree (transaction_id);

ALTER TABLE ONLY foo_historystatement
ADD CONSTRAINT foo_historystatement_transaction_id_fkey FOREIGN KEY
(transaction_id) REFERENCES foo_historytransaction(id) DEFERRABLE
INITIALLY DEFERRED;

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de