Re: Really dumb planner decision

On Thu, 16 Apr 2009, Grzegorz Jaśkiewicz wrote:
> On Thu, Apr 16, 2009 at 11:37 AM, Matthew Wakeling <matthew(at)flymine(dot)org> wrote:
>>
>> I have a query that is executed really badly by Postgres. It is a nine table
>> join, where two of the tables are represented in a view. If I remove one of
>> the tables from the query, then the query runs very quickly using a
>> completely different plan.
>
> And what happens if you execute that view alone, with WHERE .. just
> like it would be a part of the whole query? ((id = 1267676))

Really quick, just like the query that works in my email.

SELECT *
FROM
gene AS a1_,
LocatedSequenceFeatureOverlappingFeatures AS indirect0
WHERE
a1_.id = 1267676
AND a1_.upstreamIntergenicRegionId = indirect0.LocatedSequenceFeature

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
(cost=0.00..38.57 rows=1 width=168)
(actual time=0.759..27.723 rows=142 loops=1)
Join Filter: ((l1.subjectid <> l2.subjectid) AND (l2.objectid = l1.objectid))
-> Nested Loop
(cost=0.00..10.02 rows=1 width=176)
(actual time=0.136..0.149 rows=1 loops=1)
-> Index Scan using gene_pkey on gene a1_
(cost=0.00..4.29 rows=1 width=160)
(actual time=0.059..0.062 rows=1 loops=1)
Index Cond: (id = 1267676)
-> Index Scan using location__key_all on location l2
(cost=0.00..5.70 rows=2 width=16)
(actual time=0.067..0.071 rows=1 loops=1)
Index Cond: (l2.subjectid = a1_.upstreamintergenicregionid)
-> Index Scan using location_bioseg on location l1
(cost=0.00..12.89 rows=696 width=16)
(actual time=0.092..24.730 rows=1237 loops=1)
Index Cond: (bioseg_create(l1.intermine_start, l1.intermine_end) && bioseg_create(l2.intermine_start, l2.intermine_end))
Total runtime: 28.051 ms
(10 rows)

Matthew

--
"Take care that thou useth the proper method when thou taketh the measure of
high-voltage circuits so that thou doth not incinerate both thee and the
meter; for verily, though thou has no account number and can be easily
replaced, the meter doth have one, and as a consequence, bringeth much woe
upon the Supply Department." -- The Ten Commandments of Electronics