Re: [HACKERS] Bad estimate on LIKE matching
On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote: On this table, I do a query like: SELECT * FROM path WHERE path LIKE 'f:/userdirs/s/super_73/%' The estimate for this query is comlpetely off, which I beleive is the cause for a very bad selection of a query plan when it's used in a big join (creating nestloops that ends up taking 15+ minutes to complete..). Explain analyze gives: QUERY PLAN --- Index Scan using path_name_idx on path (cost=0.00..3.24 rows=1 width=74) (actual time=0.035..0.442 rows=214 loops=1) Index Cond: ((path = 'f:/userdirs/s/super'::text) AND (path 'f:/userdirs/s/supes'::text)) Filter: (path ~~ 'f:/userdirs/s/super_73%'::text) No matter what I search on (when it's very selective), the estimate is always 1 row, whereas the actual value is at least a couple of hundred. If I try with say f:/us, the difference is 377,759 estimated vs 562,459 returned, which is percentage-wise a lot less, but... I have tried upping the statistics target up to 1000, with no changes. Any way to teach the planner about this? In a recent thread on -perform, I opined that this case could best be solved by using dynamic random block sampling at plan time followed by a direct evaluation of the LIKE against the sample. This would yield a more precise selectivity and lead to the better plan. So it can be improved for the next release. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] equivalence class not working?
On Mon, 2006-01-16 at 19:03 -0500, uwcssa wrote: Fine. The rest documentation says: For now, the test only uses restriction clauses (those in restrictinfo_list). --Nels, Dec '92, however, I understand it as being overridden by the followup, which is:XXX as of 7.1, equivalence class info *is* available. Consider improving this code as foreseen by Nels. All readers are invited to solve the problem. Currently we add only implied equality conditions, so enhancing the optimizer to cope with inequalities seems possible. Best Regards, Simon Riggs ---(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] Bad estimate on LIKE matching
I have tried upping the statistics target up to 1000, with no changes. Any way to teach the planner about this? In a recent thread on -perform, I opined that this case could best be solved by using dynamic random block sampling at plan time followed by a direct evaluation of the LIKE against the sample. This would yield a more precise selectivity and lead to the better plan. So it can be improved for the next release. I was kinda hoping for something I could use in 8.1 :-) Even if it's an ugly solution for now. (My current workaround of writing it to a temp table and the joining to the temp table causes a reasonable plan, but I'd like something slightly less ugly than that if possible..) //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Surrogate keys (Was: enums)
Jim C. Nasby jnasby at pervasive.com writes: a) the optimizer does a really poor job on multi-column index statistics So it should be fixed? And there are a *lot* of singular, natural keys. b) If each parent record will have many children, the space savings from using a surrogate key can be quite large Not such a common case. c) depending on how you view things, putting actual keys all over the place is denormalized How come? Never! Generally, I just use surrogate keys for everything unless performance dictates something else. What I am proposing is the reverse: use natural keys for everything unless performance dictates something else. In support of my PoV: http://blogs.ittoolbox.com/database/soup/archives/007327.asp?rss=1 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Surrogate keys (Was: enums)
Greg Stark gsstark at mit.edu writes: I hate knee-jerk reactions too, but just think of all the pain of people dealing with databases where they used Social Security numbers for primary keys. I would never use an attribute that represents some real-world datum as a primary key any more. I am not familiar with the situation. In my experience there are very few occasions where I want a real non-sequence generated primary key. I've never regretted having a sequence generated primary key, and I've certainly had occasions to regret not having one. http://blogs.ittoolbox.com/database/soup/archives/007327.asp?rss=1 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] enums
Andrew Dunstan andrew at dunslane.net writes: If people would like to play, I have created a little kit to help in creating first class enum types in a few seconds. Isn't what we actually want possreps? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Surrogate keys (Was: enums)
On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimarães Faria Corcete DUTRA wrote: Jim C. Nasby jnasby at pervasive.com writes: Generally, I just use surrogate keys for everything unless performance dictates something else. What I am proposing is the reverse: use natural keys for everything unless performance dictates something else. In support of my PoV: http://blogs.ittoolbox.com/database/soup/archives/007327.asp?rss=1 Interesting. However, in my experience very few things have natural keys. There are no combination of attributes for people, phone calls or even real events that make useful natural keys. You don't say what the primary key on your events table was but I can see one possibility: (place,datetime) A unique on this won't prevent overlapping events. Sure, it'll get rid of the obvious duplicates but won't solve the problem. It also fails the criteria that keys stable, since you can move events. You do need a constraint on that table, but a unique constraint isn't it. While I agree with your statement that it's the abuse of these keys thats the problem, I find people are far too likely to see natural keys where none exist. BTW, the way I deal with people mixing up surrogate keys is by (usually by chance) having the sequences for different tables start at wildly different points. By starting one counter at a million and the other at one, the chances that you'll be able to mix them up is reduced. On some systems I can even identify the table a key comes from by looking at the number, just because I know only one table has keys in the 30,000 range. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] enums
Leandro Guimarães Faria Corcete DUTRA wrote: Andrew Dunstan andrew at dunslane.net writes: If people would like to play, I have created a little kit to help in creating first class enum types in a few seconds. Isn't what we actually want possreps? You appear to be responding to mail from months ago. Please catch up before replying, so we don't rehash old discussions. As previously discussed, I intend to do first class enums for the next release of postgres, if I get enough time. Enumkit was just a very small step along the research road, although it is useful in itself, which is why I released it. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Surrogate keys (Was: enums)
On Jan 18, 2006, at 22:08 , Leandro Guimarães Faria Corcete DUTRA wrote: Jim C. Nasby jnasby at pervasive.com writes: a) the optimizer does a really poor job on multi-column index statistics So it should be fixed? Of course! Patches welcome! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Unique constraints for non-btree indexes
Hi, Currently due to the way unique constraints are tied to btree there is no way to allow GiST indexes to do the same thing. The thing I'm specifically interested in is an index where you insert ranges (start,end) and if unique, the index will complain if they overlap. As a side-effect, this may make progress toward the goal of deferrable unique indexes. Part of the solution is to remove the layering violation from the btree code, it really shouldn't be accessing the heap directly. What I'm proposing is to move the bulk of _bt_check_unique into a new function (say check_unique_index) in the general index machinary and have the b-tree code do just: check_unique_index( ctid of inserting tuple, ctid of possibly conflicting tuple) The point being that GiST indexes could use exactly the same function to check for duplicates. The function would return InvalidTransactionId if there's no conflict, or an actual transaction id to wait on, just like the btree code does now. It would require some changes to the GiST code since a lot more of the index may need to be checked for duplicates. I suppose in the general case, since a key can appear in multiple places, the concurrency issues could be difficult. I suppose you would insert your key first, then check for duplicates thus ensuring that at least one of the two conflicting transactions will see it. Now, one side-effect is that you could build deferrable unique constraints on top of this by having the check function always return InvalidTransactionId but storing the conflicts for later checking. But I first want to know if there are any real issues with the above. Any thoughts? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Unique constraints for non-btree indexes
I thought gistinsert had checkUnique, it was just ifdef'd out because there was no code to enforce it... and as such, during bootstrap it was marked as amcanunique = false. Would it be that hard to enable it?On 1/18/06, Martijn van Oosterhout kleptog@svana.org wrote:Hi,Currently due to the way unique constraints are tied to btree there is no way to allow GiST indexes to do the same thing. The thing I'mspecifically interested in is an index where you insert ranges(start,end) and if unique, the index will complain if they overlap. Asa side-effect, this may make progress toward the goal of deferrable unique indexes.Part of the solution is to remove the layering violation from the btreecode, it really shouldn't be accessing the heap directly. What I'mproposing is to move the bulk of _bt_check_unique into a new function (say check_unique_index) in the general index machinary and have theb-tree code do just:check_unique_index( ctid of inserting tuple, ctid of possibly conflicting tuple)The point being that GiST indexes could use exactly the same function to check for duplicates. The function would return InvalidTransactionIdif there's no conflict, or an actual transaction id to wait on, justlike the btree code does now.It would require some changes to the GiST code since a lot more of the index may need to be checked for duplicates. I suppose in the generalcase, since a key can appear in multiple places, the concurrency issuescould be difficult. I suppose you would insert your key first, then check for duplicates thus ensuring that at least one of the twoconflicting transactions will see it.Now, one side-effect is that you could build deferrable uniqueconstraints on top of this by having the check function always return InvalidTransactionId but storing the conflicts for later checking. ButI first want to know if there are any real issues with the above.Any thoughts?--Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFDzkmWIB7bNG8LQkwRArgRAJ9E34krswmsSEsMv6h/1d1KJc7crACgg1kpm32u4QtjXCqd53fjUP6WKUE= =E0+I-END PGP SIGNATURE-
Re: [HACKERS] Surrogate keys (Was: enums)
Leandro Guimarães Faria Corcete DUTRA [EMAIL PROTECTED] writes: Greg Stark gsstark at mit.edu writes: I hate knee-jerk reactions too, but just think of all the pain of people dealing with databases where they used Social Security numbers for primary keys. I would never use an attribute that represents some real-world datum as a primary key any more. I am not familiar with the situation. The US gov't handed out unique numbers to every worker for their old age pension program. Many early database designers thought that made a wonderful natural primary key. It turns out that: a) not everyone has a social insurance number: when their business expanded to include foreign nationals these databases had to make up fake social insurance numbers. b) Occasionally people's social insurance numbers change, either because they got it wrong in the first place or because of identity theft later on. Even dealing with it changing isn't good enough because the old records don't disappear; the person essentially has *two* social insurance numbers. c) For security reasons it turns out to be a bad idea to be passing around social insurance numbers in the first place. So these database designers had a major problem adapting when people started refusing to give them social insurance numbers or complaining when their application leaked their social insurance number. In short, what seemed like the clearest possible example of a natural primary key became a great example of how hard it is to deal with changing business requirements when you've tied your database design to the old rules. Using natural primary keys makes an iron-clad design assumption that the business rules surrounding that datum will never change. And the one thing constant in business is that business rules change. In the past I've used username as a primary key for a users table, what could be safer? Later we had to create a sequence generated userid column because some data partners couldn't handle an text column without corrupting it. And of course one day the question arose whether we could handle someone wanting to change their username. Then another day we were asked whether we could have two different people with the same username if they belonged to separate branded subsites. -- greg ---(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] Unique constraints for non-btree indexes
On Wed, Jan 18, 2006 at 09:15:04AM -0500, Jonah H. Harris wrote: I thought gistinsert had checkUnique, it was just ifdef'd out because there was no code to enforce it... and as such, during bootstrap it was marked as amcanunique = false. Would it be that hard to enable it? Well, it has the argument to gistinsert but it is commented out and there is no other reference to unique anywhere in the GiST code. Once the support infrastructure is there we can talk about enabling it. At the very least we need to decide how to indicate what unique is. For example: saying the two ranges (1,3) and (2,4) cannot co-exist in the same index is not really what most people would consider the behaviour of a unique index. Indeed, for any particular data-type, there may be multiple ways of defining a conflict. For 2-D objects it may refer to having no objects overlap, but it could also refer to no overlaps in the X or Y axes. I guess what you're talking about is a constrained index, of which a unique index is just a particular type. I suppose the actual constraint would be one of the operators defined for the operator class (since whatever the test is, it needs to be indexable). Although some would obviously be more useful than others... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Unique constraints for non-btree indexes
I think I understand what you're saying, just that I don't think the btree index has anything to do with it.The extensibility is there for indexes to handle uniques in any way they choose. If you wanted to add a common unique index checking function for GIST, I'd just add it to GIST. It just seems to me like the access methods should keep the handling internal to themselves. On the chance that I'm not be understanding what you're saying, sorry.On 1/18/06, Martijn van Oosterhout kleptog@svana.org wrote: On Wed, Jan 18, 2006 at 09:15:04AM -0500, Jonah H. Harris wrote: I thought gistinsert had checkUnique, it was just ifdef'd out because there was no code to enforce it... and as such, during bootstrap it was marked as amcanunique = false.Would it be that hard to enable it? Well, it has the argument to gistinsert but it is commented out andthere is no other reference to unique anywhere in the GiST code. Oncethe support infrastructure is there we can talk about enabling it. At the very least we need to decide how to indicate what unique is.For example: saying the two ranges (1,3) and (2,4) cannot co-exist inthe same index is not really what most people would consider the behaviour of a unique index. Indeed, for any particular data-type,there may be multiple ways of defining a conflict. For 2-D objects itmay refer to having no objects overlap, but it could also refer to no overlaps in the X or Y axes.I guess what you're talking about is a constrained index, of which aunique index is just a particular type. I suppose the actual constraintwould be one of the operators defined for the operator class (since whatever the test is, it needs to be indexable). Although some wouldobviously be more useful than others...Have a nice day,--Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFDzm26IB7bNG8LQkwRAiUCAJ9MURp34CmKaxWFPrESKqvx2DDsYQCePSLvJrKzcRQU7wf25oDv42Oeosc= =y0WG-END PGP SIGNATURE-
Re: [HACKERS] debug_query_string and multiple statements
Yep, I couldn't find a better way to do it when I added debug_query_string long ago. Unless we go to a lot of work to parse the string, we could end up with something worse than we have now. --- Neil Conway wrote: While reviewing Joachim Wieland's patch to add a pg_cursors system view, I noticed that the patch assumes that debug_query_string contains the portion of the submitted query string that corresponds to the SQL statement we are currently executing. That is incorrect: debug_query_string contains the *entire* verbatim query string sent by the client. So if the client submits the query string SELECT 1; SELECT 2;, debug_query_string will contain exactly that string. (psql actually splits queries like the above into two separate FE/BE messages -- to see what I'm referring to, use libpq directly, or start up a copy of the standalone backend.) This makes debug_query_string the wrong thing to use for the pg_cursors and pg_prepared_statements views, but it affects other parts of the system as well: for example, given PQexec(conn, SELECT 1; SELECT 2/0;) and log_min_error_statement = 'error', the postmaster will log: ERROR: division by zero STATEMENT: SELECT 1; SELECT 2/0; which seems misleading, and is inconsistent with the documentation's description of this configuration parameter. Admittedly this isn't an enormous problem, but I think the current behavior isn't ideal. Unfortunately I don't see an easy way to fix this. It might be possible to extra a semicolon separated list of query strings from the parser or lexer, but that would likely have the effect of munging comments and whitespace from the literal string submitted by the client, which seems the wrong thing to do for logging purposes. An alternative might be to do a preliminary scan to look for semicolon delimited query strings, and then pass each of those strings into the raw_parser() separately, but that seems quite a lot of work (and perhaps a significant runtime cost) to fix what is at worst a minor UI wrinkle. Thoughts? -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Bad estimate on LIKE matching
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote: Any way to teach the planner about this? In a recent thread on -perform, I opined that this case could best be solved by using dynamic random block sampling at plan time followed by a direct evaluation of the LIKE against the sample. This would yield a more precise selectivity and lead to the better plan. So it can be improved for the next release. I find it exceedingly improbable that we'll ever install any such thing. On-the-fly sampling of enough rows to get a useful estimate would increase planning time by orders of magnitude --- and most of the time the extra effort would be unhelpful. In the particular case exhibited by Magnus, it is *really* unlikely that any such method would do better than we are doing now. He was concerned because the planner failed to tell the difference between selectivities of about 1e-4 and 1e-6. On-the-fly sampling will do better only if it manages to find some of those rows, which it is unlikely to do with a sample size less than 1e5 or so rows. With larger tables the problem gets rapidly worse. regards, tom lane ---(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] Surrogate keys (Was: enums)
On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimar??es Faria Corcete DUTRA wrote: b) If each parent record will have many children, the space savings from using a surrogate key can be quite large Not such a common case. Hmmm... Many blog entries per user... Many blog comments per entry Many PO's per customer... many line items per PO... Etc., etc. I would argue that one-many relationships are far more common than one-one, and it's very common for an integer ID to be a more compact representation than a real key. c) depending on how you view things, putting actual keys all over the place is denormalized How come? Never! Huh? One of the tenants of normalization is that you don't repeat data. You don't use customer name in your PO table, because it's asking for problems; what if a customer changes names (as just one example). Generally, I just use surrogate keys for everything unless performance dictates something else. What I am proposing is the reverse: use natural keys for everything unless performance dictates something else. In support of my PoV: http://blogs.ittoolbox.com/database/soup/archives/007327.asp?rss=1 Read the bottom of it: I am not saying that you should avoid autonumber surrogate keys like an SCO executive. The danger is not in their use but in their abuse. The events_id column in the events table didn't give us any trouble until we began to rely on it as the sole key for the table. The accounting application gave us problems because we were using the ID as the entire handle for the records. That crossed the line from use to misuse, and we suffered for it. To paraphrase, the issue isn't that surrogate keys were used for RI; the issue is that proper keys were not setup to begin with. Does it make sense to have a customer table where customer_name isn't unique? Almost certainly not. But that's just one possible constraint you might put on that table. To put words in Josh's mouth, the issue isn't with using a surrogate key, it's with not thinking about what constraints you should be placing on your data. Take a look at cbk's comment; he does a great job of summing the issue up. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] log_min_messages and debug levels
Hi, in my machine (win xp) i was trying to start psql (8.1.1) with log_min_messages to debug5 (just to see the messages :) but even the service start i cannot use psql nor pgadmin i receive an error of server closed the connection unexpectedly postgres=# select version(); version -- PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) (1 fila) Sorry, my postgres is in spanish but maybe you can recognize the message... ;) C:\Archivos de programa\PostgreSQL\8.1\binpsql -U postgres pruebas psql: el servidor ha cerrado la conexión inesperadamente, probablemente porque terminó de manera anormal antes o durante el procesamiento de la petición. is this expected on windows platforms? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] No heap lookups on index
Allow me a brief introduction. I work in a company who contracts intelligence analysis software to the government. We are currently developing a product which is using PostgreSQL at it's core. Due to the licensing of the product and the integration with perl this is our first choice in database solutions. We are, however, currently stuck. We are storing millions of rows and require very high query performance. We have spent the last several months tweaking, list lurking and researching all the various tweaks and performance enhancements and have come to the conclusion that our biggest slowdown is validating the index rows which match our selection criteria against the heap values. In general cases there is a very small amount required for this, but in our extreme use cases we are finding this to slow our queries by an unacceptable amount of time. We would like to resolve this issue. In that endeavor we have done some feasibility analysis (either to write a patch ourselves or attempt to commission an expert to do so), starting with the archives for this list. We found several posts discussing the issue and it seems that the complexity of storing the tuple visibility information inside of the index rows is prohibitive for simple indexes. I have used SQL Server in the past and have noticed that bookmark lookups are avoided because they force the query executor to actually fetch the data page off of disk, rather then return the values that exist in the index. I have verified times against the PostgreSQL installation and SQL Server to verify that the SQL Server queries come back at roughly the same speed when avoiding bookmark lookups as Postgres queries accessing clustered tables using the index the table is clustered on. Since I am sure everyone is tired of the intro by now, I'll get to the questions: Do commercial databases implement MVCC in a way that allows an efficient implementation of index lookups that can avoid heap lookups? Is there any way to modify PostgreSQL to allow index lookups without heap validation that doesn't involve re-writing the MVCC implementation of keeping dead rows on the live table? Is the additional overhead of keeping full tuple visibility information inside of the index so odious to the Postgres community as to prevent a patch with this solution from being applied back to the head? Maybe as an optional use feature? We would prefer this solution for our needs over the bitmap of heap pages listed in the TODO list because we want to ensure optimal query times, regardless of the state of the cache and because we are concerned with performance in the face of concurrent updates on the page level. Thanks for any thoughts on this, I know this is a perennial topic, but we are seriously considering contributing either code or money to the solution of this problem. David Scott Applied Technical Systems, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] No heap lookups on index
David Scott [EMAIL PROTECTED] writes: Is the additional overhead of keeping full tuple visibility information inside of the index so odious to the Postgres community as to prevent a patch with this solution from being applied back to the head? This has been discussed and rejected before (multiple times). If you want it considered you'll have to present stronger arguments than have so far been made. The current consensus is that the probability of a net performance win is not good enough to justify the large amount of development effort that would be required. What sort of problems are you dealing with exactly? There has been some discussion of changes that would improve certain scenarios. For instance it might be plausible to do joins using index information and only go back to the heap for entries that appear to pass the join test. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Bad estimate on LIKE matching
On Wed, 2006-01-18 at 10:37 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote: Any way to teach the planner about this? In a recent thread on -perform, I opined that this case could best be solved by using dynamic random block sampling at plan time followed by a direct evaluation of the LIKE against the sample. This would yield a more precise selectivity and lead to the better plan. So it can be improved for the next release. I find it exceedingly improbable that we'll ever install any such thing. On-the-fly sampling of enough rows to get a useful estimate would increase planning time by orders of magnitude --- and most of the time the extra effort would be unhelpful. In the particular case exhibited by Magnus, it is *really* unlikely that any such method would do better than we are doing now. He was concerned because the planner failed to tell the difference between selectivities of about 1e-4 and 1e-6. On-the-fly sampling will do better only if it manages to find some of those rows, which it is unlikely to do with a sample size less than 1e5 or so rows. With larger tables the problem gets rapidly worse. Your reply seems too strong; I wish to discuss further improvements, not fight. My willingness to do this is inspired by the years of excellent work that you and others have already contributed. I am attempting to provide a solution to the general problem. My way of doing this is to draw on my experience, just as I would draw upon any other body of knowledge such as academic papers or experimental results. My thinking is that perhaps Teradata, Oracle and DB2 were right to implement dynamic sampling for queries. Many things done elsewhere are wasted filigree, yet some are appropriate ideas that we are free to use. Accuracy need not be our goal, but a not-higher-than selectivity might allow us to avoid the worst case behaviour displayed here. On Wed, 2006-01-11 at 09:07 +, Simon Riggs wrote: On Tue, 2006-01-10 at 22:40 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I meant use the same sampling approach as I was proposing for ANALYZE, but do this at plan time for the query. That way we can apply the function directly to the sampled rows and estimate selectivity. I think this is so unlikely to be a win as to not even be worth spending any time discussing. The extra planning time across all queries will vastly outweigh the occasional improvement in plan choice for some queries. Extra planning time would be bad, so clearly we wouldn't do this when we already have relevant ANALYZE statistics. I would suggest we do this only when all of these are true - when accessing more than one table, so the selectivity could effect a join result - when we have either no ANALYZE statistics, or ANALYZE statistics are not relevant to estimating selectivity, e.g. LIKE - when access against the single table in question cannot find an index to use from other RestrictInfo predicates I imagined that this would also be controlled by a GUC, dynamic_sampling which would be set to zero by default, and give a measure of sample size to use. (Or just a bool enable_sampling = off (default)). This is mentioned now because the plan under consideration in this thread would be improved by this action. It also isn't a huge amount of code to get it to work. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] No heap lookups on index
David,You can find some of this discussion in Much Ado About COUNT(*). Related to that discussion, I had written a patch which added visibility information to the indexes.If you're interested in the patch and/or consulting, contact me offline. -JonahOn 1/18/06, Tom Lane [EMAIL PROTECTED] wrote: David Scott [EMAIL PROTECTED] writes: Is the additional overhead of keeping full tuple visibility information inside of the index so odious to the Postgres community as to prevent a patch with this solution from being applied back to the head?This has been discussed and rejected before (multiple times).If youwant it considered you'll have to present stronger arguments than have so far been made.The current consensus is that the probability of anet performance win is not good enough to justify the large amount ofdevelopment effort that would be required.What sort of problems are you dealing with exactly?There has been some discussion of changes that would improve certain scenarios.Forinstance it might be plausible to do joins using index information andonly go back to the heap for entries that appear to pass the join test. regards, tom lane---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Unique constraints for non-btree indexes
Martijn van Oosterhout kleptog@svana.org writes: check_unique_index( ctid of inserting tuple, ctid of possibly conflicting tuple) I agree it's pretty ugly to have the index AM directly poking into the heap, but adding a level of subroutine doesn't really make that a whole lot nicer :-(. In any case, you've underestimated the amount of coupling here: if the conflicting tuple is dead, _bt_check_unique also wants to know just how dead it is, so it can possibly set LP_DELETE on the old index entry. Now, one side-effect is that you could build deferrable unique constraints on top of this by having the check function always return InvalidTransactionId but storing the conflicts for later checking. I think this is not as easy as all that; consider race conditions against VACUUM for instance (the tuples might not be there anymore when you want to check the conflict). Also, we really do want to go back and set LP_DELETE if the conflict tuple is sufficiently dead. Having that not happen is unappetizing, because you could end up repeating the check a large number of times over successive updates. N updates will take O(N^2) time. My own thoughts about deferred unique checks have been along the lines of storing the possibly-conflicting key value when the initial check notes a problem, and then repeating the index search at commit. regards, tom lane ---(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] Unique constraints for non-btree indexes
Martijn van Oosterhout kleptog@svana.org writes: I guess what you're talking about is a constrained index, of which a unique index is just a particular type. I suppose the actual constraint would be one of the operators defined for the operator class (since whatever the test is, it needs to be indexable). Although some would obviously be more useful than others... I think the generalization that would be appropriate for GIST is that a unique index guarantees there are no two entries x, y such that x ~ y, where ~ is some boolean operator nominated by the opclass. We'd probably have to insist that ~ is commutative (x ~ y iff y ~ x). Concurrent insertion into a unique GIST index seems a bit nasty. In btree we can identify a unique page to lock for any given key value to ensure that no one else is concurrently inserting a conflicting key, thus usually allowing concurrent insertions of different keys. But I don't see how you do that for an arbitrary ~ operator. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unique constraints for non-btree indexes
On Wed, Jan 18, 2006 at 04:10:16PM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: check_unique_index( ctid of inserting tuple, ctid of possibly conflicting tuple) I agree it's pretty ugly to have the index AM directly poking into the heap, but adding a level of subroutine doesn't really make that a whole lot nicer :-(. Well, the raionale is that in theory the same logic would be applied for GiST indexes, so it'd be nice to do it in one place rather than repeat it for each index AM. In any case, you've underestimated the amount of coupling here: if the conflicting tuple is dead, _bt_check_unique also wants to know just how dead it is, so it can possibly set LP_DELETE on the old index entry. Hmm, ok. There's more info that the index AM would like, but the same info would be required for both GiST and b-tree, no? (assuming GiST has the same delete optimisation) My own thoughts about deferred unique checks have been along the lines of storing the possibly-conflicting key value when the initial check notes a problem, and then repeating the index search at commit. Well, I didn't want to exclude that possibility. Racing with VACUUM is a tricky one. If we keep an array of ctid in memory, we need to know if VACUUM removes one of them. OTOH, the recheck will then return either a blank or a tuple which definitly doesn't match. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Unique constraints for non-btree indexes
On Wed, Jan 18, 2006 at 04:18:10PM -0500, Tom Lane wrote: I think the generalization that would be appropriate for GIST is that a unique index guarantees there are no two entries x, y such that x ~ y, where ~ is some boolean operator nominated by the opclass. We'd probably have to insist that ~ is commutative (x ~ y iff y ~ x). Commutative, that's the criteria I was looking for. To be senseble for this purpose, the operator has to be commutative (the commutator is itself). This works for b-tree by including = and excluding and . Similarly for GiST indexes, contains no, overlaps yes. That's a fairly easy test. Concurrent insertion into a unique GIST index seems a bit nasty. In btree we can identify a unique page to lock for any given key value to ensure that no one else is concurrently inserting a conflicting key, thus usually allowing concurrent insertions of different keys. But I don't see how you do that for an arbitrary ~ operator. Well, the best I could come up with was to just do the insert for value X and then do a full index scan for X across the given constraint operator (~). Any matches would need to go through the check_unique_index function as defined earlier. The only issue is that we can't really do easy optimisation. In the case of deferred constraints you can't even remember where in the tree you were because new keys could be added anywhere so you always have to do from the top. The issue I get is deadlocks: 1. Process A inserts value X 2. Process B inserts value Y (where X ~ Y is true) 3. Process A begins scan, finds Y and waits for B 4. Process B begins scan, finds X and waits for A Oops. The only way I can think of solving that is by marking the entries tentative until the scan is complete and provide a way of resolving conflicts between two tentative entries. Requires more thinking. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] No heap lookups on index
On Wed, 2006-01-18 at 12:14 -0800, David Scott wrote: Is the additional overhead of keeping full tuple visibility information inside of the index so odious to the Postgres community as to prevent a patch with this solution from being applied back to the head? Maybe as an optional use feature? You might want to consider the thought of organised heaps as an alternative thought to index improvements. That way there is no heap to avoid visiting because the index is also the main data structure. Teradata provides hash or value-ordered tables Oracle offers index organised tables DB2 offers multi-dimensional clustering Tandem offered value ordered tables This would offer performance, but would be one of the largest patches seen in recent times. You may find some co-backers. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unique constraints for non-btree indexes
Martijn van Oosterhout kleptog@svana.org writes: On Wed, Jan 18, 2006 at 04:18:10PM -0500, Tom Lane wrote: In btree we can identify a unique page to lock for any given key value to ensure that no one else is concurrently inserting a conflicting key, thus usually allowing concurrent insertions of different keys. But I don't see how you do that for an arbitrary ~ operator. The issue I get is deadlocks: Right, the deadlock risk is exactly the reason you need some secret sauce or other. Btree's page-level lock ensures that two insertions of conflicting keys can't overlap (even if they ultimately get stored on different pages). That's not the only way to fix this but it's a pretty good way. BTW, the deadlock risk also applies to deferred uniqueness checks. Again, in btree it's possible to avoid this if you do a fresh indexscan (and take a lock on the first scanned page while you do that). If you try to do it without consulting the index then you need some other way to break ties. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] No heap lookups on index
On Wed, Jan 18, 2006 at 12:14:12PM -0800, David Scott wrote: Do commercial databases implement MVCC in a way that allows an efficient implementation of index lookups that can avoid heap lookups? Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. Every update/delete means two seperate writes to disk, one for the base table and one for the undo log (well, there's also the redo log, equivalent to our WAL). Though writes to undo can (and presumably are) grouped together, so they should normally be a lot more efficient than the updates to the base table unless you're updating data in table order. Of course there's downsides to our MVCC as well; the cost of index scans is just one. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] No heap lookups on index
On Wed, Jan 18, 2006 at 04:02:45PM -0500, Jonah H. Harris wrote: David, You can find some of this discussion in Much Ado About COUNT(*). Related to that discussion, I had written a patch which added visibility information to the indexes. If you're interested in the patch and/or consulting, contact me offline. Does the patch change all indexes across the board? Do you have any performance numbers? I suspect that in some situations storing visibility info in the index would be a big win; if that's the case it would be very good if there was an option that allowed it. Perhaps this could be done using a different index access method... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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
FW: [HACKERS] Surrogate keys (Was: enums)
Ooops, fat-finger'd -hackers... -Original Message- Adding -hackers back to the list. From: Leandro Guimarães Faria Corcete Dutra Em Seg, 2006-01-16 às 12:52 -0600, Jim C. Nasby escreveu: On Sat, Jan 14, 2006 at 07:28:21PM +0900, Michael Glaesemann wrote: For UPDATEs and INSERTs, the proper primary key also needs to be checked, but keys are used for more than just checking uniqueness: they're also often used in JOINs. Joining against a single integer I'd think it quite a different proposition (I'd think faster in terms of performance) than joining against, say, a text column or a composite key. How different is that? Comparing two ints is much, much faster than comparing two text fields. For a small number of comparisons, it doesn't matter. When you're joining tables together, it's a different story. a) the optimizer does a really poor job on multi-column index statistics Then it should eventually be fixed? It's on the to-do, but it's not an easy nut to crack. b) If each parent record will have many children, the space savings from using a surrogate key can be quite large Only where the surrogate is significantly smaller than the natural? #define significant Here's a real-life example: the primary table for stats.distributed.net has about 120M rows. One field in that table (participant_id) links back to the participant table; it's an int. If instead we used participant_name and that averaged 8 characters in length, that would grow the main table by 1GB (8 chars takes 8 bytes instead of 4, plus there's the varlena header of 4 bytes). The machine that stats runs on has 4G of memory, so cutting 1G of wasted space out of that table helps quite a bit. (In actuality, there isn't participant_name... participants are identified by email address (not a great idea, but I wasn't around when that was chosen). As you can imagine, email addresses are substantially longer than 4 bytes. When we normalized email out of that main table things got substantially faster. That was a number of years ago, so the table was probably 15-25% of it's current size, but it still made a huge difference.) c) depending on how you view things, putting actual keys all over the place is denormalized How come? See my other reply... :) Generally, I just use surrogate keys for everything unless performance dictates something else. Shouldn't it be the other way round, for the user's sake? Why should it? It's trivial to create views that abstract surrogate keys out, and if you really want to you can even make the views updatable. But here's two other things to consider: In many cases you can't define a single field as a unique key. So you end up with having to add many extra keys to all your join clauses. Not very friendly, and prone to error. Not every language has equal support for text comparisons (and in my experience, almost all real keys are mostly text). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgxs/windows
Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: dllwrap doesn't seem to get given LDFLAGS, and maybe doesn't honor it either. I wouldn't expect it to handle everything that might appear in LDFLAGS, but maybe it ought to be given the -L items from LDFLAGS (compare the way we copy just those items into SHLIB_LINK). What's confusing me at the moment is that it seems to work for Magnus. Are you maybe working from different source trees? I believe we changed around the handling of these switches recently. Maybe in his copy, the -L items from LDFLAGS are making it into the dllwrap command via SHLIB_LINK. I am working against 8.1 from the installer - he is working against a local mingw install. Also, he might be working from a later toolset - I have gcc3.2.4 while gcc 3.4.2 is the latest mingw release - some other tools might also be mildly out of date. Could this be related to the fact that pre-8.2 makefiles were not space-safe? I am unsure how pgxs worked on Win32 without being space-safe. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] No heap lookups on index
Simon Riggs [EMAIL PROTECTED] writes: You might want to consider the thought of organised heaps as an alternative thought to index improvements. That way there is no heap to avoid visiting because the index is also the main data structure. This would offer performance, but would be one of the largest patches seen in recent times. You may find some co-backers. Either way it would be a pretty monstrous patch :-( ... in this case because of the amount of code that knows about the properties of heap storage, and in what David is thinking about because of the implications of trying to keep multiple copies of tuple state up-to-date. We'd probably end up with a cleaner system structure if we tried to create an API separating out the knowledge of heap structure, but the amount of work needed seems out of proportion to the benefit. It might be possible to compromise though. Imagine an index that contains only the upper levels of a search tree --- links to what would be the leaf level point into the associated heap. In this design the heap is still a heap in the sense that you can seqscan it without any awareness of the index structure. What you can't do is insert tuples or move them around without the index AM's say-so. RelationGetBufferForTuple would become an index AM call, but otherwise I think the impact on existing code wouldn't be large. There are some limitations. For instance I don't think that the index AM could control the order of items within a heap page, because of the need for TIDs to be persistent; so within-page searches would still be kinda slow. But it's interesting to think about. regards, tom lane ---(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: FW: [HACKERS] Surrogate keys (Was: enums)
Comparing two ints is much, much faster than comparing two text fields. For a small number of comparisons, it doesn't matter. When you're joining tables together, it's a different story. That is where data independence would come handy... like a better enum, with possreps and hidden implementation. Forgive me my ignorance, but are ints inherently faster to compare than strings, or is it just an implementation detail? Ideally, if this is so a fully data-independent system would create a hash behind the back of user in order to get performance. The CPU can do an integer comparison with one instruction; it can't do that with a text string. (Well, theoretically if the string was 3/4 bytes exactly (dependin on null termination) you could compare just as fast, but I'm pretty certain that no compiler is that fancy.) Here's a real-life example: the primary table for stats.distributed.net has about 120M rows. One field in that table (participant_id) links back to the participant table; it's an int. If instead we used participant_name and that averaged 8 characters in length, that would grow the main table by 1GB (8 chars takes 8 bytes instead of 4, plus there's the varlena header of 4 bytes). The machine that stats runs on has 4G of memory, so cutting 1G of wasted space out of that table helps quite a bit. OK, hardly a typical example. As I think I left clear, my problem is not using surrogate keys, but using them by default, or even exclusively. No? It's certainly not uncommon to have tables with 100M+ rows. And keep in mind that this applies to every row of every table that has foreign keys. I'd bet it's actually common to save 1G or more with surrogate keys in moderately sized databases. Of course, you do have to be intelligent here, too. The only key defined on the table in my example is participant_id, project_id, date; there is no surrogate key because there's no real reason to have one. (In actuality, there isn't participant_name... participants are identified by email address (not a great idea, but I wasn't around when that was chosen). As you can imagine, email addresses are substantially longer than 4 bytes. When we normalized email out of that main table things got substantially faster. That was a number of years ago, so the table was probably 15-25% of it's current size, but it still made a huge difference.) This isn't normalisation at all, as far as I understand it. It is just I don't have the rules of normalization memorized enough to know what form this breaks, but I'm 99% certain it breaks at least one of them. Look at it this way: if someone wants to change their email address, best case scenario is that you have cascading RI setup and it updates thousands of rows in that table. Worst case scenario, you just de-linked a whole bunch of data. But with a surrogate key, all you have to do is update one row in one table and you're done. that we don't have data independence... so you had to expose an implementation detail? Expose to what? The application? First, this is a pretty minor thing to expose; second, if it's that big a concern you can completely hide it by using a view. But the reality is, dealing with a numeric ID can be a heck of a lot easier than an email address. Look at URLs that embbed one versus the other for a good example. Why should it? It's trivial to create views that abstract surrogate keys out, and if you really want to you can even make the views updatable. But here's two other things to consider: These views, in heavy querying environments, can be prohibitive. Normalize 'til it hurts; denormalize 'til it works. Yes, the added overhead of rules for updates/inserts/deletes could start to add up in performance-critical code. But if performance is that critical you're far more likely to run into other bottlenecks first. And worst-case, you abstract behind a stored procedure that just has the right queries hard-coded. As for select-only views you'll have a hard time showing any meaningful performance penalty. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Surrogate keys (Was: enums)
Martjin, Interesting. However, in my experience very few things have natural keys. There are no combination of attributes for people, phone calls or even real events that make useful natural keys. I certainly hope that I never have to pick up one of your projects. A table without a natural key is a data management disaster. Without a key, it's not data, it's garbage. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Surrogate keys (Was: enums)
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: Wednesday, January 18, 2006 3:59 PM To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Surrogate keys (Was: enums) Martjin, Interesting. However, in my experience very few things have natural keys. There are no combination of attributes for people, phone calls or even real events that make useful natural keys. I certainly hope that I never have to pick up one of your projects. A table without a natural key is a data management disaster. Without a key, it's not data, it's garbage. I have a different opinion. The data should absolutely never use a natural key as a primary key. The data should use something like a sequence for the primary key. Examples: SSN -- believe it or not, SSN's sometimes change. First, Middle, Last names -- Not really unique Street Address -- More than one person can live there. They can move. Basically, every physical attribute or logical attribute is a terrible choice for a primary key. They won't cause problems very often, it's true. But when they do cause problems, it is a terrible doozie of a problem. Now, on the other hand, if we are talking about INDEXES here, that's a horse of a different color. Lots of natural attributes and combinations of natural attributes make excellent candidates for keys. Such things as SSN, names, addresses, phone numbers, etc. Therefore, I am guessing the two posters upstream in this thread that I am responding to were therefore talking about different subjects altogether. One was talking about using natural attributes for indexes, which is a superior idea that I agree with. The other was talking about never using natural attributes for keys, which I also agree with. Therefore, I am guessing that everyone is in complete agreement, but it is a nomenclature thing. Just a guess. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Surrogate keys (Was: enums)
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Dann Corbit Sent: Wednesday, January 18, 2006 4:04 PM To: josh@agliodbs.com; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Surrogate keys (Was: enums) -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: Wednesday, January 18, 2006 3:59 PM To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Surrogate keys (Was: enums) Martjin, Interesting. However, in my experience very few things have natural keys. There are no combination of attributes for people, phone calls or even real events that make useful natural keys. I certainly hope that I never have to pick up one of your projects. A table without a natural key is a data management disaster. Without a key, it's not data, it's garbage. I have a different opinion. The data should absolutely never use a natural key as a primary key. The data should use something like a sequence for the primary key. Examples: SSN -- believe it or not, SSN's sometimes change. First, Middle, Last names -- Not really unique Street Address -- More than one person can live there. They can move. Basically, every physical attribute or logical attribute is a terrible choice for a primary key. They won't cause problems very often, it's true. But when they do cause problems, it is a terrible doozie of a problem. Now, on the other hand, if we are talking about INDEXES here, that's a horse of a different color. Lots of natural attributes and combinations of natural attributes make excellent candidates for keys. Make that: combinations of natural attributes make excellent candidates for indexes. See. I even messed it up, when I was trying to highlight the distinction. Of course, we can probably just chalk that up to dumb as a box of hammers. Such things as SSN, names, addresses, phone numbers, etc. Therefore, I am guessing the two posters upstream in this thread that I am responding to were therefore talking about different subjects altogether. One was talking about using natural attributes for indexes, which is a superior idea that I agree with. The other was talking about never using natural attributes for keys, which I also agree with. Therefore, I am guessing that everyone is in complete agreement, but it is a nomenclature thing. Just a guess. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] No heap lookups on index
On Wed, 2006-01-18 at 18:27 -0500, Tom Lane wrote: Imagine an index that contains only the upper levels of a search tree --- links to what would be the leaf level point into the associated heap. In this design the heap is still a heap in the sense that you can seqscan it without any awareness of the index structure. What you can't do is insert tuples or move them around without the index AM's say-so. RelationGetBufferForTuple would become an index AM call, but otherwise I think the impact on existing code wouldn't be large. Eureka! I had been thinking of a block level index which sounds almost the same thing (as opposed to the row level indexes we have now). We only need to index the row with the lowest value on any page so the main index would get 100 times smaller. The main part of the index would not need to be written to except when a block overflows. I had imagined an ordering within a block to allow fast uniqueness checks, but it would be pretty fast either way. Merge joins with the same index become block-level joins without sorts. We would just do an individual block sort before merging, so no need for very large sort-merges. Even if the block level indexes differ, we only need to sort one of the tables. Hopefully we could avoid trying to support GIST-heaps? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgxs/windows
Bruce Momjian wrote: Could this be related to the fact that pre-8.2 makefiles were not space-safe? I am unsure how pgxs worked on Win32 without being space-safe. I don't see how. In fact, pgxs seems to use short form paths anyway. Example (from previous email): dllwrap -o rainbow.dll --def rainbow.def rainbow.o c:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/../../src/utils/dllinit.o -Lc:/PROGRA~1/POSTGR~1/8.1/bin -lpostgres No spaces there. The problem is it says bin instead of lib before -lpostgres. cheers andrew ---(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: FW: [HACKERS] Surrogate keys (Was: enums)
Em Qua, 2006-01-18 às 17:22 -0600, Jim C. Nasby escreveu: Forgive me my ignorance, but are ints inherently faster to compare than strings, or is it just an implementation detail? Ideally, if this is so a fully data-independent system would create a hash behind the back of user in order to get performance. The CPU can do an integer comparison with one instruction; it can't do that with a text string. OK. Again, data independence should be the goal here. OK, hardly a typical example. As I think I left clear, my problem is not using surrogate keys, but using them by default, or even exclusively. No? It's certainly not uncommon to have tables with 100M+ rows. No, but neither are they *that* common. Certainly, lots of database have a few of them. But then, they have dozens, hundreds, thousands of much smaller tables. And keep in mind that this applies to every row of every table that has foreign keys. I'd bet it's actually common to save 1G or more with surrogate keys in moderately sized databases. Only if you have quite some children, because otherwise, in the main tables, the surrogate keys add a field, an index and a sequence to an otherwise smaller table and index. Of course, you do have to be intelligent here, too. The only key defined on the table in my example is participant_id, project_id, date; there is no surrogate key because there's no real reason to have one. Quite. (In actuality, there isn't participant_name... participants are identified by email address (not a great idea, but I wasn't around when that was chosen). As you can imagine, email addresses are substantially longer than 4 bytes. When we normalized email out of that main table things got substantially faster. That was a number of years ago, so the table was probably 15-25% of it's current size, but it still made a huge difference.) This isn't normalisation at all, as far as I understand it. It is just I don't have the rules of normalization memorized enough to know what form this breaks, but I'm 99% certain it breaks at least one of them. No, never. Normalisation is about eliminating redundancy and, therefore, update anomalies. Making all the table dependent on only the keys and the whole keys, by projecting relations to eliminate entity mixups. What you mention is actually exposing an implementation detail, namely an integer that serves as a hash of the key. Look at it this way: if someone wants to change their email address, best case scenario is that you have cascading RI setup and it updates thousands of rows in that table. Worst case scenario, you just de-linked a whole bunch of data. But with a surrogate key, all you have to do is update one row in one table and you're done. OK, if you have lots of linked data. But most tables are really dead ends. that we don't have data independence... so you had to expose an implementation detail? Expose to what? The application? First, this is a pretty minor thing to expose; second, if it's that big a concern you can completely hide it by using a view. As someone said, you end up with ids everywhere, and no user-understandable data at all... But the reality is, dealing with a numeric ID can be a heck of a lot easier than an email address. Look at URLs that embbed one versus the other for a good example. Again, implementation details... levels mixup. Why should it? It's trivial to create views that abstract surrogate keys out, and if you really want to you can even make the views updatable. But here's two other things to consider: These views, in heavy querying environments, can be prohibitive. Normalize 'til it hurts; denormalize 'til it works. Lack of data implementation biting us again. Yes, the added overhead of rules for updates/inserts/deletes could start to add up in performance-critical code. But if performance is that critical you're far more likely to run into other bottlenecks first. And worst-case, you abstract behind a stored procedure that just has the right queries hard-coded. As for select-only views you'll have a hard time showing any meaningful performance penalty. Yet real user-defined data types could make it all much simpler. -- +55 (11) 5685 2219 xmpp:[EMAIL PROTECTED] +55 (11) 9406 7191 Yahoo!: lgcdutra +55 (11) 5686 9607 MSN: [EMAIL PROTECTED] +55 (11) 4390 5383 ICQ/AIM: 61287803 ---(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] No heap lookups on index
Simon Riggs [EMAIL PROTECTED] writes: Hopefully we could avoid trying to support GIST-heaps? Well, that would be an extra index AM that someone might or might not get around to writing someday. I was thinking that both btree and hash index AMs might be interesting for this, though. Hash in particular would adapt pretty trivially ... regards, tom lane ---(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] Surrogate keys (Was: enums)
On Jan 19, 2006, at 9:31 , Leandro Guimarães Faria Corcete Dutra wrote: OK. Again, data independence should be the goal here. snip / Again, implementation details... levels mixup. snip / Lack of data implementation biting us again. snip / Yet real user-defined data types could make it all much simpler. Again, again, and again, patches welcome! PostgreSQL is an open- source project, and people contribute in a variety of ways, two of which include submitting code and sponsoring others to develop code. If you look at the todo list, there are *lots* of things people would like to see improved in PostgreSQL, but the pace at which PostgreSQL is improved and what is improved is driven in large part by what people are willing to do themselves or sponsor. If these are things you're interested in (and it certainly appears you are), why not contribute? Michael Glaesemann grzm myrealbox com ---(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] No heap lookups on index
Simon Riggs [EMAIL PROTECTED] writes: We only need to index the row with the lowest value on any page so the main index would get 100 times smaller. The main part of the index would not need to be written to except when a block overflows. BTW, the above is equivalent to saying that the leaf-level index pages aren't there: the downlink pointers on the level-1 index pages are pointers to heap pages, instead, and you're right that they effectively only index the lowest value per page (actually IIRC the highest value per page, but same difference). I think the 100x figure is overoptimistic though. There will be a lot fewer entries per leaf page because actual heap tuples will be a lot larger than index entries (typically at least). Hence, you need more level-1 entries and so the upper index levels are bigger than in a simple index. Another point is that the heap will be somewhat bloated compared to a simple heap because of containing more unused space. The traditional rule-of-thumb is that a btree index is only about 2/3rds full at steady state, and I suppose this would apply to a btree-organized heap too. Still, it seems like an idea worth investigating. Merge joins with the same index become block-level joins without sorts. We would just do an individual block sort before merging, so no need for very large sort-merges. Even if the block level indexes differ, we only need to sort one of the tables. I'd phrase that a little differently: an indexscan on such an index would normally deliver unordered output, but you could demand ordered output and get it by doing successive one-page sorts. I doubt it's worth inventing a new layer of mergejoin code to do this rather than keeping it at the index access level. Come to think of it, the idea also seems to map nicely into bitmap index scans: the index will directly hand back a list of potential pages to look at, but they are all marked lossy because the index doesn't know exactly which tuple(s) on the target pages match the query. The existing bitmap-heap-scan code can take it from there. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] No heap lookups on index
Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. It's a good point though. Surely a database should be optimised for the most common operation - commits, rather than rollbacks? Chris ---(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] No heap lookups on index
On Thu, Jan 19, 2006 at 09:18:55AM +0800, Christopher Kings-Lynne wrote: Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. It's a good point though. Surely a database should be optimised for the most common operation - commits, rather than rollbacks? Generally true, but keep in mind this counter-argument... our MVCC performs fewer disk writes (since generally you can find some free space on the page you're modifying) and you can control when you take the hit of cleaning up dead space. In fact, you can take that hit at a reduced priority (vacuum_cost_*). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] No heap lookups on index
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. It's a good point though. Surely a database should be optimised for the most common operation - commits, rather than rollbacks? The shuffling off of the data is expensive in itself, so I'm not sure you can argue that the Oracle way is more optimal for commits either. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] No heap lookups on index
On Wed, Jan 18, 2006 at 08:13:59PM -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We only need to index the row with the lowest value on any page so the main index would get 100 times smaller. The main part of the index would not need to be written to except when a block overflows. BTW, the above is equivalent to saying that the leaf-level index pages aren't there: the downlink pointers on the level-1 index pages are pointers to heap pages, instead, and you're right that they effectively only index the lowest value per page (actually IIRC the highest value per page, but same difference). Would this open the door for allowing tables to be maintained in CLUSTER order (at least at the block level if not within the blocks)? Though I have no idea how you'd handle page splits without a lot of pain, but perhaps it would be possible to strive for a certain tuple ordering that would allow for a periodic re-cluster that doesn't have to move a lot of data. One thought is to strive for the same amount of free space on each page, so if you're touching a tuple on a page that has less than desired free space you move it's new version to either the next or previous page. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] No heap lookups on index
Jim C. Nasby [EMAIL PROTECTED] writes: Would this open the door for allowing tables to be maintained in CLUSTER order (at least at the block level if not within the blocks)? Though I have no idea how you'd handle page splits without a lot of pain I think the way you'd attack that is by building the table with a pretty low fill factor, so that there's room on each page for a number of updates before you have to split. Since the index AM is going to be dictating space allocation, this is all in its hands. The existing CLUSTER code would probably be totally inapplicable to this sort of organization --- we'd have to provide some alternate code path for index-organized heaps. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: FW: [HACKERS] Surrogate keys (Was: enums)
Maybe it goes better into Advocacy or something, but I have found a quote by
database big-wigs that I strongly disagree with:
From:
http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf
We have this.
PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be assigned by
the DBMS only if a user-defined primary key is not available.
Second generation systems support the notion of a primary key, which is a
user-assigned unique identifier. If a primary key exists for a collection that
is known never to change, for example social security number, student
registration number, or employee number, then no additional system-assigned UID
is required. An immutable primary key has an extra advantage over a
system-assigned unique identifier because it has a natural, human readable
meaning. Consequently, in data interchange or debugging this may be an
advantage. If no primary key is available for a collection, then it is
imperative that a system-assigned UID be provided. Because SQL supports update
through a cursor, second generation systems must be able to update the last
record retrieved, and this is only possible if it can be uniquely identified.
If no primary key serves this purpose, the system must include an extra UID.
Therefore, several second generation systems already obey this proposition.
Moreover, as will be noted in Proposition 2.3, some collections, e.g. views, do
not necessarily have system assigned UIDs, so building a system that requires
them is likely to be proven undesirable. We close our discussion on Tenet 1
with a final proposition that deals with the notion of rules.
This is a bad idea.
Let's take the example of a Social Security Number.
Not everyone has one:
http://www.ssa.gov/pubs/10002.html#how2
If people do have one, they can definitely change it. If someone has stolen a
SSN, then the wronged party is able to get their SSN changed:
http://101-identitytheft.com/ssn.htm
The odds of this happening are low, but if you cannot handle it, then the
damage caused is considerable.
Now what happens if you want to have customers outside of the USA? {Don't
worry, we'll never go global...} I hope that my objections are very plain and
obvious.
The primary key should be immutable, meaning that its value should not be
changed during the course of normal operations of the database. What natural
key is immutable? The answer is that such an attribute does not exist. To use
them for such a purpose is begging for trouble.
I saw the argument that there is a great volume of space wasted by adding a
column that does not naturally occur in the data. That argument is simply
absurd. Consider a database with 10 billion rows of data in it. Each of those
tables gets an 8 byte primary key added for every row, resulting in 80 GB
consumed. The cost of 80 GB is perhaps $200. With a database that large
(where the extra space consumed by an artificial key column has a cost that can
easily be measured) the odds of a problem arising due to a natural column
changing its value are huge. The cost of such a tragedy is certainly more than
the $200 pittance!
If there is an argument that we also have the parent key values propagated into
the child tables as foreign keys, that argument has no merit. The other
attribute that would have been chosen would also be propagated. And so (for
instance) there is no savings to propagating a SSN field into child tables
verses propagating an 8 byte integer.
I also saw an argument that the propagated ID values are confusing to
end-users. That is the fault of the database designer who game them a stupid
name. If they were things like InvoiceID and LineItemID then there will not be
the same sort of confusion. The meaning and purpose of the column is
immediately apparent. As an alternative, the ubiquitous OID name for a column
on a table is also very transparent. Of course, when it is used in a foreign
key, it must be given a role name to avoid confusion in that case.
At any rate, the use of natural keys is a mistake made by people who have never
had to deal with very large database systems.
IMO-YMMV.
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Leandro Guimarães Faria Corcete Dutra
Sent: Wednesday, January 18, 2006 4:31 PM
To: Jim C. Nasby
Cc: pgsql-hackers@postgresql.org
Subject: Re: FW: [HACKERS] Surrogate keys (Was: enums)
Em Qua, 2006-01-18 às 17:22 -0600, Jim C. Nasby escreveu:
Forgive me my ignorance, but are ints inherently faster to compare
than
strings, or is it just an implementation detail? Ideally, if this is
so
a fully data-independent system would create a hash behind the back of
user in order to get performance.
The CPU can do an integer comparison with one instruction; it can't do
that with a text string.
OK. Again, data independence should be the goal here.
OK, hardly a typical example. As I think
Re: [HACKERS] Surrogate keys (Was: enums)
On Jan 19, 2006, at 10:34 , Dann Corbit wrote: http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be assigned by the DBMS only if a user-defined primary key is not available. snip / An immutable primary key has an extra advantage over a system- assigned unique identifier because it has a natural, human readable meaning. Consequently, in data interchange or debugging this may be an advantage. If no primary key is available for a collection, then it is imperative that a system-assigned UID be provided. snip / Dann Corbit: The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. What natural key is immutable? The answer is that such an attribute does not exist. To use them for such a purpose is begging for trouble. As far as I can tell, the only difference between your position, Dann, and Date and Darwen's, is that you think no natural key is immutable. If you *could* find an immutable natural key, would it be an acceptable key for you? Date and Darwen say explicitly that if no immutable (natural) (primary) key is available a system-assigned UID is required. If you think there is no immutable natural key available, Darwen and Date would agree that you should use a system- generated key. Or do you think I'm misreading you or The Third Manifesto? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Surrogate keys (Was: enums)
-Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 18, 2006 5:48 PM To: Dann Corbit Cc: Leandro Guimarães Faria Corcete Dutra; Jim C. Nasby; pgsql- [EMAIL PROTECTED] Subject: Re: [HACKERS] Surrogate keys (Was: enums) On Jan 19, 2006, at 10:34 , Dann Corbit wrote: http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be assigned by the DBMS only if a user-defined primary key is not available. snip / An immutable primary key has an extra advantage over a system- assigned unique identifier because it has a natural, human readable meaning. Consequently, in data interchange or debugging this may be an advantage. If no primary key is available for a collection, then it is imperative that a system-assigned UID be provided. snip / Dann Corbit: The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. What natural key is immutable? The answer is that such an attribute does not exist. To use them for such a purpose is begging for trouble. As far as I can tell, the only difference between your position, Dann, and Date and Darwen's, is that you think no natural key is immutable. If you *could* find an immutable natural key, would it be an acceptable key for you? Date and Darwen say explicitly that if no immutable (natural) (primary) key is available a system-assigned UID is required. If you think there is no immutable natural key available, Darwen and Date would agree that you should use a system- generated key. Or do you think I'm misreading you or The Third Manifesto? If you could find an immutable natural key, it would be the *BEST* thing to use. Unfortunately, I believe that immutable natural keys are rarer than horse feathers and pickle smoke. Furthermore, because of statements like the one that I collected and pasted from the above document, I believe that people will choose totally inappropriate things (I have seen it many times and had to deal with the repercussions) to use as natural keys (e.g. SSN) and cause enormous damage through those choices. But I suppose on a sort of mathematical level the statement is fully true. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] TODO-Item: B-tree fillfactor control
Hi Hackers, I'm trying the following TODO item: [Indexes] - Add fillfactor to control reserved free space during index creation I have already made an patch and it seemed to work well. $ ./pgbench -i -s 10 # select relpages from pg_class where relname = 'accounts_pkey'; relpages | 2745( default PCTFree is 10% ) # set btree_leaf_free_percent = 0; # reindex index accounts_pkey; # select relpages from pg_class where relname = 'accounts_pkey'; relpages | 2475( - about 2745 * 0.9 = 2470.5 ) # set btree_leaf_free_percent = 30; # reindex index accounts_pkey; # select relpages from pg_class where relname = 'accounts_pkey'; relpages | 3537( - about 2745 * 0.9 / 0.7 = 3529.3 ) And now, I need advice on some issues. - Is it appropriate to use GUC variables to control fillfactors? Is it better to extend CREATE INDEX / REINDEX grammar? - Should indexes remember their fillfactors when they are created? The last fillfactors will be used on next reindex. - Is fillfactor useful for hash and gist indexes? I think hash does not need it, but gist might need it. Look forward to your comments. Thanks, --- ITAGAKI Takahiro NTT Cyber Space Laboratories btree_free_percent.patch Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] No heap lookups on index
On Thu, 2006-01-19 at 09:18 +0800, Christopher Kings-Lynne wrote: Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. It's a good point though. Surely a database should be optimised for the most common operation Yes. - commits, rather than rollbacks? Commits are most common because most databases are optimized for them. Lots of programs go through a ton pre-checking to avoid a rollback that they don't need to do under PostgreSQL. I've found that for small systems I tend to rely very heavily on frequent vacuums and database level exceptions for virtually all data checking. Rollbacks are nearly as common as commits in those environments if not more-so. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgxs/windows
Andrew Dunstan wrote: Bruce Momjian wrote: Could this be related to the fact that pre-8.2 makefiles were not space-safe? I am unsure how pgxs worked on Win32 without being space-safe. I don't see how. In fact, pgxs seems to use short form paths anyway. Example (from previous email): dllwrap -o rainbow.dll --def rainbow.def rainbow.o c:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/../../src/utils/dllinit.o -Lc:/PROGRA~1/POSTGR~1/8.1/bin -lpostgres No spaces there. The problem is it says bin instead of lib before -lpostgres. OK, thanks. Next question --- are the installed file locations the same for a MinGW install and a pginstaller install? I don't think pginstaller does a MinGW install because it doesn't have the build environment in the tarball. However, the big difference seems to be that Magnus has -Llib and -Lbin, while you have only the -Lbin. I have MinGW and pginstaller installed here. How can I set things up to test this? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: FW: [HACKERS] Surrogate keys (Was: enums)
Dann, The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. Why? I don't find this statement to be self-evident. Why would we have ON UPDATE CASCADE if keys didn't change sometimes? At any rate, the use of natural keys is a mistake made by people who have never had to deal with very large database systems. Oh, I guess I'm dumb then. The biggest database system I ever had to deal with was merely 5 TB ... Anyway, my opinion on this, in detail, will be on the ITToolBox blog. You can argue with me there. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] suppress output for benchmarking
I am testing the performance of postgresql on a set of workloads. However, the output significantly affects the performance evaluation. Is there a way to by-pass all output of select statements so the timing reflects only the query evaluation process? thanks a lot
Re: [HACKERS] suppress output for benchmarking
On Wed, Jan 18, 2006 at 10:35:48PM -0500, uwcssa wrote: I am testing the performance of postgresql on a set of workloads. However, the output significantly affects the performance evaluation. Is there a way to by-pass all output of select statements so the timing reflects only the query evaluation process? SELECT count(*) FROM (SELECT ...) a; If you're using psql \timing will probably be useful as well. And this is better suited for -general... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] pgxs/windows
Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: Could this be related to the fact that pre-8.2 makefiles were not space-safe? I am unsure how pgxs worked on Win32 without being space-safe. I don't see how. In fact, pgxs seems to use short form paths anyway. Example (from previous email): dllwrap -o rainbow.dll --def rainbow.def rainbow.o c:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/../../src/utils/dllinit.o -Lc:/PROGRA~1/POSTGR~1/8.1/bin -lpostgres No spaces there. The problem is it says bin instead of lib before -lpostgres. OK, thanks. Next question --- are the installed file locations the same for a MinGW install and a pginstaller install? I don't think pginstaller does a MinGW install because it doesn't have the build environment in the tarball. However, the big difference seems to be that Magnus has -Llib and -Lbin, while you have only the -Lbin. I have MinGW and pginstaller installed here. How can I set things up to test this? Now looking at the Makefile.global in the 8.1.2 pginstaller install, in Makefile.global, $libdir is set in a pgxs-specific block: libdir := $(shell pg_config --libdir) and that seems to work: C:\Program Files\PostgreSQL\8.1\binpg_config --libdir C:/PROGRA~1/POSTGR~1/8.1/lib and that is set to LDFLAGS, which is later propogated to SHLIB_LINK, though SHLIB_LINK moves all the -L flags to the front, so what you see on the link line is not the ordering used to create the value. Andrew, can you try echoing $libdir and $SHLIB_LINK in the Makefile to find those values? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Surrogate keys (Was: enums)
Michael Glaesemann [EMAIL PROTECTED] writes: As far as I can tell, the only difference between your position, Dann, and Date and Darwen's, is that you think no natural key is immutable. DD's examples of natural keys are worth a second look though: If a primary key exists for a collection that is known never to change, for example social security number, student registration number, or employee number, then no additional system-assigned UID is required. The problem with SSN is that somebody other than you controls it. If you are the college registrar, then you control the student's registration number, and you don't have to change it. In fact, guess what: you probably generated it in the same way as a surrogate key. I'd argue that all of these are in reality the exact same thing as a surrogate key --- from the point of view of the issuing authority. But from anyone else's point of view, they are external data and you can't hang your own database design on the assumption that they won't change. regards, tom lane ---(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
[HACKERS] 8.0.5 Bug in unique indexes?
Hello, Odd problem with unique indexes: 8.0.5 64 bit (Quad Opteron) 100 tables, each table has same layout, 1 million rows per table. The problem persists within multiple tables but only within the set of 100 tables. I have a composite unique key on each table: uniq1 UNIQUE, btree (unit_id, email) Performing a query like the following: app=# select unit_id, email, count(*) as cnt from leads10 group by unit_id, email having count(*) 1; unit_id | email | cnt -++- 77212 | [EMAIL PROTECTED] | 2 app=# select unit_id,email from leads10 where unit_id = 77212 and email = '[EMAIL PROTECTED]'; unit_id | email -+ 77212 | [EMAIL PROTECTED] (1 row) app=# reindex index uniq1; ERROR: could not create unique index DETAIL: Table contains duplicated values. app=# I have verified that we have not overrun the fsm pages and that vacuums are running daily (actually twice a day). I have also ran a vacuum full on the various tables to no avail, no error but the situation does not improve. app=# set enable_indexscan = off; SET app=# select unit_id,email from leads10 where unit_id = 77212 and email = '[EMAIL PROTECTED]'; unit_id | email -+ 77212 | [EMAIL PROTECTED] 77212 | [EMAIL PROTECTED] (2 rows) app=# select lead_id,unit_id,email from leads10 where unit_id = 77212 and email = '[EMAIL PROTECTED]'; lead_id | unit_id | email --+-+ 35867251 | 77212 | [EMAIL PROTECTED] 35864333 | 77212 | [EMAIL PROTECTED] (2 rows) Thoughts? Joshua D. Drake P.S. Should this go to -bugs? ---(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] Unique constraints for non-btree indexes
Tom Lane [EMAIL PROTECTED] writes: Martijn van Oosterhout kleptog@svana.org writes: I guess what you're talking about is a constrained index, of which a unique index is just a particular type. I suppose the actual constraint would be one of the operators defined for the operator class (since whatever the test is, it needs to be indexable). Although some would obviously be more useful than others... I think the generalization that would be appropriate for GIST is that a unique index guarantees there are no two entries x, y such that x ~ y, where ~ is some boolean operator nominated by the opclass. We'd probably have to insist that ~ is commutative (x ~ y iff y ~ x). I have no big contribution here. I just want to say this is a cool idea. These Generalized uniqueish constraints could make a lot of neat things possible. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Indexes vs. cache flushes
I've been working on getting the system to pass regression tests cleanly when forcing a cache flush at every possible instant. The main tests pass now (in 8.1 --- HEAD remains broken pending lookup_rowtype_tupdesc fix), but contrib is still crashing. On investigation the problem turns out to be in index_getprocinfo(), which tries to load up a cached FmgrInfo for an index support function. If the support function is not a built-in C function, then fmgr_info() will need to open pg_proc to look it up. If a cache flush occurs in the course of that lookup, the FmgrInfo we're trying to store into goes away! Havoc ensues of course. After looking at this for a bit, it seems the cleanest fix is for RelationClearRelation() to treat any open index the same way it currently handles nailed indexes --- ie, don't do anything except re-read the pg_class record. Then we won't try to flush and rebuild the cached index support info, and the problem doesn't arise. This would still support REINDEX (which changes pg_class.relfilenode in order to replace the physical file) and ALTER INDEX SET TABLESPACE. But you couldn't make any meaningful changes in the definition of an index, such as changing its column set, operator classes, partial-index predicate, etc, except by dropping and recreating it. Now this is true today, and it doesn't seem likely to me that we'd ever want to relax it (since any such change would probably require rebuilding the index anyway). But does anyone see that differently? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Surrogate keys (Was: enums)
If a primary key exists for a collection that is known never to change, for example social security number, student registration number, or employee number, then no additional system-assigned UID is required. In point of fact Social security numbers *can* change. -- greg ---(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] No heap lookups on index
Tom Lane [EMAIL PROTECTED] writes: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. It's a good point though. Surely a database should be optimised for the most common operation - commits, rather than rollbacks? The shuffling off of the data is expensive in itself, so I'm not sure you can argue that the Oracle way is more optimal for commits either. You pay in Oracle when you read these records too. If there are pending updates you have to do a second read to the rollback segment to get the old record. This hits long-running batch queries especially hard since by the time they finish a large number of the records they're reading could have been updated and require a second read to the rollback segments. You also pay if the new value is too big to fit in the same space as the old record. Then you get to have to follow a pointer to the new location. Oracle tries to minimize that by intentionally leaving extra free space but that has costs too. And lastly rollback segments are of limited size. No matter how big you make them there's always the risk that a long running query will take long enough that data it needs will have expired from the rollback segments. Oh, and note that optimizing for the common case has limits. Rollbacks may be rare but one of the cases where they are effectively happening is on recovery after a crash. And that's one process you *really* don't want to take longer than necessary... -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.0.5 Bug in unique indexes?
Joshua D. Drake [EMAIL PROTECTED] writes: Odd problem with unique indexes: What's the database's locale? This could be the same problem fixed in 8.0.6, if the locale has weird ideas about what string equality means. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] No heap lookups on index
David Scott [EMAIL PROTECTED] writes: Since I am sure everyone is tired of the intro by now, I'll get to the questions: ... Is there any way to modify PostgreSQL to allow index lookups without heap validation that doesn't involve re-writing the MVCC implementation of keeping dead rows on the live table? Is the additional overhead of keeping full tuple visibility information inside of the index so odious to the Postgres community as to prevent a patch with this solution from being applied back to the head? The consequences of full visibility information in indexes would indeed be pretty odious. However the general gist the conversation led last time it came up had what sounded like a feasible compromise: Keep a very compact bitmap outside the table (not attached to any single index) with one bit per tuple indicating whether the tuple was known to be visible to every transaction. The hope being this bitmap would be small enough to sit in memory pretty much permanently. Even if not then it should be much smaller than the table and impose a pretty small i/o overhead. If most of the records in the table are old records that are visible to every transaction then the index scan would be able to avoid reading in pages of the heap. Most would have to be a pretty big percentage though since even a single tuple with unknown visibility would have to be read in. The bitmap would be useful for vacuum too. Any page that contained only tuples with known visibility could be skipped. That would mean running vacuum for extremely large tables that have only moderate activity wouldn't have to scan all those static pages. (There could be an issue with people whose FSM can't track all the free space but expect it to be found on subsequent vacuums, but details details.) I wonder if the bitmap can actually be one bit per page actually. A single update has to set the bit for the tuple, and that will make the whole page have to be read in for both vacuum and index lookups. Only a vacuum will be able to verify that all the tuples in the page are known-visible and index entries have been cleaned up, and the vacuum is going to be operating on the whole page anyways. A one-bit-per-page bitmap will easily fit in RAM even for very large tables. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] No heap lookups on index
Greg Stark [EMAIL PROTECTED] writes: You pay in Oracle when you read these records too. If there are pending updates you have to do a second read to the rollback segment to get the old record. This hits long-running batch queries especially hard since by the time they finish a large number of the records they're reading could have been updated and require a second read to the rollback segments. If not third or fourth read, by the time you've found the version you're supposed to be able to see. I recall discussing this several years ago with somebody who knew quite a bit about Oracle innards (though he didn't say how he knew...) According to him, heavy accesses to the rollback segments have another problem, which is contention for ownership of locks protecting access to the rollback segments. I got the impression that it would be like us needing to take the WALWriteLock anytime we wanted to look at any not-the-very-latest row version --- there's plenty of write traffic that needs that lock, and you don't want to load it down with read traffic too. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] No heap lookups on index
Greg Stark [EMAIL PROTECTED] writes: I wonder if the bitmap can actually be one bit per page actually. Yeah, I think we'd agreed that per-page was the way to go. Per-tuple bitmaps are painful to manage because of the variable number of tuples per page. And really all you need to know is whether to read the page or not --- once you have, examining multiple tuples on it doesn't cost much. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Surrogate keys (Was: enums)
On Wed, Jan 18, 2006 at 03:58:50PM -0800, Josh Berkus wrote: Martjin, Interesting. However, in my experience very few things have natural keys. There are no combination of attributes for people, phone calls or even real events that make useful natural keys. I certainly hope that I never have to pick up one of your projects. A table without a natural key is a data management disaster. Without a key, it's not data, it's garbage. ??? Please provides natural keys for any of the following: - A Person - A phone call: (from,to,date,time,duration) is not enough - A physical address - A phone line: (phone numbers arn't unique over time) - An internet account: (usernames not unique over time either) In any of these either misspellings, changes of names, ownership or even structure over time render the obvious useless as keys. There are techniques for detecting and reducing duplication but the point is that for any of these duplicates *can* be valid data. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Indexes vs. cache flushes
Tom Lane [EMAIL PROTECTED] writes: This would still support REINDEX (which changes pg_class.relfilenode in order to replace the physical file) and ALTER INDEX SET TABLESPACE. But you couldn't make any meaningful changes in the definition of an index, such as changing its column set, operator classes, partial-index predicate, etc, except by dropping and recreating it. Now this is true today, and it doesn't seem likely to me that we'd ever want to relax it (since any such change would probably require rebuilding the index anyway). But does anyone see that differently? The only example that comes to mind of something you might want to be able to twiddle and wouldn't expect to be a slow operation is making a unique index a non-unique index. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Indexes vs. cache flushes
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: But you couldn't make any meaningful changes in the definition of an index, such as changing its column set, operator classes, partial-index predicate, etc, except by dropping and recreating it. The only example that comes to mind of something you might want to be able to twiddle and wouldn't expect to be a slow operation is making a unique index a non-unique index. I think actually that that would still work, so long as you acquired exclusive lock on the parent table first (which you'd have to do anyway, because this would constitute a significant change to the table's schema --- it could invalidate plans for example). The lock would guarantee that no one has the index open. It's only in the case of an opened index that I propose not flushing the index support info. The concerns that I find more interesting are changes in the underlying objects. We don't have an ALTER OPERATOR CLASS, much less an ALTER ACCESS METHOD, but it's certainly theoretically possible to change the definition of a support function used by an index. There isn't presently any mechanism to force timely propagation of such a change, and so you'd be largely on your own --- but realistically, wouldn't such a change require rebuilding the index anyway? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
