Re: How to reduce query planning time (10s)

x27;t exhibit the performance problem, so it must be related to table contents). Is there any way to validate the bloated index hypothesis before I invest too much in this direction? iulian On Mon, Dec 20, 2021 at 1:42 PM Pavel Stehule wrote: > Hi > > po 20. 12. 2021 v 13:31 odesílatel

How to reduce query planning time (10s)

Hi, I was analyzing the query performance in a certain code path and noticed that practically all of the query time is spent planning (11s planning, 200ms execution time). Here is the output of EXPLAIN ANALYZE. I tried using a prepared statement with three parameters (run_id, and the two text comp

Re: Query plan prefers hash join when nested loop is much faster

On Tue, Aug 25, 2020 at 12:36 PM David Rowley wrote: > On Tue, 25 Aug 2020 at 22:10, iulian dragos > wrote: > > Thanks for the tip! Indeed, `n_distinct` isn't right. I found it in > pg_stats set at 131736.0, but the actual number is much higher: 210104361. > I tried to s

Re: Query plan prefers hash join when nested loop is much faster

On Tue, Aug 25, 2020 at 12:27 AM David Rowley wrote: > On Sat, 22 Aug 2020 at 00:35, iulian dragos > wrote: > > I am trying to understand why the query planner insists on using a hash > join, and how to make it choose the better option, which in this case would >

Re: Query plan prefers hash join when nested loop is much faster

On Mon, Aug 24, 2020 at 4:21 PM iulian dragos wrote: > Hi Michael, > > Thanks for the answer. It's an RDS instance using SSD storage and the > default `random_page_cost` set to 4.0. I don't expect a lot of repetitive > queries here, so I think caching may not be extr

Re: Query plan prefers hash join when nested loop is much faster

Hi Michael, Thanks for the answer. It's an RDS instance using SSD storage and the default `random_page_cost` set to 4.0. I don't expect a lot of repetitive queries here, so I think caching may not be extremely useful. I wonder if the selectivity of the query is wrongly estimated (out of 500 millio

Query plan prefers hash join when nested loop is much faster

Hi, I am trying to understand why the query planner insists on using a hash join, and how to make it choose the better option, which in this case would be a nested loop. I have two tables: // about 200 million rows CREATE TABLE module_result( *id* bigserial PRIMARY KEY, name_id bigint NOT

Query plan prefers hash join when nested loop is much faster

Hi, I am trying to understand why the query planner insists on using a hash join, and how to make it choose the better option, which in this case would be a nested loop. I have two tables: // about 200 million rows CREATE TABLE module_result( *id* bigserial PRIMARY KEY, name_id bigint NOT