Re: [PERFORM] CREATE STATISTICS and join selectivity

2017-11-15 Thread David Rowley
On 16 November 2017 at 09:19, Justin Pryzby wrote: > I just noticed that PG10 CREATE STATISTICS (dependencies) doesn't seem to work > for joins on multiple columns; is that right? Unfortunately, for now, they're not used for join selectivity estimates, only for the base rel selectivities. That's

[PERFORM] CREATE STATISTICS and join selectivity

2017-11-15 Thread Justin Pryzby
I just noticed that PG10 CREATE STATISTICS (dependencies) doesn't seem to work for joins on multiple columns; is that right? With statistics on table for 2017 but not 20171110: ts=# CREATE STATISTICS x ON site_id,sect_id FROM eric_enodeb_cell_2017; ts=# ANALYZE VERBOSE eric_enodeb_cell_20

Re: [PERFORM] query performance issue

2017-11-15 Thread Pavel Stehule
2017-11-15 20:58 GMT+01:00 Gunther : > > On 11/15/2017 8:12, Pavel Stehule wrote: > > There is wrong plan due wrong estimation > > for this query you should to penalize nested loop > > set enable_nestloop to off; > > before evaluation of this query > > > You are not the only one with this issue. M

Re: [PERFORM] query performance issue

2017-11-15 Thread Gunther
On 11/15/2017 8:12, Pavel Stehule wrote: There is wrong plan due wrong estimation for this query you should to penalize nested loop set enable_nestloop to off; before evaluation of this query You are not the only one with this issue. May I suggest to look at this thread a little earlier th

[PERFORM] Re: Query planner gaining the ability to replanning after start of query execution.

2017-11-15 Thread Gunter
May I suggest the recent discussion over the dreaded NL issue ... I had some similar ideas. http://www.postgresql-archive.org/OLAP-reporting-queries-fall-into-nested-loops-over-seq-scans-or-other-horrible-planner-choices-td5990160.html This could be massively useful and a huge leap in the industr

Re: [PERFORM] query performance issue

2017-11-15 Thread Justin Pryzby
On Wed, Nov 15, 2017 at 03:03:39PM +0530, Samir Magar wrote: > I am having performance issues with one of the query. > The query is taking 39 min to fetch 3.5 mil records. > > I want to reduce that time to 15 mins. > could you please suggest something to its performance? > "HashAggregate (cost=4

Re: [PERFORM] query performance issue

2017-11-15 Thread Pavel Stehule
2017-11-15 13:54 GMT+01:00 Samir Magar : > please find the EXPLAIN ANALYZE output. > > On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule > wrote: > >> Hi >> >> please send EXPLAIN ANALYZE output. >> >> Regards >> >> Pavel >> >> 2017-11-15 10:33 GMT+01:00 Samir Magar : >> >>> Hello, >>> I am having p

Re: [PERFORM] query performance issue

2017-11-15 Thread Samir Magar
please find the EXPLAIN ANALYZE output. On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule wrote: > Hi > > please send EXPLAIN ANALYZE output. > > Regards > > Pavel > > 2017-11-15 10:33 GMT+01:00 Samir Magar : > >> Hello, >> I am having performance issues with one of the query. >> The query is takin

Re: [PERFORM] query performance issue

2017-11-15 Thread Pavel Stehule
Hi please send EXPLAIN ANALYZE output. Regards Pavel 2017-11-15 10:33 GMT+01:00 Samir Magar : > Hello, > I am having performance issues with one of the query. > The query is taking 39 min to fetch 3.5 mil records. > > I want to reduce that time to 15 mins. > could you please suggest something

[PERFORM] query performance issue

2017-11-15 Thread Samir Magar
Hello, I am having performance issues with one of the query. The query is taking 39 min to fetch 3.5 mil records. I want to reduce that time to 15 mins. could you please suggest something to its performance? server configuration: CPUs = 4 memory = 16 GM shared_buffers = 3 GB work_mem = 100MB eff