Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, first of all, thanks to all, that replied! On Wed, Nov 16, 2005 at 08:28:31AM +0100, Martijn van Oosterhout wrote: On Wed, Nov 16, 2005 at 08:09:31AM +0100, Yann Michel wrote: Well, thanks for all the answers. Are the locks then released once they are not needed any more like in 2PC? 2PC doesn't release any locks, it can't to maintain integrity. Aehm. sorry I meant 2PL ... all this accronyms... ;-) The normal 2PL releases the locks once they are not needed anymore but can not aquire new ones. Strict 2PL releases them all at one point. That should still leaqve the taken snapshot of the released table in a consistent state but might enable other transactions to work on that one table once it is released. ACCESS SHARE means what it says, it stops the table being VACUUMed and a few other things, but doesn't block INSERTs, UPDATEs or DELETEs. Thanks. BTW: Is there anything about locks and their meaning inside of the Docs? If not, wouldn't that be nice? pg_dump doesn't blocks inserts, so your problem must be somewhere else... Are you running VACUUM anywhere. It's possible that pg_dump is blocking VACUUM which blocks your inserts... Well, now that I'm thinking about, what you've written I think this is exactly the point. I think, that there is a VACUUM waiting for the dump to finish whereas the INSERTS are waiting for the VACUUM to finish. Thannks! Cheers, Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi Martijn, On Wed, Nov 16, 2005 at 09:47:33AM +0100, Martijn van Oosterhout wrote: Ah yes, PostgreSQL doesn't use 2PL, it uses MVCC. quick overview here: http://linuxgazette.net/issue68/mitchell.html THX! That was interesting! Thanks. BTW: Is there anything about locks and their meaning inside of the Docs? If not, wouldn't that be nice? Check here: http://www.postgresql.org/docs/8.0/interactive/mvcc.html Under Explicit Locking it lists all the locks and what they're for. Yes, thanks once more! Cheers, Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, On Wed, Nov 16, 2005 at 10:07:24AM -0500, Tom Lane wrote: Yann Michel [EMAIL PROTECTED] writes: Well, now that I'm thinking about, what you've written I think this is exactly the point. I think, that there is a VACUUM waiting for the dump to finish whereas the INSERTS are waiting for the VACUUM to finish. Only if it's a VACUUM FULL ... plain VACUUM neither blocks INSERT nor is blocked by pg_dump. The short answer may be don't use VACUUM FULL (at least not for routine automatic vacuums). ... I guiess that the AUTOVACUUM switch only does an automated VACUUM but no VACUUM FULL? Cheers, Yann ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi all, On Sun, Nov 13, 2005 at 03:22:23AM +0100, Yann Michel wrote: I'm using PG_DUMP for backing up a postgres 7.4 database. As I have seen, the pg_dump aquires a table lock while dump the table's content. What will happen, if I have a basic table and several inherited tables. Will the PG_DUMP tool only aquire locks on the inherited tables or on the parent-table, too? Is anyone out there who could answer this question or even give me a hint about where to read about this if there is any writen things about that? Thanks! Yann ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, On Wed, Nov 16, 2005 at 09:59:44AM +0800, Christopher Kings-Lynne wrote: It acquires share locks on EVERY table. do you mean on EVERY inherited table once one of them is dumped? Or do you mean that a share lock is requested(and hold) on each of them once one is dumped, i.e., sequentially? Thanks, Yann ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PG_DUMP and table locking in PG7.4
Hi, On Wed, Nov 16, 2005 at 01:25:43PM +0800, Christopher Kings-Lynne wrote: I belive a lock is acquired on every table including inherited children BEFORE doing ANY dumping. To allow pg_dump to get a consistent dump snapshot. Well, thanks for all the answers. Are the locks then released once they are not needed any more like in 2PC? That should still leaqve the taken snapshot of the released table in a consistent state but might enable other transactions to work on that one table once it is released. I'm asking, because we have a bigger datawarehouse and dump the data for a backup every night. Unfortunately, the backup now takes realy long. That means, other processes that insert data will have to wait which is sometime really long! I was searching for a way to avoid this. I thought besides the query-speedub we could also gain some benefit for the backup timing... but it sounds, that this will not automatically help me with that. :-( Cheers, Yann ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] CONNECT BY PRIOR
Hi, I'm just a little bit confused because I expected postgresql to be able t connect by prior but as I have seen it is not. :-( Are there any plans to support this in the main distribution? If have found a patch to porstgres but I don't want to apply any patches but only use the vanilla postgresql. BTW: The patch is available at http://gppl.moonbone.ru/ Cheers, Yann ---(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] CONNECT BY PRIOR
Hi, On Sat, Nov 12, 2005 at 03:27:32PM -0500, Jonah H. Harris wrote: Yann, I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Fine! Looking forward to that! Cheers, Yann ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PG_DUMP and table locking in PG7.4
Hi, I'm using PG_DUMP for backing up a postgres 7.4 database. As I have seen, the pg_dump aquires a table lock while dump the table's content. What will happen, if I have a basic table and several inherited tables. Will the PG_DUMP tool only aquire locks on the inherited tables or on the parent-table, too? Thanks in advance! Cheers, Yann ---(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] User Quota Implementation
Hi again, On Mon, Jun 13, 2005 at 04:47:20PM -0600, Jonah H. Harris wrote: Well... a maximum tablespace size would be much easier to implement and would still accomplish this level of quota for larger organizations and database systems. I vote for implmenting the maximum tablespace size and revisiting actual user/group quotas when the need arises. Was someone going to implement this? If not, I can probably get it done in a couple days. are you still working on this or what has hapened to the idea of MAXSIZE? Regards, Yann ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Hi folks! On Tue, Jun 14, 2005 at 11:39:06AM -0600, Jonah H. Harris wrote: On second thought, we need to have a GUC for this, whether I want it or not. It needs to be optional to the log, yes? So it would be: log_tablespace_full = % with the default being 0 (don't log). On third thought, could we do this as part of the maximum size declaration? Like: ALTER TABLESPACE tbsp1 SET MAXSIZE 128M MAXWARN 80 That would be ideal, since the % you might want could vary per tablespace. This would be emitted as a WARNING to the log every time you run a check (e.g. after each commit). Yes, that is the best idea, I think. What I don't like with the GUC variable is, that it is _one_ warninglevel for all tablespaces independent of their character. If I have two tables in different tablespaces both with the global 90 percent threshold I may become warned that tablespace A is 90 percent full, but if this tbsp. will only grow one percent per day I still have 10 days left. Otherwise if I have a tablespace B with 9 percent full but 5 percent growth per day that will only be two days. So normaly id would have been fine to be warned 3 days ago for tablespace B but 5 to early for tablespace A. Did you get the idea? Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
I'd like to avoid a GUC for percent_full_warning if we can. Can anyone see a way around this? Should we just assume 90% full? Well, it was only an idea of not leaving the admin out in the rain but giving im a hint by time of what might happen if there was no action. I have absolutely no idea if it is usefull of introducing a new GUC or setting this value to a fixed size of 90 or whatever percent. Maybe 95 percent are enough, too? Regards, Yann ---(end of broadcast)--- TIP 3: 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] User Quota Implementation
Hi Josh, hi jonah, On Mon, Jun 13, 2005 at 12:36:12PM -0700, Josh Berkus wrote: Don't get me wrong, I think we need tablespace maximums. What I'm looking at is a user/group-based quota which would allow a superuser to grant say, 2G of space to a user or group. Any object that user owned would be included in the space allocation. So, if the user owns three tablespaces, they can still only have a maximum of 2G total. This is where I think it would be wise to allow the tablespace owner and/or superuser to set the maximum size of a tablespace. Yeah, the problem is that with the upcoming group ownership I see user-based quotas as being rather difficult to implement unambiguously. Even more so when we get local users in the future. So I'd only want to do it if there was a real-world use case that tablespace quotas wouldn't satisfy. Well, I think in one way jona is right, that I mixed up two things. Indeed a max size for a tablespace is something different, than a quota. In my opinion, it makes only sense to use quotas for ressource-owners on ressources, i.e. tablespaces. To as an example I think about some tablespace whith a MAXSIZE of 2 GB (that it won't grow until the disk is full) and a QUOTA of 500 MB for user A on that certain tablespace. In general (of cause this is only my experience in using quotas in dbms) you will create different tablespaces for different object kinds/types i.e. one for indexes, one for dimensions and at least one for the fact data in a dwh. So to allow users to store their comparable tables in the appropriate tablespace you'd set up a quota for them. Regards, Yann ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] User/Group Quotas Revisited
Hi, On Sat, Jun 11, 2005 at 09:45:12PM -0400, Gregory Maxwell wrote: - Who has permissions to set the user's quota per tablespace, the superuser and the tablespace owner? It would be nice if this were nestable, that is, if the sysadmin could carve out a tablespace for a user then the user could carve that into seperately quotated sub tables.. The idea being, a user may have several tables, some of which are likely to get big and fat and gain lots of crud, but some of which will never grow too big but you really don't want to fail just because someone floodded the other table and used up your quota. It would be nice if the user could manage that subassignment as he saw fit without assistance from the admin. I think this is not necessary, due to yu may suround that problem by using different tablespaces for that different table types. Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
Hi Josh,
On Fri, Jun 10, 2005 at 02:25:11PM -0700, Josh Berkus wrote:
O.K. This makes sens to me. Otherwise I'd like to see quotas per
tablespace. As far as I got it, a tablespace may grow in size untile the
volume is full. Here a grace quota might be usefull as well. Let's say a
5% threshold like the ext filesystem as an default for generating a
warning to th elogs files letting the admin extend the volum(s) by time.
Hmmm ... Tablespace quotas would be *even more* useful than database
quotas. If it's just as easy for you?
Well, lets see...
What do we need:
- Extension of the CREATE TABLESPACE command:
CREATE TABLESPACE tablespacename
[ OWNER username ]
[ SIZE integerK | M | G | T ]
LOCATION 'directory'
- Extension of the ALTER TABLESPACE command:
ALTER TABLESPACE name
{RENAME TO newname |
SIZE integerK | M | G | T }
- Storage of this information in the system tablespace relation
- Determine the actual size of a tables space
-- Already exists in contrib/dbsize/dbsize.c
- Define the point in time where this calculation should happen.
That's the point where I think some lazyness may appear, i.e. it is
enough to evaluate the size from time to time but not after each
statement. Of cause this will enable that a tablespace may become to
large but once it is to large, further extensions of it will become
prohibited.
- Define how to disable further extension of tablespace objects or
creation of new ones.
- Optional: Define postgresql.conf parameter:
tablesspace_full_warning = 90
Whenever the threshold of 90 percent is reached a warning will
be generated (and written to the log-files)
So far from me about my thoughts...
Regards,
Yann
---(end of broadcast)---
TIP 3: 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] User Quota Implementation
Hi,
On Sat, Jun 11, 2005 at 05:36:34PM +0100, Dave Page wrote:
What do we need:
- Extension of the CREATE TABLESPACE command:
CREATE TABLESPACE tablespacename
[ OWNER username ]
[ SIZE integerK | M | G | T ]
LOCATION 'directory'
- Extension of the ALTER TABLESPACE command:
ALTER TABLESPACE name
{RENAME TO newname |
SIZE integerK | M | G | T }
Wouldn't MAXSIZE be more appropriate?
Yes, of cause.
- Storage of this information in the system tablespace relation
- Determine the actual size of a tables space
-- Already exists in contrib/dbsize/dbsize.c
A patch was recently submitted by Andreas Pflug to move those functions
into the backend permanently.
I think that's why I found it there ;-)
Regards,
Yann
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Hi, did anything happen to implementing quotas, yet? though I did not see anything on the TODO List I was wondering what is going on. Regards, Yann ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Hi Bruce, On Fri, Jun 10, 2005 at 09:45:32AM -0400, Bruce Momjian wrote: did anything happen to implementing quotas, yet? though I did not see anything on the TODO List I was wondering what is going on. No work has been done on it, and I don't even see a TODO item for it. Do you think that it is possible that one can generate a TODO item out of the request or do you rather think different? Regards, Yann ---(end of broadcast)--- TIP 3: 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] User Quota Implementation
On Fri, Jun 10, 2005 at 10:07:59AM -0400, Bruce Momjian wrote: Do you think that it is possible that one can generate a TODO item out of the request or do you rather think different? Yes, sure. Ah, I found it. TODO has now: * Allow limits on per-db/user connections Fine! That is pretty vague, but it is all we have so far. In fact, that refers more to the number of connections rather than say disk space or CPU. The issue we have had with these issues in the past is that we aren't sure how such limits would be implemented or used. Well, I have realy a lot of experiences with oracle usage and with its limitation capabilities. What I need the most is space-limitation per tablespace. Since 9i there is also a possibility to restrict cpu-usage for a certain consumer or group but in fact I din't need to to so since most of the apps have their own database. Maybe it could be useful to have these groups later on. As far as I understood the thread above (and any other mails) the space limitation would not only be nice for me. BTW: Is there any patch available, yet? This thread dealt with a patch but I didn't see any!? Regards, Yann ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] User Quota Implementation
Hi, On Fri, Jun 10, 2005 at 10:59:46AM -0400, Bruce Momjian wrote: Well, I have realy a lot of experiences with oracle usage and with its limitation capabilities. What I need the most is space-limitation per tablespace. Since 9i there is also a possibility to restrict cpu-usage for a certain consumer or group but in fact I din't need to to so since most of the apps have their own database. Maybe it could be useful to have these groups later on. As far as I understood the thread above (and any other mails) the space limitation would not only be nice for me. I assume you can't use file system quotas for the tablespace partitions? No, that's definetely no solution, due to I'm interested in a general solution which should be applicable for all platforms. BTW: Is there any patch available, yet? This thread dealt with a patch but I didn't see any!? Oh, there is no patch, just a discussion. O.K. so I was not wrong about that. Is it usefull to re-discuss some of the aspects to get a gist of what should probably be implemented/extended? Regards, Yann ---(end of broadcast)--- TIP 3: 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] User Quota Implementation
Hi Josh! On Fri, Jun 10, 2005 at 10:13:52AM -0700, Josh Berkus wrote: Yeah. I'd prefer per-database quotas, rather than per-user quotas, which seem kind of useless. The hard part is making any transaction which would exceed the per-database quota roll back cleanly with a comprehensible error message rather than just having the database shut down. If we had per-database user quotas, and per-database users, it would pretty much wind up all of the issues which ISPs have with Postgres. O.K. This makes sens to me. Otherwise I'd like to see quotas per tablespace. As far as I got it, a tablespace may grow in size untile the volume is full. Here a grace quota might be usefull as well. Let's say a 5% threshold like the ext filesystem as an default for generating a warning to th elogs files letting the admin extend the volum(s) by time. Regards, Yann ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] User Quota Implementation
Hi Tom, On Fri, Jun 10, 2005 at 01:37:54PM -0400, Tom Lane wrote: Josh Berkus josh@agliodbs.com writes: Yeah. I'd prefer per-database quotas, rather than per-user quotas, which seem kind of useless. The hard part is making any transaction which would exceed the per-database quota roll back cleanly with a comprehensible error message rather than just having the database shut down. That part doesn't seem hard to me: we already recover reasonably well from smgrextend failures. The real difficulty is in monitoring the total database size to know when it's time to complain. We don't currently make any effort at all to measure that, let alone keep track of it in real time. Given that there might be lots of processes concurrently adding pages in different places, I don't think you could hope for an exact stop-on-a-dime limit, but maybe if you're willing to accept some fuzz it is doable ... Well I think a fuzzy test is better than none. But I think one should be able to calculate how much later the quota is detected as exceeded than it is planed to be. Therefor a threshold is usefull as well (for alerting) Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Account in postgresql database
Hi Hannu, On Thu, Jun 09, 2005 at 01:03:42AM +0300, Hannu Krosing wrote: I was searching for some information about the storage of the user data in postgresql. As far as I know there is one dictionary table for storeing all the users of any known database, right? As we'd like to provide a postgresql database service to our students we'd like to create one database for each user. This user should be able to create new accounts for other users but only for his/her database. Any comments or solutions are welcome. see if db_user_namespace (in postgresql.conf) can solve your problem Yes, that is quite the thing, I was looking for. But unfortunately there is a note within the documentation: Note: This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed. Another question is, how to grant the right of creating a databaseuser to a certain database user for only one database. Regards, Yann ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Account in postgresql database
Hi, I was searching for some information about the storage of the user data in postgresql. As far as I know there is one dictionary table for storeing all the users of any known database, right? As we'd like to provide a postgresql database service to our students we'd like to create one database for each user. This user should be able to create new accounts for other users but only for his/her database. Any comments or solutions are welcome. Regards, Yann - Human knowledge belongs to the world. The answer is open source ! - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
Hi, On Sun, Jan 23, 2005 at 12:16:31PM -0800, Josh Berkus wrote: 4) ignore backwards compatibility and just re-write the old views. I can hear the shouting already ... So, a choice of annoying options. Does anyone else on the channel have opinions? Isn't it a usefull option to introduce a postgresql-conf parameter to set the pg-views version? I mean, in a pg 7.x-comaptibility-mode you would only see the known views with their old content. If you set it to 8.x, you will see the new versions. So developers will get more time to change their applications from the old views to the new ones while being able to use new features. Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Caching of frequently used objects
Hi, as there were several questions about increasing performance in dwh systems I was looking for some hints about how to pin an object to the buffer so that they are not aged out due to the space is needed by any other process or object. I know that in oracle you can do this by defining a seperate buffer cache and to asign an object to that special buffer cache. So you could assign objects to one pool and all other objects will use the default one. I think even count(*) queries could benefit from this buffer-splitting due to indexes might be pinned to this buffer pool. So my question is if there is already any comparable functionality or if it is planed. I didn't find any comparable feature or TODO on the list. Regards, Yann ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Caching of frequently used objects
Hi, On Wed, Jan 19, 2005 at 11:54:50AM -0600, Bruno Wolff III wrote: objects will use the default one. I think even count(*) queries could benefit from this buffer-splitting due to indexes might be pinned to this buffer pool. This wouldn't have any special effect on count(*) queries. O.K. not full, but due to indexes may be used for some of this queries. the indexes themselves could be pinned into the special buffer pool and need not to be loaded into the cache. So my question is if there is already any comparable functionality or if it is planed. I didn't find any comparable feature or TODO on the list. The developers seem to feel that having postgres and the os decide what should be cached based on observed usage is better than having the DBA do this. The effect while using a seperate buffer cache for different objects, i.e. using a lru list would stay the same. There would be only two more than one buffer cache for a certain object gourp or class. In dwh systems you would normally use a special buffer pool for your dimensions to pin them into memory so that they are not rolled out by any large fact table at all. In fact they can become rolled out but this may only happen if an object belonging to the same pool should be loaded into the cache. This is more or less the fact if the dba has sized the pin-cache to small. Regards, Yann ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] [PERFORM] query rewrite using materialized views
Hi, are there any plans for rewriting queries to preexisting materialized views? I mean, rewrite a query (within the optimizer) to use a materialized view instead of the originating table? Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thoughts about updateable views
Hi, On Wed, Dec 22, 2004 at 09:41:40AM +, Richard Huxton wrote: UNION etc doesn't necessarily mean you can't update, so long as the underlying table/key can be identified. I think you mean UNION ALL, i.e. the set addition, don't you? Otherwise UNION (wothout ALL) is kind of a aggregation due to it only adds a row once to the resulting set wheter it is found twice or not. Therefore any updates are not possible. Regards, Yann ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bitmap index
Hi, On Fri, Nov 26, 2004 at 10:25:41AM -, Pawel Niewiadomski wrote: My promoter accepted the subject. I'm waiting for an official acceptance of the subject. Until then I would want to get familiar with PostgreSQL internals and bitmap index implementations. I will appreciate any links to online papers, books that could help me. That sounds nice! I thought of implementing it as part of my master thesis next year so I already started reading about bitmap indexing and so on. A nice start is possibly Hector Garcia-Mollina Database Implementation. Regards, Yann ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Contribute to the development of PostgreSQL
Hi, On Thu, Nov 04, 2004 at 11:26:41AM +0100, [EMAIL PROTECTED] wrote: I would like to hear your opinion and whether anyone is interested in helping. I'd appreciate any kind of hints, helping me to understand the modules/components and sources of each. Regards, Yann ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] plans for bitmap indexes?
Hi, On Wed, Oct 27, 2004 at 10:13:56AM -0400, Greg Stark wrote: There's a logical separation between the idea of index methods and table storage mechanisms. Trying to implement something like this that breaks that abstraction will only make things far more confusing. I think what you're trying to accomplish is better accomplished through partitioned tables. Then the user can decide which keys to use to partition the data and the optimizer can use the data to completely exclude some partitions from consideration. And it wouldn't interfere with indexes to access the data within a partition. this is not always the truth. In datawarehouosing applications you often use data paritioning (time based) and bitmap indexes for fast star-transformations. A very efficient way to solve that ist using bitmap indexes. Regards, Yann ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] plans for bitmap indexes?
Hi Tom, On Fri, Oct 15, 2004 at 11:27:05AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Thu, Oct 14, 2004 at 11:08:54PM +0200, Yann Michel wrote: BTW: Is there any more documented CVS-version available? I mean it would be really nice to read some comments from time to time or at least more comments about each function/method's purpose or functionality. Huh, the code is reasonably commented. Certainly not following Javadoc-like standards, but it can be read. Also, have you read the Internals volume of the SGML docs? Mostly pretty high-level stuff, but that's what you need to get started. The developer's FAQ is also required reading for newbies. There are also README files in various parts of the source tree that provide information about various sub-modules. I have not jet been reading all of it but some of the README files. I will keep that hint in mind but first of all I'll read something about bitmap compression and other relevant techniques before starting to discover the index internals of postgresql... ;-) I've been using all kinds of functions in oracle for a long time but never had the experience to implement any indexing strategies. The only thing I did were some operating system extensions for minix during my os-studies (scheduling, driver, acl etc.) If there is anything additional/special to know further, I apreciate any hints. Regards and thanks, Yann ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] plans for bitmap indexes?
Hi, On Sat, Oct 09, 2004 at 01:31:36PM -0400, Chris Browne wrote: The most nearly comparable thing is be the notion of partial indexes, where, supposing you had 60 region codes (e.g. - 50 US states, 10 Canadian provinces), you might set up indices thus: [...] The partial indexes will not ALWAYS be useful; in cases where they aren't, it is not inconceivable that there are improvements to be made in the query optimizer... So what you are suggesting here is the tree-fashioned-static way of real bitmap indexes. I.E. each time a new value is inserted vor any kind of thus indexes column you have to create a new index which is not very usefull as you can think of. In addition nothing about the real granularity is known to the optimizer to let it guess the best execution plan, i.e. to do a full table scan or use an index. That means if one attributes value is representative for 80 percent it is usefull to do a full table scan whereas if its value is representative for only 5 percent the index might be better. But as I understood the partial index concept, no statistics for value representation are available. Therefore I started to do read some articles and books about bitmap index implementations to maby contribute... we will see... BTW: Is there any more documented CVS-version available? I mean it would be really nice to read some comments from time to time or at least more comments about each function/method's purpose or functionality. Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plans for bitmap indexes?
Hi, On Thu, Oct 07, 2004 at 06:54:15PM -0400, Bruce Momjian wrote: I'd like to know if there are any plans on introducing bitmap indexes into postgresql. I think this could mean a big performance improvement especially for datawarehousing applications. I know that there is an index type hash but I don't know how both types are comparable due to they are both best usable for equality expressions. Lots of people have talked about it but I don't know anyone coding it. have you ever discussed if bitmap indexes lead to better query performance than hash indexes will do? Regards, Yann ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plans for bitmap indexes?
Hi, On Fri, Oct 08, 2004 at 10:09:18AM +0100, Dave Page wrote: I think what Reini was asking was why do you think you need bitmap indexes as opposed to any existing type? due to I'm developing a datawarehousing application we have lots of fact-data in our central fact-table. As I know how to improve performance on Oracle based datawarehouses, I'm used to add bitmap indexes for atributes having only a few distinct values. So I was looking for any comparable indexing technology but didn't find any so far. Regards, Yann ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] plans for bitmap indexes?
Hi Josh, On Fri, Oct 08, 2004 at 09:59:41AM -0700, Josh Berkus wrote: Lots of people have talked about it but I don't know anyone coding it. I would love to have bitmap indexes in Postgres, as would a lot of other community members. However, they are far from trivial to code. Are you offering to help? I'd like to help you, but I think, that my C-Experience is not good enough for beeing able to. I mean, I coded some C-stuff and I know how bitmap indexes (should) work but I guess that this won't be enough. In addidtion I never took a look into postgresql's sources. Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plans for bitmap indexes?
Hi Josh, On Fri, Oct 08, 2004 at 10:18:27AM -0700, Josh Berkus wrote: I'd like to help you, but I think, that my C-Experience is not good enough for beeing able to. I mean, I coded some C-stuff and I know how bitmap indexes (should) work but I guess that this won't be enough. In addidtion I never took a look into postgresql's sources. Well, there's no time like the present to get started! ;-) O.K. I downloaded it :-) We will see if and how I can help Regards, Yann ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] plans for bitmap indexes?
Hi, I'd like to know if there are any plans on introducing bitmap indexes into postgresql. I think this could mean a big performance improvement especially for datawarehousing applications. I know that there is an index type hash but I don't know how both types are comparable due to they are both best usable for equality expressions. Thanks in advance! Regards, Yann ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] suspicius behaviour during delete
Hi all, I hope this is the right mailinglist for my question. I'm using postgresql 7.2.1 and doing some bulk-loads from one table to another. Due to sometimes there may exist som already loaded rows the first thing I do is to delete them to reinsert all of them later on. This sequence is but into one transaction. My problem now is, that postgresql behaves somehow strange. Sometimes when I practice like mentioned above it works just fast within seconds but sometimes it last more than an hour. Both times 3 rows are processed. Even if I'd like to delete and insert only one row it lasts an hour until I cancel the statement. Does anyone have an idea or can anyone give me some hints how to solve that problem? I'd appreciate any help! Thanks in advance, Yann ---(end of broadcast)--- TIP 8: explain analyze is your friend
