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

Re: Really dumb planner decision

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Really dumb planner decision
Date: 2009-04-16 11:31:42
Message-ID: alpine.DEB.2.00.0904161151470.4053@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-04-16 11:42:30
Subject: Re: Really dumb planner decision
Previous:From: Matthew WakelingDate: 2009-04-16 11:24:42
Subject: Re: Really dumb planner decision

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