Обсуждение: lost records !

Hi All,

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 !

I guess there is an optimizer problem, so I have two questions

1) Is there some known bad problems outstanding ?

2) How do I get the output from explain to a file & would you like it ?


TIA,           Frank.

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 !

> I guess there is an optimizer problem, so I have two questions
> 1) Is there some known bad problems outstanding ?

No, at least not in 7.0.*.

> 2) How do I get the output from explain to a file & would you like it ?

Cut and paste from a shell window is close enough ... explain would be
good, also the full schema for your tables and indexes thereon
(pg_dump -s is a good way to extract the schema info).

Don't forget the text of the query, too ;-)
        regards, tom lane

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