planner fails on HEAD - Mailing list pgsql-hackers

Hello

I have a relative simple query

SELECT q.object_id     FROM queue q          JOIN          outgoing.cps_forms f          ON f.id = q.object_id AND
q.object_type= 'cp'          JOIN          flat_file_ex fe          ON fe.id = q.rejected_flat_file_id    WHERE
q.rejected_result= 'ACTV'; 

The planner fails on this query


#0  0x00cf7424 in __kernel_vsyscall ()
#1  0x004752f1 in raise () from /lib/libc.so.6
#2  0x00476d5e in abort () from /lib/libc.so.6
#3  0x083a1dfe in ExceptionalCondition (conditionName=0x8505474
"!(innerstartsel <= innerendsel)", errorType=0x83db178
"FailedAssertion", fileName=0x8505140 "costsize.c", lineNumber=1937)
at assert.c:57
#4  0x08244cea in cost_mergejoin (path=0x93acdd4, root=0x93935d4,
sjinfo=0xbfbc9504) at costsize.c:1937
#5  0x0826f859 in create_mergejoin_path (root=0x93935d4,
joinrel=0x93aad80, jointype=JOIN_INNER, sjinfo=0xbfbc9504,
outer_path=0x93ac0f8, inner_path=0x93ac080,
restrict_clauses=0x93acce0, pathkeys=0x0,   mergeclauses=0x93adcb4, outersortkeys=0x93adc98,
innersortkeys=0x93adcd0) at pathnode.c:1576
#6  0x0824cee4 in sort_inner_and_outer (root=0x93935d4,
joinrel=0x93aad80, outerrel=0x93a9a20, innerrel=0x9393e04,
jointype=JOIN_INNER, sjinfo=0xbfbc9504, restrictlist=0x93acce0) at
joinpath.c:306
#7  add_paths_to_joinrel (root=0x93935d4, joinrel=0x93aad80,
outerrel=0x93a9a20, innerrel=0x9393e04, jointype=JOIN_INNER,
sjinfo=0xbfbc9504, restrictlist=0x93acce0) at joinpath.c:103
#8  0x0824ea12 in make_join_rel (root=0x93935d4, rel1=0x9393e04,
rel2=0x93a9a20) at joinrels.c:733
#9  0x0824ee48 in make_rels_by_clause_joins (root=0x93935d4, level=2)
at joinrels.c:268
#10 join_search_one_level (root=0x93935d4, level=2) at joinrels.c:99
#11 0x082410bf in standard_join_search (root=0x93935d4,
levels_needed=3, initial_rels=0x93ac998) at allpaths.c:1127
#12 0x082412cf in make_rel_from_joinlist (root=0x93935d4,
joinlist=<value optimized out>) at allpaths.c:1058
#13 0x08241390 in make_one_rel (root=0x93935d4, joinlist=0x93aad64) at
allpaths.c:103
#14 0x082593d0 in query_planner (root=0x93935d4, tlist=0x939f740,
tuple_fraction=0, limit_tuples=-1, cheapest_path=0xbfbc98dc,
sorted_path=0xbfbc98d8, num_groups=0xbfbc98d0) at planmain.c:259
#15 0x0825b24d in grouping_planner (root=0x93935d4, tuple_fraction=0)
at planner.c:1240
#16 0x0825cfbd in subquery_planner (glob=0x939f37c, parse=0x9370b08,
parent_root=0x0, hasRecursion=0 '\000', tuple_fraction=0,
subroot=0xbfbc9a7c) at planner.c:524
#17 0x0825d8dd in standard_planner (parse=0x9370b08, cursorOptions=0,
boundParams=0x0) at planner.c:196
#18 0x082d1b2e in pg_plan_query (querytree=0x9370b08, cursorOptions=0,
boundParams=0x0) at postgres.c:720
#19 0x082d1c33 in pg_plan_queries (querytrees=0x939f360,
cursorOptions=0, boundParams=0x0) at postgres.c:779
#20 0x082d26fc in exec_simple_query (argc=2, argv=0x92f95a4,
username=0x92f94a0 "pavel") at postgres.c:944
#21 PostgresMain (argc=2, argv=0x92f95a4, username=0x92f94a0 "pavel")
at postgres.c:3859
#22 0x082844ae in BackendRun (port=0x9316600) at postmaster.c:3587
#23 BackendStartup (port=0x9316600) at postmaster.c:3272
#24 0x08284b58 in ServerLoop () at postmaster.c:1350
#25 0x082856f3 in PostmasterMain (argc=3, argv=0x92f8308) at postmaster.c:1110
#26 0x0821cd00 in main (argc=3, argv=0x92f8308) at main.c:199

with little bit modified query planner does

ohs=# explain   SELECT q.object_id     FROM queue q          JOIN          outgoing.cps_forms f          ON f.id =
q.object_idAND q.object_type = 'cp'          JOIN          flat_file_ex fe          ON fe.id = q.rejected_flat_file_id
 WHERE q.rejected_result = 'ACTVa';                                            QUERY PLAN 
────────────────────────────────────────────────────────────────────────────────────────────────────Nested Loop
(cost=0.00..154.05rows=1 width=4)  ->  Nested Loop  (cost=0.00..145.77 rows=1 width=8)        ->  Seq Scan on queue q
(cost=0.00..137.49rows=1 width=8)              Filter: ((object_type = 'cp'::bpchar) AND 
(rejected_result = 'ACTVa'::bpchar))        ->  Index Only Scan using cps_forms_pkey on cps_forms f
(cost=0.00..8.27 rows=1 width=4)              Index Cond: (id = q.object_id)  ->  Index Only Scan using
flat_file_ex_pkeyon flat_file_ex fe 
(cost=0.00..8.27 rows=1 width=4)        Index Cond: (id = q.rejected_flat_file_id)
(8 rows)

Data and necessary indexes should be correct

ohs=# \dt+                            List of relationsSchema │         Name          │ Type  │  Owner   │  Size   │
Description
────────┼───────────────────────┼───────┼──────────┼─────────┼─────────────public │ cps_form              │ table │
postgres│ 48 kB   │public │ flat_file_ex          │ table │ pavel    │ 2632 kB │public │ np_form               │ table
│pavel    │ 432 kB  │public │ np_return_number_form │ table │ pavel    │ 48 kB   │public │ queue                 │
table│ pavel    │ 568 kB  │ 
(5 rows)

ohs=# \dt+ outgoing.*                                List of relations Schema  │         Name          │ Type  │
Owner   │    Size    │ 
Description
──────────┼───────────────────────┼───────┼─────────────┼────────────┼─────────────outgoing │ comlog                │
table│ dialtelecom │ 8192 bytes │outgoing │ cps_forms             │ table │ dialtelecom │ 40 kB      │outgoing │
cps_forms_hist       │ table │ dialtelecom │ 72 kB      │outgoing │ flatfiles             │ table │ dialtelecom │ 1328
kB   │outgoing │ flatfiles_hist        │ table │ dialtelecom │ 8192 bytes │outgoing │ log                   │ table │
dialtelecom│ 8192 bytes │outgoing │ np_forms              │ table │ dialtelecom │ 400 kB     │outgoing │ np_forms_hist
      │ table │ dialtelecom │ 560 kB     │outgoing │ opids_soap_properties │ table │ dialtelecom │ 16 kB      │outgoing
│order_numbers         │ table │ dialtelecom │ 0 bytes    │ 
(10 rows)


ohs=# select version();                                                version
──────────────────────────────────────────────────────────────────────────────────────────────────────────PostgreSQL
9.2develon i686-pc-linux-gnu, compiled by gcc (GCC) 4.5.1 
20100924 (Red Hat 4.5.1-4), 32-bit
(1 row)

[pavel@nemesis ohs]$ uname -a
Linux nemesis 2.6.35.14-106.fc14.i686.PAE #1 SMP Wed Nov 23 13:39:51
UTC 2011 i686 i686 i386 GNU/Linux

I am able to send data by some private stream

Regards

Pavel Stehule