By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,372 Members | 1,950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,372 IT Pros & Developers. It's quick & easy.

flattened tables with normalized tables

P: n/a
If I want to set up a dbase with normalized tables for inserts,and a
flattened table for selects, am i going in the right direction for
speeding up a busy site?

Also, if some of you are also doing this, how and how often do you do
the SELECT from the normalized tables to the flattened table?
And, do you have to write a post trigger to get all the references to
match up in the flattened table?

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Thu, Oct 07, 2004 at 10:07:47AM -0700, Dennis Gearon wrote:
If I want to set up a dbase with normalized tables for inserts,and a
flattened table for selects, am i going in the right direction for
speeding up a busy site?
Are you familiar with views? If so, is there a reason not to use
them? What kinds of queries are you making? Are you experiencing
performance problems with queries on the normalized tables? Have
you investigated whether those queries can be sped up?
Also, if some of you are also doing this, how and how often do you do
the SELECT from the normalized tables to the flattened table?
And, do you have to write a post trigger to get all the references to
match up in the flattened table?


General Bits had an article on materialized views a while back:

http://www.varlena.com/varlena/GeneralBits/64.php
http://www.varlena.com/varlena/Gener.../matviews.html

Before deciding on a solution, be sure you fully understand the
problem you're trying to solve.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
Michael Fuhr wrote:
On Thu, Oct 07, 2004 at 10:07:47AM -0700, Dennis Gearon wrote:
If I want to set up a dbase with normalized tables for inserts,and a
flattened table for selects, am i going in the right direction for
speeding up a busy site?

Are you familiar with views? If so, is there a reason not to use
them? What kinds of queries are you making? Are you experiencing
performance problems with queries on the normalized tables? Have
you investigated whether those queries can be sped up?

Also, if some of you are also doing this, how and how often do you do
the SELECT from the normalized tables to the flattened table?
And, do you have to write a post trigger to get all the references to
match up in the flattened table?

General Bits had an article on materialized views a while back:

http://www.varlena.com/varlena/GeneralBits/64.php
http://www.varlena.com/varlena/Gener.../matviews.html

Before deciding on a solution, be sure you fully understand the
problem you're trying to solve.

I am just planning ahead. I hope to have the site I'm building mushroom into a high traffic site, and I want to be prepared. In the order that is necessary, with help fromt he list and probably the manual/books, I will throw:
tuning,
hardware selection
dedicated hardware,
materialized views
whatever else is appropriate at the right time,

at the problem.

I will also do all the things that you suggest as well, before I try materialized views. I found the same article that you quoted at a different location, so I'm up to speed on that.

About regular views, how does that speed things up, other than the initial SQL interpretation of the view not needing to be done?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

P: n/a
On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:

About regular views, how does that speed things up, other than the initial
SQL interpretation of the view not needing to be done?


I didn't mean to imply that views would speed things up -- I was
merely suggesting them as an alternative to your "flattened table"
if part of its purpose would be to simplify queries. You might
want to perform some experiments to see if the performance gains
from a materialized view are worth the extra complexity.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4

P: n/a
Michael Fuhr wrote:
On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:
About regular views, how does that speed things up, other than the initial
SQL interpretation of the view not needing to be done?

I didn't mean to imply that views would speed things up -- I was
merely suggesting them as an alternative to your "flattened table"
if part of its purpose would be to simplify queries. You might
want to perform some experiments to see if the performance gains
from a materialized view are worth the extra complexity.

I'll do the experiments! It'll probably be about 6 months out. I plan ahead :-)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

P: n/a
On Thu, Oct 07, 2004 at 22:35:50 -0600,
Michael Fuhr <mi**@fuhr.org> wrote:
On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:

About regular views, how does that speed things up, other than the initial
SQL interpretation of the view not needing to be done?


I didn't mean to imply that views would speed things up -- I was
merely suggesting them as an alternative to your "flattened table"
if part of its purpose would be to simplify queries. You might
want to perform some experiments to see if the performance gains
from a materialized view are worth the extra complexity.


It may even turn out there aren't any performance gains from having a
materialized view. That will depend on the mix of operations in production.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #6

P: n/a
Bruno Wolff III wrote:
On Thu, Oct 07, 2004 at 22:35:50 -0600,
Michael Fuhr <mi**@fuhr.org> wrote:
On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:
About regular views, how does that speed things up, other than the initial
SQL interpretation of the view not needing to be done?


I didn't mean to imply that views would speed things up -- I was
merely suggesting them as an alternative to your "flattened table"
if part of its purpose would be to simplify queries. You might
want to perform some experiments to see if the performance gains
from a materialized view are worth the extra complexity.

It may even turn out there aren't any performance gains from having a
materialized view. That will depend on the mix of operations in production.

Well, one particular query / view will probably draw from 11-15 tables. Several of those tables should have millions and millions of rows. However,as normalzed as all the data is, and having used surrogate, integer primary keys, the tables shouldn't be that big, most of them.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.