From: Walter Mauritz <waltermauritz@gmx.at>
To: pgsql-performance@postgresql.org
Subject: join tables vs. denormalization by trigger
Date: Tue, 04 Sep 2007 20:53:21 +0200
Message-ID: <20070904185321.221220@gmx.net> (raw)
Hi,
I wonder about differences in performance between two scenarios:
Background:
Table A, ~50,000 records
Table B, ~3,000,000 records (~20 cols)
Table C, ~30,000,000 records (~10 cols)
a query every 3sec. with limit 10
Table C depends on Table B wich depends on Table A, int8 foreign key, btree index
* consider it a read only scenario (load data only in night, with time for vacuum analyze daily)
* im required to show records from Table C, but also with some (~5cols) info from Table B
* where clause always contains the foreign key to Table A
* where clause may contain further 1-10 search parameter
Scenario A)
simply inner join Table B + C
Scenario B)
with use of trigger on insert/update I could push the required information from table B down to table C.
-> so i would only require to select from table C.
My question:
1) From your experience ... how much faster (approximately) in percent do you regard Scenario B faster than A ?
2) any other tips for such a read only scenario
Thx for any attention :-)
Walter
--
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: waltermauritz@gmx.at
Subject: Re: join tables vs. denormalization by trigger
In-Reply-To: <20070904185321.221220@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox