Re: [HACKERS] requested shared memory size overflows size_t
Dear Craig, The output is: #define SIZEOF_OFF_T 8 #define SIZEOF_VOID_P 4 #define SIZEOF_SIZE_T 4 #define SIZEOF_LONG 4 Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --enable-shared --enable-languages=c,c++,f77 Thread model: posix gcc version 3.4.6 Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Craig Ringer [mailto:cr...@2ndquadrant.com] /define Sent: Friday, March 14, 2014 4:41 AM To: Yuri Levinsky; Robert Haas Cc: Heikki Linnakangas; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] requested shared memory size overflows size_t On 03/04/2014 10:53 PM, Yuri Levinsky wrote: Please advise me: I just downloaded the source and compiled it. Sun Spark Solaris 9 is always 64 bit, I verified it with sys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiled PostgreSQL is actually 64 bit app? Many platforms include both 32-bit and 64-bit target toolchains. So you might be on a 64-bit platform, but that doesn't mean you aren't compiling a 32-bit executable. Please run: grep '^#define SIZEOF' config.log and post the results. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] requested shared memory size overflows size_t
Craig, Is this output correct now? #define SIZEOF_OFF_T 8 #define SIZEOF_VOID_P 8 #define SIZEOF_SIZE_T 8 #define SIZEOF_LONG 8 Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Craig Ringer [mailto:cr...@2ndquadrant.com] Sent: Sunday, March 16, 2014 2:40 PM To: Yuri Levinsky; Robert Haas Cc: Heikki Linnakangas; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] requested shared memory size overflows size_t On 03/16/2014 06:57 PM, Yuri Levinsky wrote: Dear Craig, The output is: #define SIZEOF_OFF_T 8 #define SIZEOF_VOID_P 4 #define SIZEOF_SIZE_T 4 OK, that confirms you have done a 32-bit build. You need to figure out how to invoke the 64-bit toolchain on your Solaris version. You might need to alter the PATH environment variable or set architecture-specific CFLAGS. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] requested shared memory size overflows size_t
Dear Developers, Please help with the following problem. I am running PostgreSQL 9.2.3 on SUN Solaris 9. This is 64 bit system with 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to set shared buffer 5G, the maximum possible value is 4G. When I decrease the configuration parameters and start the instance successfully: some queries fails on out of memory error. I verified kernel parameters: they looks same as on Solaris 10 and big enough. The only one difference is: Solaris 9 PostgreSQL version, in opposite to Solaris 10 and Linux, was compiled by me with default options. My kernel is: set semsys:seminfo_semmap=64 set semsys:seminfo_semmni=4096 set semsys:seminfo_semmns=4096 set semsys:seminfo_semmnu=4096 set semsys:seminfo_semume=64 set semsys:seminfo_semmsl=500 set shmsys:shminfo_shmmax=0x set shmsys:shminfo_shmmin=100 set shmsys:shminfo_shmmni=4096 set shmsys:shminfo_shmseg=100 Config. shared_buffers = 3GB temp_buffers = 2GB work_mem = 1024MB Sincerely yours, [Description: Celltick logo_highres] Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 inline: image002.jpg
Re: [HACKERS] requested shared memory size overflows size_t
Heikki, I changed postgresql.conf to decrease those parameters but no change: GMT54000FATAL: requested shared memory size overflows size_t My kernel is: set semsys:seminfo_semmap=64 set semsys:seminfo_semmni=4096 set semsys:seminfo_semmns=4096 set semsys:seminfo_semmnu=4096 set semsys:seminfo_semume=64 set semsys:seminfo_semmsl=500 set shmsys:shminfo_shmmax=0x set shmsys:shminfo_shmmin=100 set shmsys:shminfo_shmmni=4096 set shmsys:shminfo_shmseg=100 shared_buffers = 5GB# min 16 or max_connections*2, 8KB each temp_buffers = 256MB# min 100, 8KB each max_prepared_transactions = 1000# can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. work_mem = 256MB# min 64, size in KB maintenance_work_mem = 256MB# min 1024, size in KB max_stack_depth = 4MB [L.Y.] temp_buffers = 2GB seems very high. That settings is *per backend*, so if you have 10 backends that all use temporary tables, they will consume 20GB altogether for temp buffers. work_mem works similarly, except that a single query can use many times work_mem even in a single backend, so you need to be even more conservative with that. 1GB seems very high for work_mem. Try resetting these back to the defaults, and see if that works for you. Increase them gradually, and only if you have a query where the higher value really helps. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] requested shared memory size overflows size_t
Robert, Please advise me: I just downloaded the source and compiled it. Sun Spark Solaris 9 is always 64 bit, I verified it with sys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiled PostgreSQL is actually 64 bit app? Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Tuesday, March 04, 2014 4:31 PM To: Yuri Levinsky Cc: Heikki Linnakangas; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] requested shared memory size overflows size_t On Tue, Mar 4, 2014 at 6:05 AM, Yuri Levinsky yu...@celltick.com wrote: I changed postgresql.conf to decrease those parameters but no change: GMT54000FATAL: requested shared memory size overflows size_t I think this means you are running on a 32-bit operating system, or at least on a 32-bit build. That means you can't use more than 4GB of address space per process, which has to fit shared_buffers and everything else. Typically it's best not to set shared_buffers above 2-2.5GB on such systems, but the real solution is to use a 64-bit PostgreSQL. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash partitioning.
Guys, Single core CPU's are dying for Home users, my cellular has 4 cores. Today's standard is minimum 4 cores per CPU and tomorrow who knows? Parallelization sometimes is only one solution for heavy nightly jobs. From the other hand parallelization is very tricky and unpredictable when it comes into user's hands. Anyway when you have this option (same for hash partitioning) you in much better position than you don't have it. The question is: when we may hope to have it? Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Markus Wanner [mailto:mar...@bluegap.ch] Sent: Wednesday, June 26, 2013 6:56 PM To: Heikki Linnakangas Cc: Kevin Grittner; Claudio Freire; Robert Haas; Bruce Momjian; Yuri Levinsky; PostgreSQL-Dev Subject: Re: [HACKERS] Hash partitioning. On 06/26/2013 05:46 PM, Heikki Linnakangas wrote: We could also allow a large query to search a single table in parallel. A seqscan would be easy to divide into N equally-sized parts that can be scanned in parallel. It's more difficult for index scans, but even then it might be possible at least in some limited cases. So far reading sequentially is still faster than hopping between different locations. Purely from the I/O perspective, that is. For queries where the single CPU core turns into a bottle-neck and which we want to parallelize, we should ideally still do a normal, fully sequential scan and only fan out after the scan and distribute the incoming pages (or even tuples) to the multiple cores to process. Regards Markus Wanner This mail was received via Mail-SeCure System. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash partitioning.
Tom, I clearly understand your point. I actually came from corporate market such as Verizon, Barclays... I remember very good that PostgreSQL is open source, but let's forget it for a moment. The key issue for corporate market always been a partitioning(vertical and lately horizontal). Because of that Oracle has too many types and combinations of partitions, the other vendors as well. Easy partitions maintenance (automatic, simple syntax) is very important for everybody who lives in corporate RDBMS world and not only use DB's for free in order to create some virtual shop. The main purpose of partitioning in my world is to store billions of rows and be able to search by date, hour or even minute as fast as possible. When you dealing with company, which has ~350.000.000 users, and you don't want to use key/value data stores: you need hash partitioned tables and hash partitioned table clusters to perform fast search and 4-6 tables join based on user phone number for example. I believe to increase PostgreSQL popularity in corporate world, to make real money from support, the next features might be: better vertical and later horizontal partitioning, columnar-oriented tables, DB freeze for NetApp/EMC snapshots and similar. Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, June 25, 2013 10:33 PM To: Christopher Browne Cc: Yuri Levinsky; Robert Haas; Bruce Momjian; PostgreSQL Mailing Lists Subject: Re: [HACKERS] Hash partitioning. Christopher Browne cbbro...@gmail.com writes: There would indeed be merit in improving the partitioning apparatus, and actually, I think it's been a couple of years since there has been serious discussion of this. We could certainly use a partitioning mechanism that's easier to use than what we have now, which is basically build it yourself, here's the parts bin. There would also be some performance benefits from moving the partitioning logic into hard-wired code. However, I find it hard to think that hash partitioning as such is very high on the to-do list. As was pointed out upthread, the main practical advantage of partitioning is *not* performance of routine queries, but improved bulk-data management such as the ability to do periodic housecleaning by dropping a partition. If your partitioning is on a hash, you've thrown away any such advantage, because there's no real-world meaning to the way the data's been split up. So I find range and list partitioning way more plausible. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash partitioning.
Heikki, As far as I understand the height of the btree will affect the number of I/Os necessary. The height of the tree does not increase linearly with the number of records. May be I wrong in terminology but when I am trying to insert data into empty table the insertion time is increasing when number of records is growing. In order to keep indexes as small as possible I usually split the table by hash if I don't have any better alternative. On some systems hash functions +index might work faster when only index for insert and search operations. This especially usable when you have non unique index with small number of possible values that you don't know in advance or that changing between your customers. In that case the hash partition has to be used instead of index. Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Heikki Linnakangas [mailto:hlinnakan...@vmware.com] Sent: Wednesday, June 26, 2013 2:23 PM To: Yuri Levinsky Cc: Tom Lane; Christopher Browne; Robert Haas; Bruce Momjian; PostgreSQL Mailing Lists Subject: Re: [HACKERS] Hash partitioning. On 26.06.2013 11:17, Yuri Levinsky wrote: The main purpose of partitioning in my world is to store billions of rows and be able to search by date, hour or even minute as fast as possible. Hash partitioning sounds like a bad fit for that use case. A regular b-tree, possibly with range partitioning, sounds optimal for that. When you dealing with company, which has ~350.000.000 users, and you don't want to use key/value data stores: you need hash partitioned tables and hash partitioned table clusters to perform fast search and 4-6 tables join based on user phone number for example. B-trees are surprisingly fast for key-value lookups. There is no reason to believe that a hash partitioned table would be faster for that than a plain table. - Heikki This mail was received via Mail-SeCure System. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash partitioning.
Markus, It's no relation between partitions and raids despite they both distribute data somehow. By the end of the day when you use the raid you have one single device with some performance limitations. When you want to improve your data access after that and not to work with huge indexes that you unable to maintain or you don't want to use index like in case of range partition by time or hash partition: you welcome to use partitions. You typically don't want to use b-tree index when yo select more when ~1-2% of your data. Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: k...@rice.edu [mailto:k...@rice.edu] Sent: Wednesday, June 26, 2013 5:01 PM To: Markus Wanner Cc: Kevin Grittner; Claudio Freire; Robert Haas; Bruce Momjian; Yuri Levinsky; PostgreSQL-Dev Subject: Re: [HACKERS] Hash partitioning. On Wed, Jun 26, 2013 at 03:47:43PM +0200, Markus Wanner wrote: On 06/25/2013 11:52 PM, Kevin Grittner wrote: At least until we have parallel query execution. At *that* point this all changes. Can you elaborate on that, please? I currently have a hard time imagining how partitions can help performance in that case, either. At least compared to modern RAID and read-ahead capabilities. After all, RAID can be thought of as hash partitioning with a very weird hash function. Or maybe rather range partitioning on an internal key. Put another way: ideally, the system should take care of optimally distributing data across its physical storage itself. If you need to do partitioning manually for performance reasons, that's actually a deficiency of it, not a feature. I certainly agree that manageability may be a perfectly valid reason to partition your data. Maybe there even exist other good reasons. I don't think performance optimization is one. (It's more like giving the system a hint. And we all dislike hints, don't we? *ducks*) Regards Markus Wanner Hi Markus, I think he is referring to the fact that with parallel query execution, multiple partitions can be processed simultaneously instead of serially as they are now with the resulting speed increase. Regards, Ken This mail was received via Mail-SeCure System. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash partitioning.
Heiki, This is most professional explanation that I ever seen. Let me please disagree with a bottom line. It's heavily depends on amount of memory and actual index sizes. I did a benchmark ~6 years ago and I won a glass of beer. Anyway I am talking about hash partitioning as a feature and my example about compare with unique b-tree index scan is little bit extreme. In case you have 2,4,8..1024 different values (not known in advance) the index might be eliminated. That's whole the feature: no competition for hash function. Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Heikki Linnakangas [mailto:hlinnakan...@vmware.com] Sent: Wednesday, June 26, 2013 5:10 PM To: Yuri Levinsky Cc: Tom Lane; Christopher Browne; Robert Haas; Bruce Momjian; PostgreSQL Mailing Lists Subject: Re: [HACKERS] Hash partitioning. On 26.06.2013 16:41, Yuri Levinsky wrote: Heikki, As far as I understand the height of the btree will affect the number of I/Os necessary. The height of the tree does not increase linearly with the number of records. The height of a b-tree is O(log n), where n is the number of records. Informally, if we assume that you have on average, say, 1000 keys on one b-tree page, a two-level b-tree can hold one million items, and a three level one billion items, and so on. The height of the tree affects the number of I/Os needed for searches, but keep in mind that the top levels of the tree are going to be very frequently accessed and in practice will stay permanently cached. You will only perform actual I/O on the 1-2 bottom levels of the tree (or 0 if it all fits in cache) Now let's compare that with a hash partitioned table, with 1000 partitions and a b-tree index on every partition. When doing a search, you first hash the key to look up the right partition, then you search the index of that partition. This is almost equivalent to just having a b-tree that's one level taller - instead of looking up the right partition in the hash table, you look up the right child page at the root of the b-tree. From a very coarse theoretical point of view, the only difference is that you replaced the binary search on the b-tree root page with an equivalent hash lookup. A hash lookup can be somewhat cheaper than binary search, but in practice there is very little difference. There certainly isn't any difference in the number of actual I/O performed. In practice, there might be a lot of quirks and inefficiencies and locking contention etc. involved in various DBMS's, that you might be able to work around with hash partitioning. But from a theoretical point of view, there is no reason to expect just partitioning a table on a hash to make key-value lookups any faster. - Heikki This mail was received via Mail-SeCure System. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hash partitioning.
Hi, Do we have any plans to implement Hash Partitioning, maybe I missing this feature? Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 image002.jpg
Re: [HACKERS] Hash partitioning.
Bruce, Many thanks. According to PostgreSQL documentation it's only range and list partitions are supported. My question is: when I am following your advice, is PostgreSQL will do partitioning pruning on select? My expectation is: I divided my table on 128 hash partitions according let's say user_id. When I do select * from users where user_id=? , I am expecting the engine select from some particular partition according to my function. The issue is critical when you working with big tables, that you can't normally partition by range/list. The feature allow parallel select from such table: each thread might select from his own dedicated partition. The feature also (mainly) allow to decrease index b-tree level on partition key column by dividing index into smaller parts. Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Bruce Momjian [mailto:br...@momjian.us] Sent: Tuesday, June 25, 2013 4:21 PM To: Yuri Levinsky Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Hash partitioning. On Tue, Jun 25, 2013 at 03:48:19PM +0300, Yuri Levinsky wrote: Hi, Do we have any plans to implement Hash Partitioning, maybe I missing this feature? You can do it by writing your own constraint and trigger functions that control the hashing. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + This mail was received via Mail-SeCure System. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash partitioning.
Guys, I am sorry for taking your time. The reason for my question is: As former Oracle DBA and now simple beginner PostgreSQL DBA I would like to say: the current partitioning mechanism might be improved. Sorry, it seems to me far behind yesterday requirements. As model for improvement the Oracle might be taken as example. Unfortunately I am not writing an C code and see my benefit to PostgreSQL community in only rising this issue. I'll be very happy to be helpful in something else, but... Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Tuesday, June 25, 2013 6:55 PM To: Bruce Momjian Cc: Yuri Levinsky; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Hash partitioning. On Tue, Jun 25, 2013 at 11:45 AM, Bruce Momjian br...@momjian.us wrote: On Tue, Jun 25, 2013 at 11:15:24AM -0400, Robert Haas wrote: On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian br...@momjian.us wrote: Not really. Constraint exclusion won't kick in for a constraint like CHECK (hashme(a) % 16 == 3) and a WHERE clause of the form a = 42. Uh, I thought we checked the constant against every CHECK constraint and only scanned partitions that matched. Why does this not work? That's a pretty fuzzy description of what we do. For this to work, we'd have to be able to use the predicate a = 42 to prove that hashme(a) % 16 = 3 is false. But we can't actually substitute 42 in for a and then evaluate hashme(42) % 16 = 3, because we don't know that the a = 42 in the WHERE clause means exact equality for all purposes, only that it means has the numerically same value. For integers, equality under = is sufficient to prove equivalence. But for numeric values, for example, it is not. The values '42'::numeric and '42.0'::numeric are equal according to =(numeric, numeric), but they are not the same. If the hashme() function did something like length($1::text), it would get different answers for those two values. IOW, the theorem prover has no way of knowing that the hash function provided has semantics that are compatible with the opclass of the operator used in the query. I looked at predtest.c but I can't see how we accept = and = ranges, but not CHECK (a % 16 == 3). It is the '%' operator? I am not sure why the hashme() function is there. Wouldn't it work if hashme() was an immutable function? Let me back up a minute. You told the OP that he could make hash partitioning by writing his own constraint and trigger functions. I think that won't work. But I'm happy to be proven wrong. Do you have an example showing how to do it? Here's why I think it WON'T work: rhaas=# create table foo (a int, b text); CREATE TABLE rhaas=# create table foo0 (check ((a % 16) = 0)) inherits (foo); CREATE TABLE rhaas=# create table foo1 (check ((a % 16) = 1)) inherits (foo); CREATE TABLE rhaas=# create table foo2 (check ((a % 16) = 2)) inherits (foo); CREATE TABLE rhaas=# create table foo3 (check ((a % 16) = 3)) inherits (foo); CREATE TABLE rhaas=# explain select * from foo where a = 1; QUERY PLAN Append (cost=0.00..101.50 rows=25 width=36) - Seq Scan on foo (cost=0.00..0.00 rows=1 width=36) Filter: (a = 1) - Seq Scan on foo0 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) - Seq Scan on foo1 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) - Seq Scan on foo2 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) - Seq Scan on foo3 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) (11 rows) Notice we get a scan on every partition. Now let's try it with no modulo arithmetic, just a straightforward one-partition-per-value: rhaas=# create table foo (a int, b text); CREATE TABLE rhaas=# create table foo0 (check (a = 0)) inherits (foo); CREATE TABLE rhaas=# create table foo1 (check (a = 1)) inherits (foo); CREATE TABLE rhaas=# create table foo2 (check (a = 2)) inherits (foo); CREATE TABLE rhaas=# create table foo3 (check (a = 3)) inherits (foo); CREATE TABLE rhaas=# explain select * from foo where a = 1; QUERY PLAN Append (cost=0.00..25.38 rows=7 width=36) - Seq Scan on foo (cost=0.00..0.00 rows=1 width=36) Filter: (a = 1) - Seq Scan on foo1 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) (5 rows) Voila, now constraint exclusion is working. I confess that I'm not entirely clear about the details either, but the above tests speak for themselves. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company This mail was received via Mail-SeCure System. -- Sent via pgsql-hackers mailing list
