X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E4AA147580B for ; Sun, 16 Feb 2003 15:29:18 -0500 (EST) Received: from mail.idea.net.pl (smtp-in.centertel.pl [194.9.223.7]) by postgresql.org (Postfix) with ESMTP id 474B2475461 for ; Sun, 16 Feb 2003 15:29:18 -0500 (EST) Received: from skorupa (vb145.neoplus.adsl.tpnet.pl [80.50.229.145]) by mta1.centertel.pl (iPlanet Messaging Server 5.2 (built Feb 21 2002)) with ESMTPA id <0HAF003Y84TUYV@mta1.centertel.pl> for pgsql-performance@postgresql.org; Sun, 16 Feb 2003 21:27:31 +0100 (CET) Date: Sun, 16 Feb 2003 21:27:31 +0100 From: Mariusz =?iso-8859-2?q?Czu=B3ada?= Subject: Re: Views with unions In-reply-to: <28375.1045421478@sss.pgh.pa.us> To: pgsql-performance@postgresql.org Message-id: <200302162127.31324.manieq@idea.net.pl> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-2 Content-transfer-encoding: quoted-printable Content-disposition: inline User-Agent: KMail/1.6.1-cool References: <20030216095555.K94589-100000@megazone23.bigpanda.com> <28375.1045421478@sss.pgh.pa.us> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/100 X-Sequence-Number: 1156 Dnia nie 16. lutego 2003 19:51, Tom Lane napisa=B3: > Stephan Szabo writes: > > Yeah, but I think what he's hoping is that it'll notice that > > "key=3D1 and key=3D3" would be noticed as a false condition so that it > > doesn't scan those tables since a row presumably can't satisify both. T= he 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= =20 300.000...500.000 rows a day. With average row size of 100 bytes we get up = to=20 50MB of data per day. Keeping data for 1 year only gives us some 18GB per= =20 table. Also, in each table there is a field with very low cardinality (5..2= 0=20 unique values). This field appears in most of our queries to the table, in= =20 'where' clause (mostly key_field =3D 5, some times key_field in (1,2,3)). What I was thinking of is to implement some kind of horizontal table=20 partitioning. I wanted to split physical storage of data to few smaller=20 tables. In my case it could be come 12 subtables, 1..2 GB each. Now, with= =20 'union-all' view (and lots of rules, of course) I could simultate partition= ed=20 table as Oracle implements it. IMHO while querying this view (supertable) f= or=20 one or few 'key_field' values it should be much faster for scan 5 GB of 3= =20 partitions (subtables) than 18GB for one big table. I realize it is not the only solution. Perhaps it could be implemented by a= =20 function taking key_filed value and returning all rows from proper table=20 (p[lus functions for insert/update/delete). Perhaps application code (log= =20 feeder and report module) could be recoded to know about splitted tables.= =20 Still I think it is 'elegant' and clear.=20 I wait for your comments, Mariusz Czulada