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_id AND 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.05 rows=1 width=4)
-> Nested Loop (cost=0.00..145.77 rows=1 width=8)
-> Seq Scan on queue q (cost=0.00..137.49 rows=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_pkey on 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 relations
Schema │ 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.2devel on 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
