Here's the query:
SELECT unit.id FROM unit WHERE unit.delete = 'f' AND unit.status=2
AND (
(unit.rent >= 100.0 AND unit.rent <= 600.0)
OR (unit.rent_type = 2 AND (
(unit.slidinglow >= 100.0 AND
unit.slidinglow <= 600.0)
OR (unit.slidinglow < 100.0 AND unit.rent
> 600.0)
)))
AND unit.belowHUDFMR = 't' AND unit.features & 1::INT8 =1::INT8;Basically, it is trying to pull in all available (status=2) rental (unit.features & 1::INT8 =1::INT8) unit listings between 100 and 600 dollars per month (rent_type 2 is sliding scale rent, which we have to test for any overlap between the query values and the listing's sliding scale rent range). This query was produced by a bug in our middleware, neglecting to include city-limiting clauses (hence then being able to use an index, specifically "unit_rental_search". But still, pg ought not to belly up.
It works on 8.0.0B3, with query plan:
Seq Scan on unit (cost=0.00..2480.16 rows=11 width=8) (actual time=0.242..452.632 rows=2326 loops=1)
Filter: (("delete" = false) AND (status = 2) AND (((rent >= 100::double precision) AND (rent <= 600::double precision)) OR ((rent_type = 2) AND (((slidinglow >= 100::double precision) AND (slidinglow <= 600::double precision)) OR ((slidinglow < 100::double precision) AND (rent > 600::double precision))))) AND (belowhudfmr = true) AND ((features & 1::bigint) = 1::bigint))
Total runtime: 464.402 ms
(2326 rows returned)
Here's the backtrace ...
#0 0x900429ac in kill ()
#1 0x9009eb1c in abort ()
#2 0x002c10f4 in ExceptionalCondition (conditionName=0x31fd18 "!(((((Node*)(restrictinfo))->type) == T_RestrictInfo))", errorType=0x2fca7c "FailedAssertion", fileName=0x31fe60 "indxpath.c", lineNumber=853) at assert.c:51
#3 0x00186948 in pred_test_restrict_list (predicate=0xc7f444, restrictinfo_list=0xc8176c) at indxpath.c:853
#4 0x00186890 in pred_test_recurse_pred (predicate=0xc7f444, restrictinfo_list=0xc8176c) at indxpath.c:834
#5 0x001866cc in pred_test (predicate_list=0xc7f484, restrictinfo_list=0xc8176c) at indxpath.c:788
#6 0x0018bb60 in best_or_subclause_index (root=0x201ff38, rel=0x20200e4, subclause=0xc7fef0, retIndexInfo=0xbfffdf8c, retIndexClauses=0xbfffdf90, retIndexQuals=0xbfffdf94, retStartupCost=0xbfffdf98, retTotalCost=0xbfffdfa0) at orindxpath.c:379
#7 0x0018b8dc in best_or_subclause_indexes (root=0x201ff38, rel=0x20200e4, subclauses=0xc7f9ec) at orindxpath.c:264
#8 0x0018b7d8 in create_or_index_paths (root=0x201ff38, rel=0x20200e4) at orindxpath.c:207
#9 0x001804c0 in set_plain_rel_pathlist (root=0x201ff38, rel=0x20200e4, rte=0x201ffc4) at allpaths.c:181
#10 0x00180400 in set_base_rel_pathlists (root=0x201ff38) at allpaths.c:135
#11 0x001801b8 in make_one_rel (root=0x201ff38) at allpaths.c:79
#12 0x00194604 in query_planner (root=0x201ff38, tlist=0xc7b2c4, tuple_fraction=0, cheapest_path=0xbfffe278, sorted_path=0xbfffe27c) at planmain.c:154
#13 0x00195b28 in grouping_planner (parse=0x201ff38, tuple_fraction=0) at planner.c:935
#14 0x00194d6c in subquery_planner (parse=0x201ff38, tuple_fraction=0) at planner.c:326
#15 0x001948b4 in planner (parse=0x201ff38, isCursor=0 '\0', cursorOptions=0, boundParams=0x0) at planner.c:129
#16 0x001fdbb8 in pg_plan_query (querytree=0x201ff38, boundParams=0x0) at postgres.c:647
#17 0x001fdd04 in pg_plan_queries (querytrees=0xc7b24c, boundParams=0x0, needSnapshot=0 '\0') at postgres.c:715
#18 0x001fe02c in exec_simple_query (query_string=0x201e634 "SELECT unit.id FROM unit WHERE unit.delete = 'f' AND unit.status=2\nAND (\n(unit.rent >= 100.0 AND unit.rent <= 600.0)\nOR (unit.rent_type = 2 AND (\n(unit.slidinglow >= 100.0 AND unit.slidinglow <= 600."...) at postgres.c:874
#19 0x00201a0c in PostgresMain (argc=5, argv=0x2001c74, username=0x2001c4c "social") at postgres.c:2961
#20 0x001b29e0 in BackendRun (port=0xd00e70) at postmaster.c:2773
#21 0x001b1dfc in BackendStartup (port=0xd00e70) at postmaster.c:2399
#22 0x001af454 in ServerLoop () at postmaster.c:1144
#23 0x001aec64 in PostmasterMain (argc=6, argv=0xd00760) at postmaster.c:863
#24 0x001539ec in main (argc=6, argv=0xd00760) at main.c:270
Here's the table and index definitions ...
(sorry table is *huge* -- gotta bust it up one of these days).
social=# \d unit
Table "public.unit"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
id | bigint | not null
city | bigint |
zipcode | bigint |
building | bigint |
waitinglist_id | bigint |
leadpaintunit | bigint |
status | integer | not null
features | bigint |
deletedate | timestamp with time zone |
lastupdatedate | timestamp with time zone |
delete | boolean | not null
version | integer | not null
rent_type | integer | not null
rent | double precision | not null
slidinglow | double precision | not null
median_rent_20 | double precision |
median_rent_30 | double precision |
median_rent_40 | double precision |
median_rent_50 | double precision |
median_rent_60 | double precision |
securitydeposit | double precision | not null
minimumlease | double precision | not null
income_based_ss_rent | boolean |
medianrentnorealrent | boolean | not null
negotiabledeposit | boolean |
forsaleprice | integer |
minimum_down_payment | integer |
forsale_sliding_low | integer |
homeowner_fee | integer |
negotiabledownpayment | boolean |
bedroom | double precision | not null
bathroom | double precision | not null
yearbuilt | integer | not null
maxoccupancyadult | integer | not null
maxoccupancychild | integer | not null
squarefeet | integer | not null
section8 | boolean | not null
pets | boolean | not null
smoking | boolean | not null
furniture | boolean | not null
availabledate | timestamp with time zone |
marketdate | timestamp with time zone |
postdate | timestamp with time zone |
fridge | boolean | not null
washer | boolean | not null
washerhookup | boolean | not null
dryer | boolean | not null
gasincluded | boolean | not null
electricincluded | boolean | not null
waterincluded | boolean | not null
gasheat | boolean | not null
oilheat | boolean | not null
electricheat | boolean | not null
electricwater | boolean | not null
gaswater | boolean | not null
taxcredit | boolean | not null
subsidized | boolean | not null
senior | boolean | not null
seniorlicensed | boolean | not null
leadpaint | boolean | not null
air | boolean | not null
dishwasher | boolean | not null
trashcollection | boolean | not null
yardmaintenance | boolean | not null
belowhudfmr | boolean | not null
deck | boolean |
purchasemoreparking | boolean |
trashpickup | boolean |
basementtype | integer |
parkingtype | integer |
yardmaintenancetype | integer |
parkingspaces | integer |
parkingfee | double precision |
trashpickupfee | double precision |
yardmaintenancefee | double precision |
pubxport_blocks | integer | not null
access | boolean | not null
represents | integer | not null
available_count | integer | not null
waiting_list | boolean | not null
type | text |
street | text |
street2 | text |
description | text |
petsconditions | text |
stovetype | text |
flooring | text |
promotion | text |
amenities | text |
parkingcomment | text |
qualifiers | text |
upgrades_addons | text |
lastmodusername | text |
sliding_qualifiers | text |
Indexes:
"unit_pkey" PRIMARY KEY, btree (id)
"unit_building" btree (building, represents)
"unit_forsale_search" btree (city, forsaleprice) WHERE forsaleprice > 0
"unit_rental_search" btree (city, status, belowhudfmr, "delete") WHERE statu
s = 2 AND belowhudfmr = true AND "delete" = false
"unit_sys_disabled" btree (building, status) WHERE status = 8
Foreign-key constraints:
"unit_building_fkey" FOREIGN KEY (building) REFERENCES building(id)
"unit_city_fkey" FOREIGN KEY (city) REFERENCES housingcity(id)
"unit_leadpaintunit_fkey" FOREIGN KEY (leadpaintunit) REFERENCES leadpaintun
it(id)
"unit_waitinglist_id_fkey" FOREIGN KEY (waitinglist_id) REFERENCES waiting_l
ist(id)
"unit_zipcode_fkey" FOREIGN KEY (zipcode) REFERENCES zipcode(id)
---- James Robinson Socialserve.com
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
