pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Spiegelberg <gspiegelberg@cranel.com>
To: pgsql-performance@postgresql.org
Subject: Re: Compare rows, SEMI-SUMMARY
Date: Thu, 09 Oct 2003 16:14:08 -0400
Message-ID: <3F85C190.6080108@cranel.com> (raw)
In-Reply-To: <1065719782.2529.7.camel@fuji.krosing.net>
References: <3F841A28.8060504@cranel.com>
	<200310081611.59179.josh@agliodbs.com>
	<m3fzi35fkd.fsf@wolfe.cbbrowne.com>
	<200310082236.59305.josh@agliodbs.com>
	<1065719782.2529.7.camel@fuji.krosing.net>

Per Josh's recommendation to implement a Vertical Child Table I came
up with 3 possible tables to handle the 3 possible value types: varchar,
numeric and bigint.  Each table has 7 columns: 1 to denote the time the
data was collected, 4 which identify where the data came from, 1 to
tell me the value name and the last being the value itself.

		OLD		NEW
tables		1		3
columns		642		7 each
indexes		~1200		39
views		37		?
rows		1700-3000	30,000
query on table	0.01 sec	0.06 sec
query on view	0.02 sec	?

Not too bad.  Guess there were a few 0's and NULL's out there, eh?

642 * 1,700    = 1,091,400 cells
3 * 7 * 30,000 =   630,000 cells
                    461,400 NULL's and 0's using the big 'ol table

I can get around in this setup, however, I would appreciate some help
in recreating my views.  The views use to be there simply as an initial
filter and to hide all the 0's and NULL's.  If I can't do this I will
be revisiting and testing possibly hundreds of programs and scripts.

Any takers?

Greg

-- 
Greg Spiegelberg
  Sr. Product Development Engineer
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.





Message-ID: <3F85C190.6080108@cranel.com>
Permalink:  ../3F85C190.6080108@cranel.com/
Also on:    postgresql.org/message-id/3F85C190.6080108@cranel.com

 · 

reply

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: gspiegelberg@cranel.com
  Subject: Re: Compare rows, SEMI-SUMMARY
  In-Reply-To: <3F85C190.6080108@cranel.com>

* 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