Re: master/detail: master rows without details

[Thomas Guettler <hv@xxxxxxxxxxxx>]

Peter Eisentraut <peter_e@xxxxxxx>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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general