Hay all :)
Firstly, just wanted to say how much I appreceate all the hard work that
has gorn into postgres. It really is a fine system.
Anyway, mybug: I have a test SELECT statement (Listing A - see "sql
listings.txt") wich produces different results under two simular setups
(Listing B and Listing C). Each setup should product the same result for
the given SELECT statement.
In the first setup (Listing B) the table "items" has 10,000 records id'ed
from 1 to 10,000. The field "number" for every record except two records
(id:500 and 600) has the value NULL., the two exceptions (id: 500 and 600)
have the value 1.
This first setup produces the correct results for the given SELECT
statement.
The SELECT statement is essentlually a dubble negitive - using two LEFT
JOINS.
- The most inner nested query selects WHERE number = 1
- Then next most inner selects everything but, and
- The most outer selects every thing but everything but WHERE number = 1
The second setup (Listing C) is identicle to the first execpt that the
table "items" has an extra field and a primary key index. The goal of this
setup is to produce a cirtian query plan that I beleive is broken, where
it seems that the "Nested Loop Left Join" has forced the filter for "WHERE
number = 1" outside or (perhaps after) a join one of the more nested joins
causeing that more nested join to cancel it self out.
Well.... that's what I make of it. I really hope you are able to
reproduce this, it took me ages to find a setup that would be reproduce
what I was observing in my developement system.
See the attached output.txt for the results and the EXPLAIN statement for
the SELECT statement under each setup.
Postgres:
"PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)"
Hardware:
AMD Athlon(tm) 64 X2 Dual Core Processor 4400+
2GB Mem
OS:
Kernel: Linux version 2.6.18-4-amd64 (Debian 2.6.18.dfsg.1-12)
OS: GNU/Linux Debian "Etch" - AMD64
Kind regards,
Adam Terrey
Data Systems - Marketing Strategy and Planning
University Relations Directorate
Australian Catholic University Limited (ABN 15 050 192 660)
42 Edward Street, North Sydney NSW 2060
PO Box 968 North Sydney NSW 2059
Telephone 02 9739 2517
Facsimile 02 9739 2960
Mobile 0421 430 824
Email: [EMAIL PROTECTED]
http://www.acu.edu.au
Australian Catholic University and the courses offered by the University
are
registered on the Commonwealth Register of Institutions and Courses for
Overseas Students (CRICOS). Provider registration codes: 00004G, 0012C, --
--
-- Listing A - Test Query
--
SELECT items.id
FROM items
LEFT JOIN (
-- Query i.
SELECT items.id
FROM items
LEFT JOIN (
-- Query ii.
SELECT id FROM items WHERE number = 1
) AS moded_items USING (id)
WHERE moded_items.id IS NULL
) AS sub_items USING (id)
WHERE sub_items.id IS NULL;
--
--
-- Listing B - The Working Setup
--
BEGIN;
CREATE TABLE items AS
SELECT
id::INTEGER,
NULL::INTEGER as number
FROM generate_series(1, 10000) AS id;
UPDATE items SET number = 1 WHERE id = 500;
UPDATE items SET number = 1 WHERE id = 600;
COMMIT;
--
--
-- Listing C - The Setup That Breaks My Test Query
--
-- Does not work
BEGIN;
CREATE TABLE items AS
SELECT
id::INTEGER,
'field that has lots of text to make this table more expensive to scan
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
v
more text more text more text text more text more text more text more text
more text '::VARCHAR AS field1,
NULL::INTEGER as number
FROM generate_series(1, 10000) AS id;
ALTER TABLE items ADD CONSTRAINT items_pkey PRIMARY KEY(id);
UPDATE items SET number = 1 WHERE id = 500;
UPDATE items SET number = 1 WHERE id = 600;
COMMIT;=Results from the working setup (Listing B)
==========================================
id
-----
500
600
(2 rows)
QUERY PLAN
--------------------------------------------------------------------------------
Hash Left Join (cost=473.04..756.38 rows=14 width=4)
Hash Cond: (public.items.id = public.items.id)
Filter: (public.items.id IS NULL)
-> Seq Scan on items (cost=0.00..161.70 rows=10670 width=4)
-> Hash (cost=472.38..472.38 rows=53 width=4)
-> Hash Left Join (cost=189.04..472.38 rows=53 width=4)
Hash Cond: (public.items.id = public.items.id)
Filter: (public.items.id IS NULL)
-> Seq Scan on items (cost=0.00..161.70 rows=10670 width=4)
-> Hash (cost=188.38..188.38 rows=53 width=4)
-> Seq Scan on items (cost=0.00..188.38 rows=53 width=4)
Filter: (number = 1)
(12 rows)
Test Query under the faulty setup (Listing C)
=============================================
id
----
(0 rows)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop Left Join (cost=476.90..1099.08 rows=1 width=4)
Filter: (public.items.id IS NULL)
-> Hash Left Join (cost=476.90..1079.30 rows=50 width=8)
Hash Cond: (public.items.id = public.items.id)
Filter: (public.items.id IS NULL)
-> Seq Scan on items (cost=0.00..351.40 rows=10040 width=4)
-> Hash (cost=351.40..351.40 rows=10040 width=4)
-> Seq Scan on items (cost=0.00..351.40 rows=10040 width=4)
-> Index Scan using items_pkey on items (cost=0.00..0.38 rows=1 width=4)
Index Cond: (public.items.id = public.items.id)
Filter: (number = 1)
(11 rows)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
