Re: [PERFORM] Query with large number of joins

On Tue, Oct 21, 2014 at 11:50 AM, Tom Lane wrote: > Marco Di Cesare writes: >> COUNT(DISTINCT "foxtrot_india"."bravo_romeo") > > Ah. That explains why the planner doesn't want to use a hash aggregation > step --- DISTINCT aggregates aren't supported with those. yup. With this q

Re: [PERFORM] Query with large number of joins

large number of joins On 10/21/2014 12:31 PM, Andrew Dunstan wrote: > Please don't top-post on the PostgreSQL lists. See > <http://idallen.com/topposting.html> Oops, sorry. >Have you tried a) either turning off geqo or setting geqo_threshold >fairly high b) setting joi

Re: [PERFORM] Query with large number of joins

Andrew Dunstan writes: > Have you tried a) either turning off geqo or setting geqo_threshold > fairly high b) setting join_collapse_limit fairly high (assuming all > the above join targets are tables and not views, setting it to > something like 25 should do the trick. Tom Lane < t...@sss.pgh

Re: [PERFORM] Query with large number of joins

On 10/21/2014 12:31 PM, Andrew Dunstan wrote: > Please don't top-post on the PostgreSQL lists. See > Oops, sorry. >Have you tried a) either turning off geqo or setting geqo_threshold fairly >high b) setting join_collapse_limit fairly high (assuming >all th

Re: [PERFORM] Query with large number of joins

Marco Di Cesare writes: > COUNT(DISTINCT "foxtrot_india"."bravo_romeo") Ah. That explains why the planner doesn't want to use a hash aggregation step --- DISTINCT aggregates aren't supported with those. regards, tom lane -- Sent via pgsql-performance

Re: [PERFORM] Query with large number of joins

Andrew Dunstan writes: > Have you tried a) either turning off geqo or setting geqo_threshold > fairly high b) setting join_collapse_limit fairly high (assuming all the > above join targets are tables and not views, setting it to something > like 25 should do the trick. You'd have to do both, I

Re: [PERFORM] Query with large number of joins

On 10/21/2014 12:09 PM, Marco Di Cesare wrote: I did not mean to imply this works any better on SQL Server. We never tried. I just meant to say this is the first time we are using Postgres so we don't have much experience with it. We tried with work_mem set to 1GB (even as high as 3GB) but it

Re: [PERFORM] Query with large number of joins

| character varying(40) | not null | extended | | lima | character varying(30) | not null | extended | | global_client_id | character varying(40) | not null | extended | | org_assess_id| integer

Re: [PERFORM] Query with large number of joins

On Mon, Oct 20, 2014 at 3:32 PM, Marco Di Cesare wrote: > We are using Postgres for the first time after being SQLServer users for a > long time so forgive for being noobs. > > > > We are using a BI tool that generates a query with an unusually large number > of joins. My understanding is that wit

Re: [PERFORM] Query with large number of joins

2014-10-20 21:59 GMT-02:00 Tom Lane : > Marco Di Cesare writes: > > We are using a BI tool that generates a query with an unusually large > number of joins. My understanding is that with this many joins Postgres > query planner can't possibly use an exhaustive search so it drops into a > heuristi

Re: [PERFORM] Query with large number of joins

Marco Di Cesare writes: > We are using a BI tool that generates a query with an unusually large number > of joins. My understanding is that with this many joins Postgres query > planner can't possibly use an exhaustive search so it drops into a heuristics > algorithm. Unfortunately, the query r

[PERFORM] Query with large number of joins

We are using Postgres for the first time after being SQLServer users for a long time so forgive for being noobs. We are using a BI tool that generates a query with an unusually large number of joins. My understanding is that with this many joins Postgres query planner can't possibly use an exha