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.
Here is the view:
On Thu, Apr 16, 2009 at 11:37 AM, Matthew Wakeling matt...@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
On Thu, 16 Apr 2009, Grzegorz Jaśkiewicz wrote:
On a second look, it looks like you are are joining that view twice,
at this point, I have no idea myself what it might be. But I guess it
has to search over 5M rows for each of 105 in other query.
I wonder what more experienced guys here will
On Thu, Apr 16, 2009 at 11:37 AM, Matthew Wakeling matt...@flymine.org wrote:
SELECT *
FROM
gene AS a1_,
intergenicregion AS a2_,
regulatoryregion AS a3_,
chromosome AS a4_,
location AS a5_,
dataset AS a6_,
LocatedSequenceFeatureOverlappingFeatures AS indirect0,
On Thu, 16 Apr 2009, Grzegorz Jaśkiewicz wrote:
On Thu, Apr 16, 2009 at 11:37 AM, Matthew Wakeling matt...@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
On Thu, 16 Apr 2009, Robert Haas wrote:
What happens if you change join_collapse_limit and from_collapse_limit
to some huge number?
http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-FROM-COLLAPSE-LIMIT
That solves the problem. So, a view is treated as a subquery
On Thu, Apr 16, 2009 at 8:05 AM, Matthew Wakeling matt...@flymine.org wrote:
On Thu, 16 Apr 2009, Robert Haas wrote:
What happens if you change join_collapse_limit and from_collapse_limit
to some huge number?
2009/4/16 Matthew Wakeling matt...@flymine.org:
On Thu, 16 Apr 2009, Grzegorz Jaśkiewicz wrote:
On Thu, Apr 16, 2009 at 11:37 AM, Matthew Wakeling matt...@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
Merlin Moncure mmonc...@gmail.com writes:
On Thu, Apr 16, 2009 at 8:05 AM, Matthew Wakeling matt...@flymine.org wrote:
That solves the problem. So, a view is treated as a subquery then?
no...the view is simply inlined into the query (think C macro) using
the rules. You just bumped into an
Tom Lane t...@sss.pgh.pa.us wrote:
Bear in mind that those limits exist to keep you from running into
exponentially increasing planning time when the size of a planning
problem gets big. Raise 'em to the moon isn't really a sane
strategy.
It might be that we could get away with raising them
On Thu, Apr 16, 2009 at 10:11 AM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Tom Lane t...@sss.pgh.pa.us wrote:
Bear in mind that those limits exist to keep you from running into
exponentially increasing planning time when the size of a planning
problem gets big. Raise 'em to the moon
On Thu, Apr 16, 2009 at 9:49 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Merlin Moncure mmonc...@gmail.com writes:
On Thu, Apr 16, 2009 at 8:05 AM, Matthew Wakeling matt...@flymine.org
wrote:
That solves the problem. So, a view is treated as a subquery then?
no...the view is simply inlined into
On Thu, 16 Apr 2009, Robert Haas wrote:
I hasten to point out that I only suggested raising them to the moon
as a DEBUGGING strategy, not a production configuration.
The problem is that we have created a view that by itself a very
time-consuming query to answer, relying on it being
Matthew Wakeling matt...@flymine.org writes:
On Thu, 16 Apr 2009, Robert Haas wrote:
I hasten to point out that I only suggested raising them to the moon
as a DEBUGGING strategy, not a production configuration.
The problem is that we have created a view that by itself a very
time-consuming
14 matches
Mail list logo
