Re: Really dumb planner decision

От: Matthew Wakeling
Тема: Re: Really dumb planner decision
Дата: ,
Msg-id: alpine.DEB.2.00.0904161151470.4053@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: Really dumb planner decision  (Grzegorz Jaśkiewicz)
Ответы: Re: Really dumb planner decision  (Robert Haas)
Список: pgsql-performance

Скрыть дерево обсуждения

Really dumb planner decision  (Matthew Wakeling, )
 Re: Really dumb planner decision  (Grzegorz Jaśkiewicz, )
  Re: Really dumb planner decision  (Matthew Wakeling, )
   Re: Really dumb planner decision  (Robert Haas, )
    Re: Really dumb planner decision  (Matthew Wakeling, )
     Re: Really dumb planner decision  (Merlin Moncure, )
      Re: Really dumb planner decision  (Tom Lane, )
       Re: Really dumb planner decision  ("Kevin Grittner", )
        Re: Really dumb planner decision  (Merlin Moncure, )
       Re: Really dumb planner decision  (Robert Haas, )
        Re: Really dumb planner decision  (Matthew Wakeling, )
         Re: Really dumb planner decision  (Tom Lane, )
 Re: Really dumb planner decision  (Grzegorz Jaśkiewicz, )
  Re: Really dumb planner decision  (Matthew Wakeling, )

On Thu, 16 Apr 2009, Grzegorz Jaśkiewicz wrote:
> On Thu, Apr 16, 2009 at 11:37 AM, Matthew Wakeling <> 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


В списке pgsql-performance по дате сообщения:

От: dforum
Дата:
Сообщение: Re: GiST index performance
От: Tom Lane
Дата:
Сообщение: Re: GiST index performance