Re: [HACKERS] Feature Freeze date for 8.4
On 10/25/07, Gregory Stark [EMAIL PROTECTED] wrote: Huh, I hadn't heard of that either. The Debian package patchutils says it was downloaded from: http://cyberelk.net/tim/data/patchutils What's really cool is that patchutils also appears to have the utility I've been looking for for a while: a tool to view the differences between two diffs! I wouldn't trust that too much. I've played with it quite a bit and there are quite a few corner cases where it gives the wrong answer (or at least a different diff than I get when I manually apply them and then take the diff) -- Kris Shannon [EMAIL PROTECTED] ---(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] Constraint Exclusion + Joins?
On 5/2/06, Brandon Black [EMAIL PROTECTED] wrote: On 4/30/06, Heikki Linnakangas [EMAIL PROTECTED] wrote: On Fri, 28 Apr 2006, Brandon Black wrote: I dug around in CVS to have a look for this, and I did eventually find it (well, I found the corresponding docs patch that removed the note about not working for joins).I see it's in MAIN but not in 8_1_STABLE.Does that mean it's headed for 8.2.x when that comes about? (Sorry, I'm not terribly familiar with how you guys handle all of this). Yes.Perhaps I'm confused about the meaning of the removal of theJOINs-related caveat from the constraint exclusion docs in MAIN.What I was intending to ask about was constraint exclusion kicking in wherethe constrained column is being joined to a column of another table,with no constants involved.For a contrived example:-- CREATE TABLE basic ( basic_id INTEGER NOT NULL PRIMARY KEY, basic_data TEXT);CREATE TABLE basic_sub1 (PRIMARY KEY (basic_id),CHECK ( basic_id = 0 AND basic_id 100 ) ) INHERITS (basic);CREATE TABLE basic_sub2 (PRIMARY KEY (basic_id),CHECK ( basic_id = 100 AND basic_id 200 )) INHERITS (basic);[...]CREATE TABLE jstuff (jstuff_id INTEGER NOT NULL PRIMARY KEY, jstuff_data TEXT);EXPLAIN ANALYZE SELECT basic.* FROM basic JOIN jstuff ON(basic.basic_id = jstuff.jstuff_id) WHERE jstuff_data = 'foo';--If you only need 1 column from basic, then a subquery will do it for you: SELECT jstuff_id AS basic_id, (SELECT basic_data FROM basic WHEREbasic.basic_id = jstuff.jstuff_id) FROM jstuff WHERE jstuff_data = 'foo';If you need more than one column you can use ROW() constructors but that gets pretty dirty.
[HACKERS] Possible planner deficiency?
I have a situation where i need to select a couple of rows from an inherited table collection. The statement I'm using is: SELECT * FROM parent NATURAL JOIN interesting where interesting is a 1 column temporary table with the primary key's of the rows I'm interested in. All the child tables use the same primary key (it's actually unique across the whole inheritance tree but I'm not enforcing that) The plan that would make sense to me is: Nested Loop - Seq Scan on interesting - Append - Seq Scan on parent - Index Scan using child1_pkey on child1 parent Index Cond: (child1.pk = outer.pk) - Index Scan using child2_pkey on child2 parent Index Cond: (child2.pk = outer.pk) or something bloody close to that, but it seems that append inside a nested loop just doesn't want to happen. Is this a deficiency in the planner? With Setup: CREATE TABLE parent (pk INTEGER PRIMARY KEY, value CHAR(80)) WITHOUT OIDS; CREATE TABLE child1 () INHERITS (parent) WITHOUT OIDS; CREATE TABLE child2 () INHERITS (parent) WITHOUT OIDS; INSERT INTO child1 SELECT pk, '' AS value FROM (SELECT generate_series(1,5) * 2 - 1 AS pk) g; INSERT INTO child2 SELECT pk, '' AS value FROM (SELECT generate_series(1,5) * 2 AS pk) g; CREATE TABLE interesting (pk INTEGER PRIMARY KEY) WITHOUT OIDS; INSERT INTO interesting SELECT generate_series(1,10) * 999; ALTER TABLE child1 ADD PRIMARY KEY (pk); ALTER TABLE child2 ADD PRIMARY KEY (pk); CLUSTER parent_pkey ON parent; CLUSTER child1_pkey ON child1; CLUSTER child2_pkey ON child2; CLUSTER interesting_pkey ON interesting; VACUUM ANALYZE; Doing: EXPLAIN ANALYZE SELECT * FROM ONLY parent NATURAL JOIN interesting; EXPLAIN ANALYZE SELECT * FROM ONLY child1 NATURAL JOIN interesting; EXPLAIN ANALYZE SELECT * FROM ONLY child2 NATURAL JOIN interesting; EXPLAIN ANALYZE SELECT * FROM parent NATURAL JOIN interesting; EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM child1 UNION ALL SELECT * FROM child2) u NATURAL JOIN interesting; EXPLAIN ANALYZE SELECT * FROM child1 NATURAL JOIN interesting UNION ALL SELECT * FROM child2 NATURAL JOIN interesting; Results (on Debian 8.0.3-10): EXPLAIN ANALYZE SELECT * FROM ONLY parent NATURAL JOIN interesting; QUERY PLAN --- Hash Join (cost=1.12..15.43 rows=10 width=248) (actual time=0.272..0.272 rows=0 loops=1) Hash Cond: (outer.pk = inner.pk) - Seq Scan on parent (cost=0.00..12.80 rows=280 width=248) (actual time=0.004..0.004 rows=0 loops=1) - Hash (cost=1.10..1.10 rows=10 width=4) (actual time=0.094..0.094 rows=0 loops=1) - Seq Scan on interesting (cost=0.00..1.10 rows=10 width=4) (actual time=0.009..0.046 rows=10 loops=1) Total runtime: 0.342 ms (6 rows) EXPLAIN ANALYZE SELECT * FROM ONLY child1 NATURAL JOIN interesting; QUERY PLAN Nested Loop (cost=0.00..31.36 rows=10 width=88) (actual time=0.091..0.504 rows=5 loops=1) - Seq Scan on interesting (cost=0.00..1.10 rows=10 width=4) (actual time=0.007..0.048 rows=10 loops=1) - Index Scan using child1_pkey on child1 (cost=0.00..3.01 rows=1 width=88) (actual time=0.030..0.032 rows=0 loops=10) Index Cond: (child1.pk = outer.pk) Total runtime: 11.798 ms (5 rows) EXPLAIN ANALYZE SELECT * FROM ONLY child2 NATURAL JOIN interesting; QUERY PLAN Nested Loop (cost=0.00..31.36 rows=10 width=88) (actual time=0.109..0.365 rows=5 loops=1) - Seq Scan on interesting (cost=0.00..1.10 rows=10 width=4) (actual time=0.007..0.047 rows=10 loops=1) - Index Scan using child2_pkey on child2 (cost=0.00..3.01 rows=1 width=88) (actual time=0.017..0.019 rows=0 loops=10) Index Cond: (child2.pk = outer.pk) Total runtime: 0.450 ms (5 rows) EXPLAIN ANALYZE SELECT * FROM parent NATURAL JOIN interesting; QUERY PLAN - Hash Join (cost=1.12..3023.14 rows=3582 width=248) (actual time=7.531..1500.865 rows=10 loops=1) Hash Cond: (outer.pk = inner.pk) - Append (cost=0.00..2484.80 rows=100280 width=248) (actual time=0.049..1113.450 rows=10 loops=1) - Seq Scan on parent (cost=0.00..12.80 rows=280 width=248) (actual
Re: [PATCHES] [HACKERS] 8.0.0beta5 FailedAssertion (Crash) when casting composite types
On Sat, 11 Dec 2004 18:28:15 -0500, Tom Lane [EMAIL PROTECTED] wrote: I have applied the attached patch to HEAD in order to fix the problems discussed in this thread: http://archives.postgresql.org/pgsql-hackers/2004-12/msg00187.php regards, tom lane Well, I was going to have a stab at it, but after looking at the patch I can see my knowledge of the internals still leaves a bit to be desired (make that a LOT) Thanks for this anyway. You just simplified my coding immensely -- Kris Shannon [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 8.0.0beta5 FailedAssertion (Crash) when casting composite types
template2=# SELECT version(); version -- PostgreSQL 8.0.0beta5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 (Debian 3.4.2-3) (1 row) template2=# CREATE TABLE base (i integer); CREATE TABLE template2=# CREATE TABLE derived () INHERITS (base); CREATE TABLE template2=# INSERT INTO derived (i) VALUES (0); INSERT 0 1 template2=# SELECT derived::base FROM derived; TRAP: FailedAssertion(!(typeId == ( (olddata)-t_choice.t_datum.datum_typeid )), File: tuptoaster.c, Line: 830) Ouch! The actual case that I discovered this on had extra columns in the derived table and I was expecting some sort of error message (and I guess I got one - just a little more extreme than I was expecting) -- Kris Shannon [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
