pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mariusz Czułada <manieq@idea.net.pl>
To: pgsql-performance@postgresql.org
Subject: Re: Views with unions
Date: Sun, 16 Feb 2003 21:27:31 +0100
Message-ID: <200302162127.31324.manieq@idea.net.pl> (raw)
In-Reply-To: <28375.1045421478@sss.pgh.pa.us>
References: <20030216095555.K94589-100000@megazone23.bigpanda.com>
	<28375.1045421478@sss.pgh.pa.us>

Dnia nie 16. lutego 2003 19:51, Tom Lane napisał:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Yeah, but I think what he's hoping is that it'll notice that
> > "key=1 and key=3" would be noticed as a false condition so that it
> > doesn't scan those tables since a row presumably can't satisify both. The

Yes, that is what I expected.

>
> Yes, this is the key point: we won't put in an optimization that wins on
> a small class of queries unless there is no material cost added for
> planning cases where it doesn't apply.
>
> > In addition, you'd have to be careful to make it work correctly with
> > operator overloading, since someone could make operators whose
> > semantics in cross-datatype comparisons are wierd.
>
> It'd be a pretty considerable amount of work to optimize a plan tree
> fully for this sort of thing (eg, suppressing unnecessary joins), and
> I doubt it's worth the trouble.

Ok, perhaps I should give some explaination about my case.

We are gathering lots of log data in a few tables. Each table grows by some 
300.000...500.000 rows a day. With average row size of 100 bytes we get up to 
50MB of data per day. Keeping data for 1 year only gives us some 18GB per 
table. Also, in each table there is a field with very low cardinality (5..20 
unique values). This field appears in most of our queries to the table, in 
'where' clause (mostly key_field = 5, some times key_field in (1,2,3)).

What I was thinking of is to implement some kind of horizontal table 
partitioning. I wanted to split physical storage of data to few smaller 
tables. In my case it could be come 12 subtables, 1..2 GB each. Now, with 
'union-all' view (and lots of rules, of course) I could simultate partitioned 
table as Oracle implements it. IMHO while querying this view (supertable) for 
one or few 'key_field' values it should be much faster for scan 5 GB of 3 
partitions (subtables) than 18GB for one big table.

I realize it is not the only solution. Perhaps it could be implemented by a 
function taking key_filed value and returning all rows from proper table 
(p[lus functions for insert/update/delete). Perhaps application code (log 
feeder and report module) could be recoded to know about splitted tables. 
Still I think it is 'elegant' and clear. 

I wait for your comments,

Mariusz Czulada



view thread (13+ messages)  latest in thread

Message-ID: <200302162127.31324.manieq@idea.net.pl>
Permalink:  ../200302162127.31324.manieq@idea.net.pl/
Also on:    postgresql.org/message-id/200302162127.31324.manieq@idea.net.pl

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: manieq@idea.net.pl
  Subject: Re: Views with unions
  In-Reply-To: <200302162127.31324.manieq@idea.net.pl>
  References: <20030216095555.K94589-100000@megazone23.bigpanda.com> <28375.1045421478@sss.pgh.pa.us> <200302162127.31324.manieq@idea.net.pl>

* 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