Re: [BUGS] BUG #3494: may be Query Error: subplan does not executed
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Sergey Burladyan wrote: >> i have query with join of two table and 'where' filter it result by subplan >> which have references to join result, but this subplan not executed and >> result is incorrect. This subplan also not exist in explain analyze output. > I can reproduce this on 8.1 and 8.2 branch heads, but not on 8.3 HEAD. > I've simplified test case down to this: You don't even need the sub-select: regression=# explain SELECT * FROM test1, test2 WHERE test1.id = test2.id AND test1.id = test1.id*test2.id; QUERY PLAN - Merge Join (cost=299.56..653.73 rows=22898 width=8) Merge Cond: (test1.id = test2.id) -> Sort (cost=149.78..155.13 rows=2140 width=4) Sort Key: test1.id -> Seq Scan on test1 (cost=0.00..31.40 rows=2140 width=4) -> Sort (cost=149.78..155.13 rows=2140 width=4) Sort Key: test2.id -> Seq Scan on test2 (cost=0.00..31.40 rows=2140 width=4) (8 rows) It appears that join_clause_is_redundant() is rejecting the clause as redundant. I suppose some part of that machinery gets confused by the fact that the RHS of the clause references both relations. The EquivalenceClass rewrite cleaned this whole area up greatly, so no surprise that the bug is gone in HEAD. No time to look at it more now. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3495: PostgreSQL does not use POSIX SHM or SEM in place of SYSV equivalents
The following bug has been logged online: Bug reference: 3495 Logged by: Roger Leigh Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Debian GNU/Linux (glibc 2.6, linux 2.6.22) Description:PostgreSQL does not use POSIX SHM or SEM in place of SYSV equivalents Details: PostgreSQL is using the old SYSV shared memory and semaphores, as evidenced by ipcs: % sudo ipcs -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x0052e2c1 0 postgres 60029384704 2 -- Semaphore Arrays keysemid owner perms nsems 0x0052e2c1 0 postgres 60017 0x0052e2c2 32769 postgres 60017 0x0052e2c3 65538 postgres 60017 0x0052e2c4 98307 postgres 60017 0x0052e2c5 131076 postgres 60017 0x0052e2c6 163845 postgres 60017 0x0052e2c7 196614 postgres 60017 -- Message Queues keymsqid owner perms used-bytes messages However, the SYSV memory limit is set in-kernel and while it can be easily changed, it is rather less flexible than POSIX SHM support: % df /dev/shm Filesystem 1K-blocks Used Available Use% Mounted on tmpfs 257584 0257584 0% /dev/shm On this system, that limit could be increased up to 6 GiB (though the above would probably be the upper useful limit given that this is VM-backed). Having the option of utilising either form of shared memory would be rather useful. Regards, Roger ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3495: PostgreSQL does not use POSIX SHM or SEM in place of SYSV equivalents
Roger Leigh wrote: > The following bug has been logged online: Not a bug, but a feature request... > PostgreSQL is using the old SYSV shared memory and semaphores, as evidenced > by ipcs: > ... > However, the SYSV memory limit is set in-kernel and while it can be easily > changed, it is rather less flexible than POSIX SHM support: > ... > Having the option of utilising either form of shared memory would be rather > useful. A patch to do that submitted was submitted in Spring, but it was not accepted for 8.3 because there was some issues with it. Search the archives for more details. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3495: PostgreSQL does not use POSIX SHM or SEM in place of SYSV equivalents
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Roger Leigh wrote: >> PostgreSQL is using the old SYSV shared memory and semaphores, as evidenced >> by ipcs: > A patch to do that submitted was submitted in Spring, but it was not > accepted for 8.3 because there was some issues with it. More strongly: it never will be accepted because the POSIX API doesn't offer the features we need. You can use POSIX semaphores if you choose (there are compile options for that), but experience to date says that that degrades performance. POSIX shared memory is simply not happening. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
