Re: [HACKERS] VLDB Features
On Saturday 2007-12-15 02:14, Simon Riggs wrote: > On Fri, 2007-12-14 at 18:22 -0500, Tom Lane wrote: > > Neil Conway <[EMAIL PROTECTED]> writes: > > > By modifying COPY: COPY IGNORE ERRORS or some such would instruct COPY > > > to drop (and log) rows that contain malformed data. That is, rows with > > > too many or too few columns, rows that result in constraint violations, > > > and rows containing columns where the data type's input function raises > > > an error. The last case is the only thing that would be a bit tricky to > > > implement, I think: you could use PG_TRY() around the > > > InputFunctionCall, but I guess you'd need a subtransaction to ensure > > > that you reset your state correctly after catching an error. > > > > Yeah. It's the subtransaction per row that's daunting --- not only the > > cycles spent for that, but the ensuing limitation to 4G rows imported > > per COPY. > > I'd suggest doing everything at block level > - wrap each new block of data in a subtransaction > - apply data to the table block by block (can still work with FSM). > - apply indexes in bulk for each block, unique ones first. > > That then gives you a limit of more than 500 trillion rows, which should > be enough for anyone. Wouldn't it only give you more than 500T rows in the best case? If it hits a bad row it has to back off and roll forward one row and one subtransaction at a time for the failed block. So in the worst case, where there is at least one exception row per block, I think you would still wind up with only a capacity of 4G rows. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VLDB Features
On Friday 2007-12-14 16:22, Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > By modifying COPY: COPY IGNORE ERRORS or some such would instruct COPY
> > to drop (and log) rows that contain malformed data. That is, rows with
> > too many or too few columns, rows that result in constraint violations,
> > and rows containing columns where the data type's input function raises
> > an error. The last case is the only thing that would be a bit tricky to
> > implement, I think: you could use PG_TRY() around the InputFunctionCall,
> > but I guess you'd need a subtransaction to ensure that you reset your
> > state correctly after catching an error.
>
> Yeah. It's the subtransaction per row that's daunting --- not only the
> cycles spent for that, but the ensuing limitation to 4G rows imported
> per COPY.
You could extend the COPY FROM syntax with a COMMIT EVERY n clause. This
would help with the 4G subtransaction limit. The cost to the ETL process is
that a simple rollback would not be guaranteed send the process back to it's
initial state. There are easy ways to deal with the rollback issue though.
A {NO} RETRY {USING algorithm} clause might be useful. If the NO RETRY
option is selected then the COPY FROM can run without subtransactions and in
excess of the 4G per transaction limit. NO RETRY should be the default since
it preserves the legacy behavior of COPY FROM.
You could have an EXCEPTIONS TO {filename|STDERR} clause. I would not give the
option of sending exceptions to a table since they are presumably malformed,
otherwise they would not be exceptions. (Users should re-process exception
files if they want an if good then table a else exception to table b ...)
EXCEPTIONS TO and NO RETRY would be mutually exclusive.
> If we could somehow only do a subtransaction per failure, things would
> be much better, but I don't see how.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] My honours project - databases using dynamically attached entity-properties
On Wednesday 2007-03-14 08:26, Csaba Nagy wrote: > On Wed, 2007-03-14 at 16:08, [EMAIL PROTECTED] wrote: > > On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: > > > "David Fetter" <[EMAIL PROTECTED]> writes: > > > > CREATE TABLE symptom ( > > > > symptom_id SERIAL PRIMARY KEY, /* See above. */ > > > > ... > > > > ); > > > > > > > > CREATE TABLE patient_presents_with ( > > > > patient_id INTEGER NOT NULL REFERENCES patient(patient_id), > > > > symptom_id INTEGER NOT NULL REFERENCES symptom(symptom_id), > > > > UNIQUE(patient_id, symptom_id) > > > > ); > > > > > > I'm just glad I don't have your doctor. I hope mine doesn't think > > > symptoms are all boolean values. > > > > Where is the boolean above? It is M:N, with each having whatever data > > is required. > > The boolean is assumed in the symptoms table. In any case, even if it's > not a boolean value, even if maybe the symptoms table is a complex one > on it's own, it still is one single type for all symptoms of all > patients. The real problem is that in some real world applications you > have a mix of wildly varying types of attributes a user might want to > use, and you can't know what those will be beforehand... the symptoms > thing is simple to solve in the way David did it, but there really are > other situations which a simple m:n can't easily cover. How would you > handle a data base of user settings for 10K different applications and > 100M different users where each application must be able to store it's > own (type safe !!) settings in the same structure, and applications come > and go with their own settings ? Come up with a good solution to this > combined with queries like "give me all the users who have this set of > settings set to these values" running fast, and then you're talking. > > Cheers, > Csaba. At that point traditional referential database design starts to break down. If you need THAT MUCH flexibility it's probably time to look at network databases, knowledge bases, extensible knowledge bases, and ad hoc knowledge bases (OWL, RDF, etc). Flexibility, friendliness to marketeers or accountants, extesiblity, none are really the strong points of relational database. Databases as they exist today do best with finite domains that can be formally organized. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How to avoid transaction ID wrap
On Tuesday 2006-06-06 20:11, Mark Woodward wrote: > > Mark Woodward wrote: > >> OK, here's my problem, I have a nature study where we have about 10 > >> video > >> cameras taking 15 frames per second. > >> For each frame we make a few transactions on a PostgreSQL database. > > > > Maybe if you grouped multiple operations on bigger transactions, the I/O > > savings could be enough to buy you the ability to vacuum once in a > > while. Or consider buffering somehow -- save the data elsewhere, and > > have some sort of daemon to put it into the database. This would allow > > to cope with the I/O increase during vacuum. > > The problem is ssufficiently large that any minor modification can easily > hide the problem for a predictble amount of time. My hope was that someone > would have a real "long term" work around. I'm not certain that I understand the original problem correctly so I am going to restate it. VACCUM needs to be run for two reasons. 1) To recover the transaction counter. 2) To recover records marked for deletion. VACCUM needs to be run over the entire database. If the data in the database is N, then VACCUM is O(N). Roughly, VACCUM scales linearly with the size of the database. In the digital video problem: Data is stored indefinitely online. (It is not archived.) (Virtually no records need to be recovered from deletion.) Data comes in at a constant rate, frames. The database therefore grows at frames/time (D). It follows that no matter how much tuning is done, given constant hardware, VACCUM grows to consume so many resources that it is no longer possible to process frames/time[m] before frames/time[m+1] arrives. Ideally, the transaction management system would be proportional to the marginal change in size of the database rather than the gross size of the database. That is VACCUM being O(N) should be replaced (or there should be an optional alternative) that scales with D, O^k(D) where any k > 1 involves a tradeoff with VACCUM. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Modular Type Libraries: was A real currency type
On Wednesday 2006-03-22 08:53, William ZHANG wrote: > "Tom Lane" <[EMAIL PROTECTED]> > > > Timestamps and numerics are definitely in the spec, geometric and > > network types are definitely not. IIRC, bitstring types are in SQL99 > > but for some reason are deprecated in SQL2003 (if anyone knows the > > reasoning behind the SQL committee's about-face on that, please clue > > us in). > > There is a standard data type called "BIT" in ODBC and JDBC, > but it is sth. like SQL standard's BOOLEAN, not BIT. It seems that > some DBMSs implement BIT as BOOLEAN in the backend. Maybe the standard > committee think that bit string is useless and easy to cause confusion? In or out of the standard, bitstring can be a nice type to have. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Modular Type Libraries: was A real currency type
Without directly addressing the merits of enumerations, enumeration interfaces, real currency and time zone types, or whether currency and time zone types should be built using enumerations, I would like to ask the powers-that-be to seriously consider radically modularizing Postgresql's type system. The core Postgresql installation would come with just those built-in types needed to bootstrap itself, perhaps just varchar and an integer type. Everything else would be a contributed module. An interface or contract would be described for creating additional types. It would include things like parameter handlers, how to dump the type, and how to load the type. (That is, standard housekeeping functions needed by the Postgresql engine.) Other that the tiny number of bootstrap types, Postgresql types would basically all be contrib modules. Types could be bundled into groups such as binary, character, numerical, 2d-spatial, networking, and so on. Then one would not debate whether a type (or meta-type, like an enumeration) should be put into the core product. Instead, the debate would be whether or not to grade the type as "mature" and whether or not to put a given type into pre-packaged type libraries with names like "legacy", "sql-2003-standard", or "recommended-default". Power user DBA's could customize the types offered on their systems. In short: 1) Types would be modular. This would be elegant, but have no practical effect on database performance. 2) The framework needed to support modular types would encourage type development. This would enhance Postgresql's adaptability which would be A Very Good Thing. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving N-Distinct estimation by ANALYZE
Sorry to interupt. The discussion is interesting, but I need some help to
follow along.
On Wednesday 2006-01-04 17:07, Josh Berkus wrote:
> Simon,
>
> > - Are there any performance issues that can be directly attributed to
> > mis-estimation of N-Distinct ("D") by the ANALYZE command?
>
> Yes. There's at least one query (maybe two) from TPC-H which bombs
> because of bad N-distinct estimation, even with stats_target =1000. Based
> on my experience with data warehouses, this also occurs in the field.
>
> > - If so, can we do better than we currently achieve? How?
>
> Replace the current algorithm and broaden the sample.
Is "replace the algorithm" the same as saying "contextually use some estimate
of D that is not Chaudhuri?
> > - Would altering the estimate of D cause problems in other places?
>
> Unlike Index Cost Estimation, I wouldn't expect it to. We make pretty
> "proper" use of D right now, it's just that for some common cases our
> estimates of D are bad.
>
> > The estimation of D is difficult and imprecise. The current method works
> > well in many cases, yet breaks down *badly* in one particular very
> > common use case of database design: a large dependent table with a
> > multi-column Primary Key. In some cases the estimate of D *decreases* as
> > the size of the table increases, though the estimate of D is an
> > underestimate in almost all cases, whatever the table design.
>
> Actually, the current estimator underestimates D for *any* large table's
> high-cardinality columns, primary key, multi-column, or not.
> Chaudhuri's calculation seems to be designed to yield the smallest
> number of cardinal values that could reasonably be expected to yield the
> provided sample. That is, if the estimate range within a stdev of 2.0
> gives from 50,000 to 500,000 distinct values, Chaudhuri picks 50,000.
>
> This conservative approach makes sense when you're only considering join
> strategies. That is, given an unreliable estimate you want to estimate
> D low so that you don't wrongly choose a nested loop, the cost for which
> mistake being much higher than the cost of performing an unnecessary
> hash join. It's "conservative" in that sense.
So Chaudhuri's estimate of D is appropriate (and is working) when making
decisions about joins.
> However, PostgreSQL now has a whole set of hash operations and other
> query types for which a
> too-low estimate of D causes query lockup.
Why?
> So for these operations,
> Chaudhuri ceases to be conservative and becomes high-risk. FWIW, my
> testing with TPCH showed that estimate error is usually OK within +/-
> 5x. Beyond that any you start to get bad query plans.
>
> (yes, I know all of the above begs examples. I'm swamped. I believe I
> posted examples when I first started talking about n-distinct estimation a
> year ago)
>
> So I think it's vital that we look at algorithms designed to deliver us
> the median estimated D, not the lowest reasonable, in addition to
> increasing sample size. The block-based estimator functions which
> Andrew and I looked at seem designed to do that provided a sample of
> between 1% and 10%.
Do you *really* want the median estimate in these case? Are you certain you
do not want something with the opposite behavior of Chaudhuri's estimate so
that for small sample sizes the bias is toward a high estimate of D?
(Converges on D from the right instead of the left.)
Chaudhuri's <-D--> needed
Estimate estimate
> > 1. *All* methods of statistical analysis are improved by larger sample
> > fractions. The D estimator method currently in use shows an optimum of
> > accuracy and sample fraction at around 5% of a table, as shown in the
> > author's original paper [Haas Stokes (1998)]. The current
> > implementation's error rates climb higher as table size increases.
>
> I read 5 different papers on ACM about sampling D. All of them were
> united in saying that you couldn't get even slightly accurate estimates
> with less than 3% sampling.
These statements are at odds with my admittedly basic understanding of
statistics. Isn't the power of a sample more related to the absolute size of
the sample than the sample as fraction of the population? Why not just pick
a smallish sample size, say about 3000, and apply it to all the tables, even
the ones with just a single row (modify appropriately from block sampling).
> > 2. In terms of I/O, ANALYZE is relatively inefficient, since it uses a
> > row sampling technique rather than a block sampling technique. This
> > would translate directly into a performance drop from large sample
> > ratios, but since we currently use a fixed sample size this problem is
> > not yet visible for larger tables. With a 2GB table, we would typically
> > sample 1% of the blocks, yet around 0.025 - 0.05% of the rows.
>
> This woudl be a reason to use block-sampling ONLY, rather than hybrid
> sampling.
>
> > 3. Large values of statistics
Re: [HACKERS] Automatic function replanning
On Thursday 2005-12-22 14:28, Lukas Kahwe Smith wrote: > Bruce Momjian wrote: > > Right, if the cardinality changes, you realize this before execution and > > optimize/save the plan again. A further optimization would be to save > > _multiple_ plans for a single prepared plan based on constants and > > choose one of the other, but that is beyond where we are willing to > > consider at this stage, I think. > > ok .. so you store the cardinality that was used when generating the > original plan. on the next execution you look up the cardinality again > and compare it, if its off too much, you replan. however this could in > extreme cases mean that you replan on every execution and thereby > killing off the entire advantage of storing the plan. but thats the > absolute worse case scenario. > > regards, > Lukas > > PS: bruce original email was only send to me directly .. So you have a parameterized query (one parameter for simplicity of argument), as the parameter changes, cardinality changes dramatically. It seems to me that in this case better than replanning is building a data structure that associates different parameter values with appropriate plans. The plans can be reused until, as would be the case with an no-parameter query, a parameter specific plan should be flushed (or the entire family of plans can be flushed). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Automatic function replanning
On Saturday 2005-12-17 16:28, Lukas Smith wrote: > Bruce Momjian wrote: > > * Flush cached query plans when the dependent objects change, > > when the cardinality of parameters changes dramatically, or > > when new ANALYZE statistics are available > > Wouldn't it also make sense to flush a cached query plan when after > execution it is determined that one or more assumptions that the cached > query plan was based on was found to be off? Like the query plan was > based on the assumption that a particular table would only return a hand > full of rows, but in reality it returned a few thousand. > > regards, > Lukas > Proposed rewrite * Mark query plan for flush (opportunistic replan) when: ** dependent objects change, ** cardinality of parameters changes sufficiently (per planner parameter) ** when new ANALYZE statistics are available and per planner parameter differ sufficiently from prior statistics. * Mark plan as "tried" when parameters of returned set out of statistical control, create alternate plan hill-climbing to statical control. ** Too many/too few rows relative to plan expectations *** Auto-sample for better statistics? ** History of plan shows throughput time for result set varies excessively (need more execution stability, possibly at expense of median optimality). > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Inherited Constraints
On Thursday 2005-12-08 15:47, Simon Riggs wrote: > does of course already exist, so the following should cause dependency > violation ERRORs: > - omitting the CASCADE when attempting to delete parent constraint > - attempting to drop the child constraint Why should dropping the child constraint fail? Child tables are supposed to be able to over-ride parent constraints. Dropping a parent's constraint sounds like just a way to over-ride a constraint with no constraint at all. (Making the column unconstrained.) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Feature Request: Multi-octet raw
It would be nice if Postgresql supported multi-octet raw data. Certainly a lot of what you would do with it would be similar to bytea, but the basic string functions would be overloaded so that the unit of work would be a multi-octet word. Multi-octet instances could be cast to bytea when one wanted to work with the data bit-by-bit instead of word-by-word. the declaration would be something like: CREATE TABLE acme ( ... ,multi_octet_foo MULTIOCTET (octets-per-word, max-words-per-column) ,... ) To reuse a declaration you could create a domain. MULTIOCTET columns could be loaded using octal, decimal, or hexadecimal text input. The real advantage of a multi-octet type would be for power users. The code would be reusable. It would simplify tasks like creating the often requested native support for UTF-16 and the less frequently requested UTF-32. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] generalizing the planner knobs
Is it possible to submit a hand written or arbitrary execution plan to the retrieval engine? (That is, can one bypass the SQL parser and planner or optimizer and just provide instructions to nested loop join table a to table b ...) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] generalizing the planner knobs
On Thursday 2005-12-01 19:01, Gregory Maxwell wrote: > On 12/1/05, Pollard, Mike <[EMAIL PROTECTED]> wrote: > > Optimizer hints were added because some databases just don't have a very > > smart optimizer. But you are much better served tracking down cases in > > which the optimizer makes a bad choice, and teaching the optimizer how > > to make a better one. That way, all users get the benefit of the fix. > > Remember, the purpose of SQL is to isolate the end user from having to > > care about how the data is retrieved; that is the RDBMS' problem. (the > > other thing forgotten was that it was supposed to be a natural language. > > NVL. Bah.) > > The flipside there is that a good set of hinting options may increase > the amount of detailed feedback we get from users on improvements > needed in the optimizer. The current knobs are pretty blunt and don't > do as much as I'd like when trying to track down exactly where the > optimiser has gone wrong. > > If we'd really like to avoid people using the knobs to rig queries, > how about making them only work with explain analyze, useful for > debugging but not so useful for actual queries. I'm all in favor of sticking to the declarative language ideal. Also, I'm much in favor of protecting people from themselves. On the other hand, if folks insist on engaging in extreme sports (like second guessing the optimizer) I'm against regulating their freedom. I think exposing planner variables would be a good thing, on net. Naturally, you would warn everyone not to touch them. (Safety and freedom are both necessary.) If you can play with the knobs, you should let them be used to return real result sets. That way, when you get feedback, you will be able to tell if the cost estimator is "broken". Just returning a modified plan won't challenge costing assumptions. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] Major Problem, need help! Can't run our website!
On Monday 2005-11-14 20:48, Tim Allen wrote: OOPS deleted pg_xlog because surely it was only a log file. > > We've seen reports of people firing this particular foot-gun before, > haven't we? Would it make sense to rename pg_xlog to something that > doesn't sound like it's "just" full of log files? Eg pg_wal - something > where the half-educated will have no idea what it is, and therefore not > think they know what they can do with it. > > Tim Renaming the file sounds like an excellent design decision since the current name is a proven "human factor" bug. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] enums
On Thursday 2005-10-27 17:39, Michael Glaesemann wrote:
> On Oct 28, 2005, at 9:23 , Trent Shipley wrote:
> > On Thursday 2005-10-27 16:22, Andrew Dunstan wrote:
> >> Jim C. Nasby wrote:
> Relational databases already have a type for unordered sets: tables.
> IMO, if there's going to be a separate enumerated type, it should be
> more than just an alternative way of defining a set of key-value pairs.
>
> Michael Glaesemann
> grzm myrealbox com
Of course, what is an enumeration except an *ordered* list of key-value pairs;
that is, a set with a built-in collation.
Glaesemann is right. To me that implies that no database NEEDS an enumeration
type.
We just started discussing it because it would greatly enhance MySQL
migration. Even more important, enumerations would be tremendously
convenient (even if they are not strictly necessary).
Enumerations would be good marketing and good engineering.
The debate is about implementation:
1) Pure list (seems to be why MySQL does). Each enumeration is a list. If
you want to create a variant collation for the list, create a new, parallel
list.
This is very straightforward but violates the "store once, read many times"
principle of database design.
2) Hybrid list + secondary collations. You create and store an enumeration
(call it enum_a). If you want to reorder the underlying set, just declare a
new collation for the enumeration (call the result enum_b). enum_b is
effectively a virtual enumeration. The relationship of enum_b to enum_a is
like that between a view and its table.
On the downside, this approach is a theoretical stew. It should be relatively
easy to implement.
3) Set + collation functions. You define a set. You define a collation for
the set. Having declared set_a and a collation_a you can then declare
enumeration_a. (Note that the result defined by the developer's collation
function may not necessarily result in a _per se_ enumeration.)
This has the appeal of separating the symbol declaration from its ordering.
Furthermore, of all the options it is the most powerful. Unfortunately, it
may be verbose, unintuitive, and the most difficult to implement.
There is probably no reason approach #1 or #2 could not be implemented using
the machinery for approach #3 under the bonnet. That way we could have
something like:
CREATE SYMBOL SET
{possibly a disguised create table, but probably not for performance
reasons}
CREATE COLLATION USING function_name
CREATE ENUMERATION
and
CREATE MYSQL_ENUMERATION. {probably just overload CREATE ENUMERATION}
===
http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html
http://dev.mysql.com/doc/refman/5.1/en/enum.html
(Note that unlike C enumerations MySql enumerations are two way and do some
context dependent magic.)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] enums
On Thursday 2005-10-27 16:22, Andrew Dunstan wrote: > Jim C. Nasby wrote: > >Like I said, if we're going to support a concept of ordering of items in > >an enum then we need to support it fully. For starters that means having > >the ability to re-order things in an enum seamlessly. > > I do not see this at all. An enumeration defines an ordering and a set > of labels. Why should you be able to change it? If you want a different > ordering, create a new enumeration. Let's do this right because it's a > feature worth having, not just mimic the competition's idiocy > The symbols in the set have no _per se_ order. A collation rule is necessary to sort the symbols consistently. ASCII is an enumeration Unicode is a large enumeration with a simple naive collation and a complex default collation. Defining a set results in an unordered specification of symbols. Defining a collation produces an ordering for the set. There can be many collations for a set. An enumeration is just a computer science short-hand way to define a set and a "native" collation for the set. An enumeration's native collation need not be the only, or even the most common, collation for the enumerated set of symbols. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] relational class vs partitioned table (was Inherited indexes)
On Tuesday 2005-10-04 13:54, Simon Riggs wrote: > On Tue, 2005-10-04 at 18:16 +0200, Zeugswetter Andreas DAZ SD wrote: > > > Another possibility is optimizing for the special case of > > > indexing on a partitioning key. In this case, index values > > > would be very localized to one table, so just storing the > > > table info on each index page (or something similar) would work well. > > > > If you have the partitioning key in the index and the partitions don't > > overlap, it is better to create separate [unique] indexes on the > > subtables. > > Building separate indexes per partition is usually preferred because of: > > 1. performance of dropping a partition > > 2. smaller index for CE > > ... merge node strategy. > > Usually you will only want the "one big unique index" when the > > partitioning is not > > reflectable in the index keys, and then (also in other db's) such an > > index is usually a pain ... > > Agreed^2. The idea of a global index is a non-starter for all of the > reasons that Tom gave and the main one: Its's unusably huge. There's no > point in partitioning a 1TB table if you then have to build a 500GB > index on it. The tree would be so deep that each insert would require > maybe 3 I/Os on index branch blocks before you got to the leaf. Insert > performance would suck real bad, which is a blocker since if you have a > large table you almost certainly have a lot of data to load. If you > don't have a big table you shouldn't be partitioning it anyway. It has taken me a while to organize my thoughts on this post to the thread, but I am struck by the fact that what started out as a discussion of relational inheritance and support for multi-relation uniqueness by indexes morphed into a discussion of partitioning table storage and how that might be supported by indexes. It is possible that the topical change was simply due to the usual meandering of threads but I fear that instead it may not have been random but caused by conflating the inheritance problem with partitioning. The two problems have seeming similarities inasmuch as both involve multiple internal tables. Unfortunately, they are rather distinct. Partitioning is a database engineering tool to enhance the performance of HUGE databases, most notably biogenetic databases. Partitioning is a classic divide and conquer strategy -- the goal is to chop something unmanageably large into things that are manageably small. Just as critical partitioning is in no way a relational problem. It has no effect on data representation and it should be irrelevant to a database developer or a report writer. Partitioning is strictly a storage and implementation problem. In general, one partitions by a hash rule (usually a simple modulus operation on a serial number or OID) or by a range rule -- typically on a date-time or possibly postal codes. Since the partition rule is "published" the database engine can "hash" to the proper index or table. Note that just as one can have multi-dimensional arrays, partitioning presents an analogous data storage problem and there is no logical reason that a relation could not be partitioned by any number of ranges or hashes. In practice, this serves no useful performance advantage, but simply divides the logical table (relation) into many small physical tables. In Oracle indexes of attributes that are used as partitioning criteria are simply partitioned in parallel to the (logical) table they reference. This seems to be the sort of solution that the thread was heading toward. --- In contrast relational inheritance is a design tool that would allow polymorphic access to modestly large relations. Note that an instance of relational inheritance explicitly or implicitly establishes a relational class. Furtheremore, a relational class is a type of multi-relation. Relational inheritance provides partial semantic unification over a taxonomic space. The semantic element in relational inheritance is critical. With a relation partitioned on _sequence_result_, given any _sequence_result_ one knows in what physical table(s) to look up the associated tuple (because by definition partitioned relations have partition rules). With a multi-relation partially unified on _sequence_result_, however, there is no way one can know what member table has _sequence_result_'s tuple. In the case of a relational class (or any other partially unified multi-relation) you have to use a layer of indirection. Class-wide uniqueness (partial unification of multi-relations) can be solved by using a global index for the relational class. It cannot be solved using partitioning. In many ways the problems are quite distinct. = Of course, there is no reason a relation in a relational class might not be huge. By way of inclusion, a relational class containing one or more huge relations would also be huge. It seems to me that rather that partitioning member relations on unifying
Fwd: Re: [HACKERS] postgresql clustering
What is the relationship between database support for clustering and grid computing and support for distributed databases? Two-phase COMMIT is comming in 8.1. What effect will this have in promoting FOSS grid support or distribution solutions for Postgresql? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: R: [HACKERS] feature proposal ...
On Thursday 2005-09-22 13:16, Andrew Dunstan wrote: > Jim C. Nasby wrote: > >While I'm all for COPY from views, I think I'd rather have the syntactic > >warts than code warts. ISTM that > > > >CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; > >COPY some_name TO stdout; > > > >is much uglier than > > > >COPY SELECT * FROM table WHERE ... TO stdout; > > They aren't mutually exclusive, though. And once you have code in place > for the first part, turning the direct query case into a temp_view+copy > is arguably just a case of syntactic sugar. I do think the direct query > should at least be parenthesized, if we go that way. Definitely any SELECT that might occur in COPY should be a sub-select. It should meet any syntactic restrictions on a sub-select and it should be in parentheses (or for the liberal, implied parentheses). Proposed: o Allow COPY to output from views -- Pending "Allow COPY to output from views", Allow COPY to output from subqueries. The rationale being that all subqueries can be the create clause of a views. > So why not do what everyone is agreed on now? Whatever happens the work > won't be wasted. > > Also, as nifty as this might be, we should also be prepared for people > to complain that it runs a lot slower than vanilla COPY, because it > surely will. Why would there be a material difference in speed in the case of a simple projection? For example Given CREATE TABLE foo ( col_0 ,col_1 , . , . ,col_2N) Then COPY (SELECT col_0 ,col_2 , . , . ,col_2N) TO file-like-target ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] feature proposal ...
On Wednesday 2005-09-21 07:01, Hans-Jürgen Schönig wrote: > Rod Taylor wrote: > >>the problem is: COPY can write data returned by a SELECT statement to a > >>file. our idea is to implement precisely that. > >> > >>example: > >> > >>COPY TO file_name USING some_select_statement; > > > > I have run into plenty of cases where I wanted to dump part of a > > structure and this could be used for that, but I've always found that > > temporary tables were sufficient and equally SQL scriptable > > > > CREATE TEMP TABLE tab AS SELECT ...; COPY tab TO file_name; > > Hi Rod, > > TEMP TABLE are not suitable for my case. Using a temp table would > essentially mean that we had to store the data 3 times: Original data, > temp table + dump. Temp tables are only fine for small amounts of data > but we are talking about too much data here (my smallest export will > contain 15.000.000 records). Wouldn't you also need a CREATE TEMP TABLE privilege but the COPY TO file USING select_statement would only need select. (In other words using a temp table would not seem to be as secure nor as general as the requested feature.) Ideally COPYing from a view would be supported. As a user I like to treat a relation as a relation without having to worry about it's type. Nevertheless, there remains the issue of atomic permissions. One ought to be able to make selecting, copying, and creating views independent permissions for groups, roles, and users. A user should be able to copy and select without being able to create views. One can think of a table as a prototypical relation. Views are virtual tables. SELECT statements are ephemeral views. In a select statement you can use a proper table, a pre-defined view, or another select statement in the FROM or WHERE clause. Parallel behavior for COPY is reasonable. ---(end of broadcast)--- TIP 6: explain analyze is your friend
