Search Postgresql Archives

Design ? table vs. view?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have a table which has some "raw" data in it. By "raw", I mean it is minimally processed from a log file. Every week, I update this table by processing the weekly log using awk to create a "psql script" file which looks similar to:

COPY rawdata FROM STDIN;
.... lines created by awk script
\.

The table schema is:
        Table "jobrun.rawdata"
  Column  |     Type      |
----------+---------------+
 lpar     | character(4)  |
 yyddd    | character(5)  |
 timedata | character(11) |
 jobid    | character(8)  |
 msgid    | character(7)  |
 jobname  | character(8)  |

Now, this data is not really very useful in its raw form. So I "process" it via a view:

                          View "jobrun.rundata"
 Column  |           Type           |
---------+--------------------------+
 lpar    | character(4)             |
 msgid   | character(7)             |
 jobname | character(8)             |
 jobid   | character(8)             |
 msgtime | timestamp with time zone |
View definition:
 SELECT rawdata.lpar,
    rawdata.msgid,
    rawdata.jobname,
    rawdata.jobid,
    to_timestamp((rawdata.yyddd::text || ' '::text) || rawdata.timedata::text, 'YYDDD HH24:MI:SS.MS'::text) AS msgtime
   FROM rawdata;

My question is this: If I do a number of SELECTs on the "rundata" table. So, would it be worth while to make this a table in itself? The plus of a view is that I don't need to worry about updates. And I still have the "raw" data around. In reality, this is just the first VIEW. I create three other views. Two views are to "subset" the data based on the contents of the "msgid" value (there are only 2 possibilities at present: 'START' and 'END'). The final view, which is my actual information is a FULL OUTER JOIN of the START and END subset, based on lpar,jobname, and jobid:

                          View "jobrun.runinfo"
  Column  |           Type           |
----------+--------------------------+
 lpar     | character(4)             |
 jobname  | character(8)             |
 jobid    | character(8)             |
 runstart | timestamp with time zone |
 runend   | timestamp with time zone |
View definition:
 SELECT COALESCE(a.lpar, b.lpar) AS lpar,
    COALESCE(a.jobname, b.jobname) AS jobname,
    COALESCE(a.jobid, b.jobid) AS jobid,
    a.msgtime AS runstart,
    b.msgtime AS runend
   FROM runstart a
   FULL JOIN runend b ON a.lpar = b.lpar AND a.jobname = b.jobname AND a.jobid = b.jobid;

So the overhead may be quite high, because to SELECT from RUNINFO, PostgreSQL must realize all four views.

I appreciate your thoughts on if this is OK, given that performance is currently acceptable. Mainly because this work is basically only done one a week, on Sundays. And I don't do it myself, it is done via a scheduler (not cron, but similar) which runs some scripts.

--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux