Skip site navigation (1) Skip section navigation (2)

planner fails on HEAD

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: planner fails on HEAD
Date: 2011-12-04 07:40:31
Message-ID: CAFj8pRAm9bhxzi4Hnc9V=5kUsyJBwacYiX+8cP94AhwK3yTTtQ@mail.gmail.com (view raw or flat)
Thread:
Lists: 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_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(at)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

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2011-12-04 07:49:24
Subject: Re: planner fails on HEAD
Previous:From: Nikhil SontakkeDate: 2011-12-04 07:22:56
Subject: Re: Review: Non-inheritable check constraints

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group