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
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