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 <gryzman@gmail.com>) |
Ответы |
Re: Really dumb planner decision
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-performance |
On Thu, 16 Apr 2009, Grzegorz Jaśkiewicz wrote: > On Thu, Apr 16, 2009 at 11:37 AM, Matthew Wakeling <matthew@flymine.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
В списке pgsql-performance по дате отправления: