Re: Postgres Query Plan using wrong index

resulting query plan which uses just the index on col_b: postgres=# explain analyze select min(col_b) from test_table where col_a > 4996; Result (cost=62.13..62.14 rows=1 width=4) (actual time=536.648..536.649 rows=1 loop

Re: Postgres Query Plan using wrong index

is col_a > 5000, but switch to the slower (col_b) index when the filter is not at the edge of the range, like col_a > 4996? For reference, here’s the query plan when filtering for col_a > 5000. It uses the correct index on (col_a, col_b). postgres=# explain analyze select min(col_b)

Re: Postgres Query Plan using wrong index

On Thu, 3 Apr 2025 at 18:07, Tom Lane wrote: > A simple-minded approach could be to just be pessimistic, and > increase our estimate of how many rows would need to be scanned as a > consequence of noticing that the columns have significant correlation. > The shape of that penalty function would be

Re: Postgres Query Plan using wrong index

David Rowley writes: > On Thu, 3 Apr 2025 at 16:24, Manikandan Swaminathan > wrote: >> why doesn’t making a multivariate statistic make a difference? > Extended statistics won't help you here. "dependencies" just estimates > functional dependencies between the columns mentioned in the ON > claus

Re: Postgres Query Plan using wrong index

On Thu, 3 Apr 2025 at 16:24, Manikandan Swaminathan wrote: > Since you mentioned the planner not knowing about the correlation between the > columns, I’m curious, why doesn’t making a multivariate statistic make a > difference? > > > CREATE STATISTICS col_a_col_b_stats (dependencies) ON col_a, c

Re: Postgres Query Plan using wrong index

t at the edge of the range, like col_a > 4996? At some point, as less and less of the col_a-major index would need to be scanned, there's a crossover in the cost estimates for the two ways of doing this. I would not have cared to predict where the crossover is, but you evidently found i

Postgres Query Plan using wrong index

am aggregating the result with min(col_b). Both columns are covered by "idx_col_b_a". However, explain analyze indicates otherwise: postgres=# explain analyze select min(col_b) from test_table where col_a > 4996;

Re: Postgres Query Plan using wrong index

Manikandan Swaminathan writes: > 4. When running the following query, I would expect the index "idx_col_b_a" > to be used: select min(col_b) from test_table where col_a > 4996. > I have a range-based filter on col_a, and am aggregating the result with > min(col_b). Both columns are covered by "id

Re: About PostgreSQL Query Plan

Hello, Okay, thank you very much, Mr. David, for your support and the information, Eşref To: Eşref Halıcıoğlu (esref.halicio...@primeit.com.tr);Cc: pgsql-general@lists.postgresql.org;Subject: About PostgreSQL Query Plan;14.01.2025, 13:09, "David Rowley" :On Tue, 14 Jan

Re: About PostgreSQL Query Plan

On Tue, 14 Jan 2025 at 03:45, Eşref Halıcıoğlu wrote: > I do not fully understand the logic of this issue. I would be very grateful > if you can share information on the subject. > > The query plan is as follows. > > Update on "test_table1" tt1 (cost=0.13..159112.8

Re: About PostgreSQL Query Plan

would be very grateful if you could help me. Thank you, Eşref Halıcıoğlu To: Eşref Halıcıoğlu (esref.halicio...@primeit.com.tr);Cc: pgsql-general@lists.postgresql.org;Subject: About PostgreSQL Query Plan;13.01.2025, 19:56, "hubert depesz lubaczewski" :On Mon, Jan 13, 2025 at

Re: About PostgreSQL Query Plan

;))  ->  Index Scan using partitiontable_2024_12_pkey on "PartitionTable_2024_12" t4  (cost=0.43..4.34 rows=1 width=38) (never executed)    Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval))

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 08:01:56PM +0300, Eşref Halıcıoğlu wrote: > Yes, you are right; it seems that only 4 batches had data changes. However, > the query also accessed other batches and then removed > them again. What could be the reason for this and how can it be solved? >   > Obviously, I woul

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 07:48:09PM +0300, Eşref Halıcıoğlu wrote: > Hello, >   > Yes, you are right; this query is not a SELECT, it is an UPDATE query, there > was a mistake in expressing it here. The columns I want > to update here only operate on data from the last 3 months time interval. >   >

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 05:26:09PM +0300, Eşref Halıcıoğlu wrote: > Hello, >   > I have a query in PostgreSQL and I want this query to retrieve only data from > the last 3 months. However, when I examine the query > plan, I see that all partitions are listed. Please note that your

About PostgreSQL Query Plan

Hello, I have a query in PostgreSQL and I want this query to retrieve only data from the last 3 months. However, when I examine the query plan, I see that all partitions are listed. This raises a few questions in my mind:Are all partitions really being accessed, or only the partitions of the last

Re: query plan

=?UTF-8?Q?Torsten_F=C3=B6rtsch?= writes: > This is part of a query plan: > Nested Loop Left Join (cost=26.32..47078866.36 rows=1344945195 width=626) >-> Nested Loop Left Join (cost=25.74..5312.48 rows=1344945195 > width=608) > -> Nested Loop Left Join (co

query plan

Hi, This is part of a query plan: Nested Loop Left Join (cost=26.32..47078866.36 rows=1344945195 width=626) -> Nested Loop Left Join (cost=25.74..5312.48 rows=1344945195 width=608) -> Nested Loop Left Join (cost=6.79..2876.77 rows=102 width=373) -> Ne

Re: Inefficient query plan for SELECT ... EXCEPT ...

Thank you all for the answers, they covered me well. Is this worth a bug report? I can file one if the issue is not known. No. It's just a missing optimisation. We know about it. It's good I shot an email first then. FWIW my usual way in other projects would be to check the bugtracker, and

Re: Inefficient query plan for SELECT ... EXCEPT ...

David Rowley writes: > It would be possible to have some sort of MergeExcept operator and > have the planner consider that. Unfortunately, since the upper planner > was changed a few years ago to have it consider paths the same as the > join planner does, nobody has yet come back to the union plan

Re: Inefficient query plan for SELECT ... EXCEPT ...

rforms much better, and I believe is equivalent. I find it less readable > than the query in question though. Plus, I have a bunch of SELECT-EXCEPT > queries (with smaller right-side tables) in my application that I would > hate to change them all to the ugliest equivalent. Under what conditio

Re: Inefficient query plan for SELECT ... EXCEPT ...

On Tue, Oct 31, 2023 at 3:41 PM Dimitrios Apostolou wrote: > > Is this worth a bug report? I can file one if the issue is not known. > Or am I misunderstanding the implications of the SELECT-EXCEPT query? > > In the meantime I have replaced the query with a LEFT OUTER JOIN which > performs much b

Inefficient query plan for SELECT ... EXCEPT ...

Hello list, I'm getting an inefficient query plan for a SELECT ... EXCEPT ... query, where the left side is a very short table (even zero-length sometimes, but also also rarely can be as long as 200K rows), and the right side is a table with 10M UNIQUE NOT NULL rows: \d test_dat

Re: Query plan regression between CTE and views

I'm on PostgreSQL 15 with essentially a stock configuration. On Tue, Aug 15, 2023 at 8:58 AM Ron wrote: > > On 8/14/23 09:54, David Gilman wrote: > > I have a query that was originally written as a handful of CTEs out of > > convenience. It is producing a reasonable que

Re: Query plan regression between CTE and views

On 8/14/23 09:54, David Gilman wrote: I have a query that was originally written as a handful of CTEs out of convenience. It is producing a reasonable query plan because the CTE materialization was kicking in at an appropriate place. The CTEs aren't totally linear. The graph looks like

Query plan regression between CTE and views

I have a query that was originally written as a handful of CTEs out of convenience. It is producing a reasonable query plan because the CTE materialization was kicking in at an appropriate place. The CTEs aren't totally linear. The graph looks like this, where A, B, C and D are CTEs, and B

Re: Query plan for "id IS NULL" on PK

Thank you all for your responses! I will continue to put pressure on the vendor (Stitch Data, if anyone knows folks there) to address the issue on their end with the query being issued. Best, Ben Chrobot On Tue, Feb 14, 2023 at 11:11 PM Tom Lane wrote: > David Rowley writes: > > On Wed, 15 F

Re: Query plan for "id IS NULL" on PK

On 2/14/23 18:21, David Rowley wrote: [snip] since it likely only applies to nearly zero real-world cases Are you sure? -- Born in Arizona, moved to Babylonia.

Re: Query plan for "id IS NULL" on PK

David Rowley writes: > On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer wrote: >> OTOH it could also be argued that the optimizer should be able to >> perform the same simplifications as I did above and produce the same >> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?)) >> as for WHERE

Re: Query plan for "id IS NULL" on PK

On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer wrote: > OTOH it could also be argued that the optimizer should be able to > perform the same simplifications as I did above and produce the same > code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?)) > as for WHERE (("id" > ?)) AND (("id" <=

Re: Query plan for "id IS NULL" on PK

On 2/14/23 15:43, Peter J. Holzer wrote: On 2023-02-14 15:36:32 -0700, Rob Sargent wrote: But if the query is supposed to be generic and re-used in a situation where id could be null, wouldn't the null id records be fetched every time? No, they will never be fetched because of the AND (("id" <=

Re: Query plan for "id IS NULL" on PK

On 2023-02-14 15:36:32 -0700, Rob Sargent wrote: > But if the query is supposed to be generic and re-used in a situation where id > could be null, wouldn't the null id records be fetched every time?  No, they will never be fetched because of the AND (("id" <= ?)). hp -- _ | Peter J.

Re: Query plan for "id IS NULL" on PK

On 2023-02-14 17:04:51 -0500, Ben Chrobot wrote: > We have a large table (~470 million rows) with integer primary key id (not > null) on a Postgres 14.5 cluster. A third-party tool is attempting to perform > a > SELECT-based full table copy in preparation for log-based sync with a query > like the

Re: Query plan for "id IS NULL" on PK

On 2/14/23 15:30, David G. Johnston wrote: On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote: When will id be null in a primary key? The OP seems to be aware of this... "We cannot change the query being executed. Is there any way we can make the query planner ignore `OR (id IS NULL)` (

Re: Query plan for "id IS NULL" on PK

On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote: > > When will id be null in a primary key? > > The OP seems to be aware of this... "We cannot change the query being executed. Is there any way we can make the query planner ignore `OR (id IS NULL)` (as that will never be the case for the PK) an

Re: Query plan for "id IS NULL" on PK

uot; <= ?)) ORDER  BY "id" LIMIT 5; The lower bound increments by batch size (50k) while the upper bound is always the `max(id)`, in our case around 575,000,000. The q

Query plan for "id IS NULL" on PK

IT 5; The lower bound increments by batch size (50k) while the upper bound is always the `max(id)`, in our case around

Re: Missing query plan for auto_explain.

On Mon, Sep 12, 2022 at 05:34:37PM +0100, Matheus Martin wrote: > Understood. I have run a prepared statement with the query in question > through `psql` and JIT was not used (see plan below), however please note > that the long response times were never reproducible from `psql`, they only > happen

Re: Missing query plan for auto_explain.

. QUERY PLAN --- Limit (cost=31.41..31.41 rows=1 width=707) (actual

Re: Missing query plan for auto_explain.

On Thu, Sep 8, 2022 at 1:18 PM Matheus Martin wrote: > We do have JIT enabled `jit=on` with `jit_above_cost=10`. > > I am sorry but I don't quite understand what role JIT plays in > this situation with `auto_explain`. Could you please elaborate on that? > > In your log - time spent during the

Re: Missing query plan for auto_explain.

We do have JIT enabled `jit=on` with `jit_above_cost=10`. I am sorry but I don't quite understand what role JIT plays in this situation with `auto_explain`. Could you please elaborate on that? On Tue, 6 Sept 2022 at 00:29, Maxim Boguk wrote: > > > On Tue, Aug 30, 2022 at 1:38 PM Matheus Mar

Re: Missing query plan for auto_explain.

On Tue, Aug 30, 2022 at 1:38 PM Matheus Martin < matheus.mar...@voidbridge.com> wrote: > Our Postgres recently started reporting considerably different execution > times for the same query. When executed from our JDBC application the > Postgres logs report an average execution time of 1500 ms bu

Re: Missing query plan for auto_explain.

`auto_explain.log_min_duration` is set to 500 ms. On Mon, 5 Sept 2022 at 12:35, Peter J. Holzer wrote: > On 2022-09-02 10:58:58 +0100, Matheus Martin wrote: > > Yes, we do see some plans logged by the auto_explain. We couldn't find a > > `auto_explain.log_min_duration_statements` setting > > Th

Re: Missing query plan for auto_explain.

On 2022-09-02 10:58:58 +0100, Matheus Martin wrote: > Yes, we do see some plans logged by the auto_explain. We couldn't find a > `auto_explain.log_min_duration_statements` setting This is weird as the documentation says: | Note that the default behavior is to do nothing, so you must set at | leas

Re: Missing query plan for auto_explain.

Yes, we do see some plans logged by the auto_explain. We couldn't find a `auto_explain.log_min_duration_statements` setting but `log_min_duration_statement` as in https://www.postgresql.org/docs/current/runtime-config-logging.html is set to 100 ms. Unfortunately, due to the amount of traffic we ha

Re: Missing query plan for auto_explain.

Hi, On Thu, Sep 01, 2022 at 08:20:13PM +0100, Matheus Martin wrote: > We tried running the prepared statement six times as suggested but wasn't > still able to recreate the original problem. > > Perhaps more concerning/relevant is that we have not found any explanation > to why the explain plan i

Re: Missing query plan for auto_explain.

We tried running the prepared statement six times as suggested but wasn't still able to recreate the original problem. Perhaps more concerning/relevant is that we have not found any explanation to why the explain plan is not being logged by `auto_explain`. Could this be a bug? Shall we report it?

Re: Missing query plan for auto_explain.

Alvaro Herrera writes: > On 2022-Aug-30, Matheus Martin wrote: >> Good idea on using an actual prepared statement but unfortunately it didn't >> produce any different result. > I should have also mentioned to try the EXPLAIN EXECUTE six times and > see if the last one produces a different plan.

Re: Missing query plan for auto_explain.

On 2022-Aug-30, Matheus Martin wrote: > Good idea on using an actual prepared statement but unfortunately it didn't > produce any different result. I should have also mentioned to try the EXPLAIN EXECUTE six times and see if the last one produces a different plan. That's when it switches from pl

Re: Missing query plan for auto_explain.

The threshold for `auto_explain` was changed to 500 ms and explain plans are still not being logged. On Tue, 30 Aug 2022 at 13:30, Julien Rouhaud wrote: > Hi, > > On Tue, Aug 30, 2022 at 01:16:43PM +0200, Alvaro Herrera wrote: > > On 2022-Aug-30, Matheus Martin wrote: > > > > > Our Postgres rece

Re: Missing query plan for auto_explain.

Good idea on using an actual prepared statement but unfortunately it didn't produce any different result. Could you please elaborate a bit on your advice concerning ExecutorEnd/PortalCleanup? I am afraid it doesn't mean much to me. On Tue, 30 Aug 2022 at 12:16, Alvaro Herrera wrote: > On 2022-A

Re: Missing query plan for auto_explain.

Hi, On Tue, Aug 30, 2022 at 01:16:43PM +0200, Alvaro Herrera wrote: > On 2022-Aug-30, Matheus Martin wrote: > > > Our Postgres recently started reporting considerably different > > execution times for the same query. When executed from our JDBC > > application the Postgres logs report an average

Re: Missing query plan for auto_explain.

On 2022-Aug-30, Matheus Martin wrote: > Our Postgres recently started reporting considerably different > execution times for the same query. When executed from our JDBC > application the Postgres logs report an average execution time of 1500 > ms but when the query is manually executed through `ps

Missing query plan for auto_explain.

Our Postgres recently started reporting considerably different execution times for the same query. When executed from our JDBC application the Postgres logs report an average execution time of 1500 ms but when the query is manually executed through `psql` it doesn't take longer than 50 ms. With a

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 set it manually, but the p

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

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 > set it manually, but the plan is still the same (both the actual number and a > perc

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 > be a nested loop. > > > |

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

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 be a > nested loop. > | -> Index Scan using > test_result_module

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

emely useful. I wonder if > the selectivity of the query is wrongly estimated (out of 500 million rows, > only a few thousands are returned). > > I tried lowering the `random_page_cost` to 1.2 and it didn't make a > difference in the query plan. > I experimented a bit more with d

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

out of 500 million rows, only a few thousands are returned). I tried lowering the `random_page_cost` to 1.2 and it didn't make a difference in the query plan. iulian On Fri, Aug 21, 2020 at 6:30 PM Michael Lewis wrote: > Your system is preferring sequential scan to > using test_result_mo

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

Your system is preferring sequential scan to using test_result_module_result_id_idx in this case. What type of storage do you use, what type of cache hits do you expect, and what do you have random_page_cost set to? That comes to mind as a significant factor in choosing index scans based on costs.

Query plan prefers hash join when nested loop is much faster

dule_result ON module_result.id = test_result.module_result_id where module_resul t.run_id=158523 group by test_result.status +--

Query plan prefers hash join when nested loop is much faster

dule_result ON module_result.id = test_result.module_result_id where module_resul t.run_id=158523 group by test_result.status +--

Re: Table with many NULLS for indexed column yields strange query plan

Seqscans are not disabled. Also, this is PostgreSQL 10.11 if that helps. Costs are as follows: seq_page_cost --- 1 random_page_cost -- 1.5 It is odd that it does not just do a seqscan on table3. It's a very small table... only like 36 rows. I'd think the pla

Re: Table with many NULLS for indexed column yields strange query plan

"Peter J. Holzer" writes: > How is the selectivity of "type"? Would an index on that column help? The EXPLAIN results say that the "type = 'Standard'" condition is completely not selective: in both plans, there is no "Rows Removed by Filter" indication where it's applied, indicating that it did n

Re: Table with many NULLS for indexed column yields strange query plan

On 2020-03-05 18:08:53 -0700, greigwise wrote: > I have a query like this: > > SELECT "table1".* FROM "table1" > INNER JOIN "table2" ON "table2"."table1_id" = "table1"."id" > INNER JOIN "table3" ON "table3"."id" = "table2"."table3_id" > WHERE "table3"."number" = '' > AND ("table2"."type") IN

Table with many NULLS for indexed column yields strange query plan

I have a query like this: SELECT "table1".* FROM "table1" INNER JOIN "table2" ON "table2"."table1_id" = "table1"."id" INNER JOIN "table3" ON "table3"."id" = "table2"."table3_id" WHERE "table3"."number" = '' AND ("table2"."type") IN ('Standard') ; table2 has a large number of NULLS in the col

No partition pruning when initializing query plan with LATERAL JOIN and aggregates

FROM demo2 WHERE demo2.key = demo.key ) d ON TRUE WHERE demo.key = 1; QUERY PLAN --- Nested Loop (cost=0.00..2.03

Re: Complex filters -> Bad row estimates -> bad query plan

-- I'm thinking the OFFSET 0 create an optimization barrier that prevents the planner from collapsing that sub-query into the top query, and enforces ordering in the query? That's my understanding. I think it is an optimizer hint by another name. I used to put things in a CTE (which is always mate

Re: Complex filters -> Bad row estimates -> bad query plan

Thanks Michael. I'll give some join alternatives a shot first... but, that's cool. What about OFFSET 0 makes this approach work? I'm thinking the OFFSET 0 create an optimization barrier that prevents the planner from collapsing that sub-query into the top query, and enforces ordering in the quer

Re: Complex filters -> Bad row estimates -> bad query plan

If those conditions that are throwing off the stats are expected to be minimally impactful/filtering few rows, then you can use the one tried-and-true optimizer hint (aside from materialized CTEs, stylized indexes, etc) --- OFFSET 0 at the end of a sub-query. SELECT * FROM ( [your existing query w

Complex filters -> Bad row estimates -> bad query plan

qscan on Table2. The reality is that many thousands of records match all the conditions; a Merge Anti Join or Hash Anti Join would be a better query plan. I've tested the query planner with just the simpler conditions, and it makes pretty reasonable estimates about the row count (+/- 10%).

Re: Query plan: SELECT vs INSERT from same select

> On 23 Jul 2019, at 22:29, Alexander Voytsekhovskyy > wrote: > > I have quite complicated query: > > SELECT axis_x1, axis_y1, SUM(delivery_price) as v_1 FROM ( > SELECT to_char(delivery_data.delivery_date, '-MM') as axis_x1, > clients.id_client as axis_y1, delivery_data.amount * produc

Re: Query plan: SELECT vs INSERT from same select

[ please keep the list cc'd ] Alexander Voytsekhovskyy writes: > Sorry again > here is both links: > https://explain.depesz.com/s/AEWj > https://explain.depesz.com/s/CHwF Don't think I believe that those are the same query --- there's a CTE in the second one that doesn't appear in the first, and

Re: Query plan: SELECT vs INSERT from same select

Alexander Voytsekhovskyy writes: > You can see explain analyze verbose here: > https://explain.depesz.com/s/AEWj > The problem is, when i wrap it to > A) > INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1) > SELECT SAME QUERY > OR even > B) > WITH rows AS ( > ... SAME SELECT QUERY ..

Re: Query plan: SELECT vs INSERT from same select

gt; SELECT * FROM rows > > The query time dramatically drops to 500+ seconds. > > You can see explain analyze verbose here > https://explain.depesz.com/s/AEWj > > As you can see, 100% of time goes to same SELECT query, there is no issues > with INSERT-part > > I hav

Query plan: SELECT vs INSERT from same select

g all time. So my question is, why wrapping SELECT query with INSERT FROM SELECT dramatically change query plan and make it 500x slower?

Postgres using inefficient query plan when using OR filter, uses correct indices when using IN clause

Is there a way I can force the index to be used even when using OR's? Query with Disjunction: SELECT field1, field2,..., fieldN > FROM table1 WHERE > field9='val1' OR field9='val2') OR field9='val3') OR field9='val4') > AND (field6='val

Re: Odd Row Estimates in Query Plan (rows=75)

Don Seiler wrote: > We have a report query that has gone from maybe a few seconds to run to a few > minutes to run since mid-July. > Looking at the output of EXPLAIN ANALYZE, the row count estimates are way > off, even though this table was > just analyzed a day or so ago. What's more bizarre to

Re: Odd Row Estimates in Query Plan (rows=75)

On Wed, Aug 15, 2018 at 3:31 PM, Adrian Klaver wrote: > > lts.date_added > '2017-07-14 11:13:05' > > and > > lts.date_gifted >= '2017-08-13 11:13:05' > ? > > In other words one '>' and the other '>=' ? > The date_added filters were added just to use that index and with a broad r

Re: Odd Row Estimates in Query Plan (rows=75)

On 08/15/2018 01:03 PM, Don Seiler wrote: Here's the query, obfuscated manually by me: SELECT         'Foo' as system_function, stores.name as store,         lt.owner,         lt.minute_of_day,         lt.records         FROM         foo.stores         LEFT OUTER JOIN

Re: Odd Row Estimates in Query Plan (rows=75)

Here's the query, obfuscated manually by me: SELECT 'Foo' as system_function, stores.name as store, lt.owner, lt.minute_of_day, lt.records FROM foo.stores LEFT OUTER JOIN (SELECT lts.store_pkey,

Re: Odd Row Estimates in Query Plan (rows=75)

On 08/15/2018 12:31 PM, Don Seiler wrote: PostgreSQL 9.6.6 on CentOS. We have a report query that has gone from maybe a few seconds to run to a few minutes to run since mid-July. Looking at the output of EXPLAIN ANALYZE, the row count estimates are way off, even though this table was just ana

Odd Row Estimates in Query Plan (rows=75)

PostgreSQL 9.6.6 on CentOS. We have a report query that has gone from maybe a few seconds to run to a few minutes to run since mid-July. Looking at the output of EXPLAIN ANALYZE, the row count estimates are way off, even though this table was just analyzed a day or so ago. What's more bizarre to m

Clarification on PL/pgSQL query plan caching

We were experiencing insert slowdowns at the beginning of the day when we add new tables. As part of our data insert process, we have a read function and we decided to modify it to use EXECUTE to avoid plan caching. Our assumption was was the adding the table would invalidate the plan for the curr