Ok, here's some more details. This is my query:
SELECT ah.FEATURE_ID, FG.TILE_NUM FROM nndb.admin_hierarchy ah JOIN NNDB.LINK_ADMIN LA ON ah.FEATURE_ID = LA.ADMIN_ID JOIN psi.FEATURE_GEOMETRY FG ON LA.LINK_ID = FG.NNDB_FEATURE_ID WHERE fg.tile_num = 8613949 and ah.feature_id in (SELECT AH2.FEATURE_ID FROM NNDB.ADMIN_HIERARCHY AH2 LEFT JOIN psi.FEATURE_GEOMETRY FG2 ON AH2.FEATURE_ID = FG2.NNDB_FEATURE_ID WHERE FG2.nndb_feature_id IS NULL) This is the output of EXPLAIN: "Hash Join (cost=87.20..105.37 rows=13 width=8)" " Hash Cond: (fg.nndb_feature_id = la.link_id)" " -> Bitmap Heap Scan on feature_geometry fg (cost=4.45..22.51 rows=26 width=8)" " Recheck Cond: (tile_num = 8613949)" " -> Bitmap Index Scan on nx_featuregeometry_tilenum (cost=0.00..4.45 rows=26 width=0)" " Index Cond: (tile_num = 8613949)" " -> Hash (cost=82.59..82.59 rows=13 width=8)" " -> Nested Loop Semi Join (cost=73.89..82.59 rows=13 width=8)" " Join Filter: (ah.feature_id = la.admin_id)" " -> Seq Scan on admin_hierarchy ah (cost=0.00..1.13 rows=13 width=4)" " -> Materialize (cost=73.89..99.95 rows=2606 width=12)" " -> Nested Loop (cost=50.20..71.29 rows=2606 width=12)" " -> HashAggregate (cost=50.20..50.21 rows=1 width=4)" " -> Hash Anti Join (cost=48.95..50.19 rows=1 width=4)" " Hash Cond: (ah2.feature_id = fg2.nndb_feature_id)" " -> Seq Scan on admin_hierarchy ah2 (cost=0.00..1.13 rows=13 width=4)" " -> Hash (cost=31.20..31.20 rows=1420 width=4)" " -> Seq Scan on feature_geometry fg2 (cost=0.00..31.20 rows=1420 width=4)" " -> Index Scan using linkadmin_adminid on link_admin la (cost=0.00..17.82 rows=261 width=8)" " Index Cond: (la.admin_id = ah2.feature_id)" The query result is empty. However, using the query SELECT ah.FEATURE_ID, FG.TILE_NUM FROM nndb.admin_hierarchy ah JOIN NNDB.LINK_ADMIN LA ON ah.FEATURE_ID = LA.ADMIN_ID JOIN psi.FEATURE_GEOMETRY FG ON LA.LINK_ID = FG.NNDB_FEATURE_ID WHERE fg.tile_num = 8613949 and ah.feature_id in (170303063) where 170303063 is one of the values returned by the subselect in the first query when run in isolation, you get a non-empty result set, so there seems to be a problem with the subselect. If you need any other information to decide whether this is one of the known bugs or a new one, just let me know what exactly you need. I can provide a backup of the three tables in question, which should be enough to isolate the problem. Best regards, Harald > -----Ursprüngliche Nachricht----- > Von: Tom Lane [mailto:t...@sss.pgh.pa.us] > Gesendet: Mittwoch, 2. September 2009 20:09 > An: Wellmann, Harald > Cc: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] Subselect problem > > "Wellmann, Harald" <harald.wellm...@harman.com> writes: > > The problem occurs with PostgreSQL 8.4.0. I cannot > reproduce it with > > PostgreSQL 8.3.7. > > There are known bugs in 8.4.0 having to do with improperly > exchanging the ordering of semijoins (IN joins) and other > joins. You haven't provided enough information to test > whether your case is one of them. > If you can try CVS branch tip or a recent nightly snapshot, > there might still be enough time to do something about it for > 8.4.1, if it isn't fixed already. > > regards, tom lane > ******************************************* innovative systems GmbH Navigation-Multimedia Geschaeftsfuehrung: Edwin Summers - Michael Juergen Mauser Sitz der Gesellschaft: Hamburg - Registergericht: Hamburg HRB 59980 ******************************************* Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und loeschen Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the contents in this e-mail is strictly forbidden. ******************************************* -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
