frank <f.callaghan@ieee.org> writes:
>>>> I have a five table join that should return 1 record but, 0 comes back
>>>> this was working before running vacuum last night - as a simple test
>>>> I set enable_seqscan=off and hay-presstoe it came back !
> invent=# \i t
> psql:t:13: NOTICE: QUERY PLAN:
>
> Aggregate (cost=1283.07..1283.07 rows=1 width=36)
> -> Nested Loop (cost=940.96..1282.35 rows=289 width=36)
> -> Merge Join (cost=940.96..1000.04 rows=7 width=32)
> -> Index Scan using purch_order_pkey on purch_order (cost=0.00..28.53 rows=35 width=8)
> -> Sort (cost=940.96..940.96 rows=2001 width=24)
> -> Merge Join (cost=498.47..831.27 rows=2001 width=24)
> -> Index Scan using part_info_pkey on part_info (cost=0.00..194.06 rows=3450 width=12)
> -> Sort (cost=498.47..498.47 rows=5799 width=12)
> -> Seq Scan on po_line_item (cost=0.00..135.99 rows=5799 width=12)
> -> Index Scan using parts_pkey on parts (cost=0.00..39.38 rows=41 width=4)
>
> invent=# set enable_seqscan=off;
> SET VARIABLE
> invent=# \i t
> psql:t:13: NOTICE: QUERY PLAN:
>
> Aggregate (cost=100001283.07..100001283.07 rows=1 width=36)
> -> Nested Loop (cost=100000940.96..100001282.35 rows=289 width=36)
> -> Merge Join (cost=100000940.96..100001000.04 rows=7 width=32)
> -> Index Scan using purch_order_pkey on purch_order (cost=0.00..28.53 rows=35 width=8)
> -> Sort (cost=100000940.96..100000940.96 rows=2001 width=24)
> -> Merge Join (cost=100000498.47..100000831.27 rows=2001 width=24)
> -> Index Scan using part_info_pkey on part_info (cost=0.00..194.06 rows=3450 width=12)
> -> Sort (cost=100000498.47..100000498.47 rows=5799 width=12)
> -> Seq Scan on po_line_item (cost=100000000.00..100000135.99 rows=5799 width=12)
> -> Index Scan using parts_pkey on parts (cost=0.00..39.38 rows=41 width=4)
Well, that's pretty dang odd, because the two plans sure look the same!
So how could they generate different results?
It's possible that there is some difference in details that don't show
in EXPLAIN --- are the EXPLAIN VERBOSE results also the same?
regards, tom lane