Extremely slow when query uses GIST exclusion index

Hi. My databases make heavy use of timestamp ranges, and they rely on GIST exclusion constraints to ensure that the ranges are disjoint. I've noticed that queries that hit the GIST indexes are EXTREMELY slow, and the queries run much faster if I make trivial changes to avoid the GIST indexes. Here

Re: Extremely slow when query uses GIST exclusion index

Thanks for your help investigating this! Follow-up below: On Wed, Aug 29, 2018 at 7:25 AM, Andreas Kretschmer wrote: > > Okay, other solution. The problem is the nested loop, we can disable that: >> > test=*# set enable_nestloop to false; Is it OK to keep this off permanently in production? I t

Re: Batch insert heavily affecting query performance.

to determine the cause. We typically see low CPU and high IOPS just prior to our degraded performance. Our production environment runs provisioned IOPS to avoid this very issue. Regards, David  From: Jean Baro To: Jeremy Finzel Cc: Danylo Hlynskyi ; pgsql-performa...@postgresql.org Sent

Re: pgaudit and create postgis extension logs a lot inserts

ill log the CREATE EXTENSION of course, but not necessarily all > the contents of it, since that's actually defined in the extension > itself already? That's doable, but I think it could be abused if it was always on and installing extensions is generally not a daily activity. It seems in this case the best action is to disable pgaudit before installing postgis or install postgis first. Regards, -- -David da...@pgmasters.net

Re: pgaudit and create postgis extension logs a lot inserts

I supposed this changing of  audit session log parameter should be > logged to file? pgaudit is not intended to audit the superuser and only a superuser can set pgaudit.log. However, you can limit superuser access with the setuser extension: https://github.com/pgaudit/set_user Regards, -- -David da...@pgmasters.net

Slow performance after restoring a dump

nt_trans_id) Total runtime: 0.050 ms (15 rows) Regards, -- David

Re: Slow performance after restoring a dump

Hi, yes I've run "analyse" against the newly restored database. Should that be enough? On 19 March 2018 at 15:35, Tom Lane wrote: > David Osborne writes: > > The first question people will ask is did you re-ANALYZE the new > database? pg_dump doesn't tak

Re: Slow performance after restoring a dump

t_trans_pkey on account_trans a (cost=0.43..8.44 rows=1 width=8) (never executed) Index Cond: (account_trans_id = s.account_trans_id) Planning time: 0.255 ms Execution time: 0.039 ms (16 rows) On 19 March 2018 at 16:22, Tom Lane wrote: > David Osborne writes: > > H

Re: Slow planning time for custom function

xplain verbose select lower('TEST'); QUERY PLAN --- Result (cost=0.00..0.01 rows=1 width=32) Output: 'test'::text (2 rows) Would be interesting to see what changes without the IMMUTABLE flag. -- David Rowley

Re: functions: VOLATILE performs better than STABLE

on, but not planning to personally. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: help in analysis of execution plans

stimation for BRIN may realise that the bitmap heap scan is not a good option, although I'm not sure it'll be better than what the current v10 plan is using. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

nel" value matching your WHERE clause. I guess "channel" must not be the primary key to "valueseries" and that's why you use an IN(). The above query would return an error if multiple rows were returned by the subquery. David

Re: Useless memoize path generated for unique join on primary keys

ould be. Not the other way around, which is your case. create statistics extdataregular_field_index_stats (ndistinct) on field, index from extdataregular; analyze extdataregular; would likely put that right. David

Re: Useless memoize path generated for unique join on primary keys

On Wed, 4 May 2022 at 00:21, Benjamin Coutu wrote: > Thanks David, using extended statistics for both (and only for both) tables > solved this problem. Oh, whoops. I did get that backwards. The estimate used by the Memoize costing code is from the outer side of the join, which

Re: Window partial fetch optimization

sense? Or is this something PG is already doing, and I just > haven’t found the right magic words / built my index correctly to unlock it? > (I notice that the last example is an index-only scan; would I get this > behaviour from the previous two queries if I made the index a coverin

Re: Why is there a Sort after an Index Only Scan?

plan you want if you requite the query and replace your date range with shipping_date = '2022-05-01'. Your use of WHERE TRUE indicates to me that you might be building this query in an application already, so maybe you can just tweak that application to test if the start and end dates are the same and use equality when they are. David [1] https://commitfest.postgresql.org/38/3524/

Re: Postgresql 13 partitioning advice

ould experiment with in particular ? Perhaps if you want to keep a small high-chun table in check you might want to consider if autovacuum_naptime is set low enough. You may not care if the space being consumed in the standard 1min autovacuum_naptime is small enough not to be of concern. David [1] https://www.postgresql.org/docs/release/12.0/ [2] https://www.postgresql.org/docs/13/sql-update.html

Re: Catching up with performance & PostgreSQL 15

rmine the number of times a given node will be executed before we can determine how worthwhile JITting an expression in a node will be. David > [1] > https://www.postgresql.org/message-id/B6025887-D73F-4B5B-9925-4DA4B675F7E5%40elevated-dev.com

Re: wrong rows and cost estimation when generic plan

arams values, but when generic_plan, > planner() use boundparams=NULL, it try to calculate average value based on > mcv list of the index attributes (starttime,endtime) ? IIRC, generic plan estimates become based on distinct estimations rather than histograms or MCVs. David

Re: wrong rows and cost estimation when generic plan

t'll take the non-Const path for planning generic plans. David

Re: DML sql execution time slow down PGv14 compared with PGv13

On Thu, 15 Dec 2022 at 21:12, James Pang (chaolpan) wrote: >We had some load test ( DML inserts/deletes/updates/ on tens of hash > partition tables) and found that PGV14 slow down 10-15% compared with PGV13. > Same test server, same schema tables and data. From pg_stat_statements, sql > e

Re: Postgres12 looking for possible HashAggregate issue workarounds?

are repeated by setting some negative value, as described in the documents. You'll need to analyze the table again after changing this setting. David [1] https://www.postgresql.org/docs/12/sql-altertable.html

Re: Getting an index scan to be a parallel index scan

ose to set it to "on"? David

Re: Getting an index scan to be a parallel index scan

On Thu, 2 Feb 2023 at 14:49, Thomas Munro wrote: > If I had more timerons myself, I'd like to try to make parallel > function scans, or parallel CTE scans, work... I've not really looked in detail but I thought parallel VALUES scan might be easier than those two. David

Re: Window Functions & Table Partitions

rsuing this then feel free to take the patch to the pgsql-hackers mailing list and propose it. It's unlikely I'll get time to do that for a while, but I will keep a branch locally with it to remind me in case I do at some point in the future. David [1] https://git.postgresql.org/git

Re: Window Functions & Table Partitions

On Fri, 10 Feb 2023 at 06:40, Benjamin Tingle wrote: > Thanks for the helpful response david! I'll have a shot at getting the patch > to work myself & submitting to pgsql-hackers. I took some time today for this and fixed up a few mistakes in the patch and added it to the Marc

Re: multicolumn partitioning help

ew tips about partitioning in [1] that you may wish to review. David [1] https://www.postgresql.org/docs/devel/ddl-partitioning.html

Re: multicolumn partitioning help

e > PARTITION BY RANGE (EXTRACT(YEAR FROM dob)) LIST (SUBSTRING(hash, 1, 1)); Effectively, multi-level partitioning gives you that, It's just the DDL is different from how you wrote it. David

Re: speeding up grafana sensor-data query on raspberry pi 3

lanner to flip to a Hash Aggregate which would eliminate the Sort before aggregation. You'd only need to sort 236 rows after the Hash Aggregate for the ORDER BY. Plus, what Justin said. David

Re: High QPS, random index writes and vacuum

multiple passes would be required. The chunk of memory for dead tuple storage is capped at 1GB. David

Re: Partitioning update-heavy queue with hash partitions vs partial indexes

e given Constants have been hashed, finding the partition is just a single divide operation away. David

Re: Slow query, possibly not using index

* from pg_stat_user_tables where relid = 'media.block'::regclass; David

Re: Range partitioning query performance with date_trunc (vs timescaledb)

7;, time) >= '2021-01-01' is the same as time >= '2021-01-01'. It would be possible to make PostgreSQL do that, but that's a core code change, not something that you can do from SQL. David

Re: Queries containing ORDER BY and LIMIT started to work slowly

be. Better to focus on trying to make it faster. I suggest you create the asins_statistics (asin_id) index. However, I can't say with any level of confidence that the planner would opt to use that index if it did exist. Lowering random_page_cost or increasing effective_cache_size would increase the chances of that. David

Re: Planning time is time-consuming

index or are just a duplicate of some other index. Getting rid of those 3 will save some time in create_index_paths(). David

Re: Planning time is time-consuming

ack if you find PG14 to be much faster here. You could also experiment with a set of tables which are empty. It's possible getting the relation sizes are a factor to consider here. mdnblocks() needs to do a bit more work when the relation has multiple segments. David

Re: Planning time is time-consuming

On Tue, 12 Sept 2023 at 02:27, Tom Lane wrote: > > David Rowley writes: > > I'm not sure if you're asking for help here because you need planning > > to be faster than it currently is, or if it's because you believe that > > planning should always be

Re: [EXTERNAL] Re: Performance down with JDBC 42

skewed dataset, then this might not be very good. You might find things run better if you adjust postgresql.conf and set plan_cache_mode = force_custom_plan then select pg_reload_conf(); Please also check the documentation so that you understand the full implications for that. David

Re: I don't understand that EXPLAIN PLAN timings

executed or after the top node returns NULL. If you're using psql, if you do \timing on, how long does EXPLAIN take without ANALYZE? That also goes through executor startup and shutdown. It just skips the running the executor part. David

Re: I don't understand that EXPLAIN PLAN timings

0; EXPLAIN (SUMMARY ON) ; RESET enable_hashjoin; The following will show others that you could try. select name,setting from pg_settings where name like 'enable%'; David [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e731ed12aa

Re: I don't understand that EXPLAIN PLAN timings

on the planning time as it seems unlikely that disabling an enable* GUC would result in increased planning time. However, it does not seem impossible that that *could* happen. David

Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it

quest for the explain (analyze, buffers) output with track_io_timing on will help confirm this. If it is just reading empty pages that's causing this issue then adding that missing index would improve the situation after running just plain VACUUM each time there's a bulk delete. David

Table Partitioning and Indexes Performance Questions

I was told that partitioned table indexed must always start with the partition key columns. Is this always the case or does it depend on use case? When would you want to create indexes in this way? The documentation just mentions that it is strictly unnecessary but can be helpful. My understandin

Re: Plan selection based on worst case scenario

y ANALYZE on tracktrip, or perhaps increasing the statistics targets on the columns being queried here. If there's a correlation between the "a" and "route" columns then you might want to try CREATE STATISTICS: CREATE STATISTICS ON a,route FROM tracktrip; ANALYZE tracktrip; David

Re: a lot of shared buffers hit when planning for a simple query with primary access path

haps you have lots of bloat in your system catalogue tables. That could happen if you make heavy use of temporary tables. There are many other reasons too. It's maybe worth doing some vacuum work on the catalogue tables. David

Re: a lot of shared buffers hit when planning for a simple query with primary access path

On Mon, 1 Jul 2024 at 22:20, Pavel Stehule wrote: > The planners get min/max range from indexes. So some user's indexes can be > bloated too with similar effect I considered that, but it doesn't apply to this query as there are no range quals. David

Re: Hash Right join and seq scan

ache_size to encourage the nested loop -> index scan plan. Good ranges for effective_cache_size is anywhere between 50 - 75% of your servers's RAM. However, that might not be ideal if your server is under memory pressure from other running processes. It also depends on how large shared_buffers are as a percentage of total RAM. David

Re: Hash Right join and seq scan

all 32 hash partitions since the first query estimated 8 rows > only ? > extend statistics may help estimate count(partitionkeyid) based on > other columns bind variables, but looks like that did not help table join > case. I can't quite follow this. You'll need to better explain where you're getting these numbers for me to be able to understand. David

Re: Has gen_random_uuid() gotten much slower in v17?

on it yet. On Wed, 11 Sept 2024, 10:40 Peter Eisentraut, wrote: > On 10.09.24 15:58, David Mullineux wrote: > > I'm getting a bit concerned by the slow performance of generating uidds > > on latest dev code versus older versions. Here I compare the time to > > generate

Re: "set primary keys..." is missing when using hight values for transactions / scaling factor with pgbench

7;t be used in conjunction with the options you've mentioned. pgbench will perform a vacuum before an actual test run, so perhaps that's what you're seeing. You may also have noticed it also didn't perform the create tables and data population too without -i. -- David R

Queue table that quickly grows causes query planner to choose poor plan

se where for example 0 rows are inserted in step 2 and this is expected to run very very quickly. Do I have any other options? Postgres 9.5 ATM, but an upgrade is in planning. Thanks in advance David Wheeler Software developer [cid:2C4D0888-9F8B-463F-BD54-2B60A322210C] E dwhee...@dgitsystems.com&

Re: Queue table that quickly grows causes query planner to choose poor plan

ints out I can selectively ANALYSE only when > x rows are inserted, which I think is the best way forward. David Wheeler Software developer [cid:2C4D0888-9F8B-463F-BD54-2B60A322210C] E dwhee...@dgitsystems.com<mailto:dwhee...@dgitsystems.com> D +61 3 9663 3554 W http://dgitsystems.com Le

Re: Performance difference in accessing differrent columns in a Postgres Table

tribute 200 then it must deform 1-199 first. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: To keep indexes in memory, is large enough effective_cache_size enough?

ou might get more specific recommendations if you mention how much RAM the server has and how big the data is now and will be in the future. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: To keep indexes in memory, is large enough effective_cache_size enough?

to be written out before the new buffer can be loaded in. In a worst-case scenario, a backend performing a query would have to do this. pg_stat_bgwriter is your friend. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: To keep indexes in memory, is large enough effective_cache_size enough?

the most of that effort. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: To keep indexes in memory, is large enough effective_cache_size enough?

er useful buffers to appear 0 times. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: To keep indexes in memory, is large enough effective_cache_size enough?

u get better performance from smaller shared buffers. I think the best thing you can go and do is to go and test this. Write some code that mocks up a realistic production workload and see where you get the best performance. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQ

Re: NOT IN vs. NOT EXISTS performance

/flat/CAMkU%3D1zPVbez_HWao781L8PzFk%2Bd1J8VaJuhyjUHaRifk6OcUA%40mail.gmail.com#7c6d3178c18103d8508f3ec5982b1b8e -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

On Wed, 28 Nov 2018 at 03:16, Sanyo Moura wrote: > 11.0 > Planning Time: 7.238 ms > Planning Time: 2.638 ms > > 11.5 > Planning Time: 15138.533 ms > Execution Time: 2.310 ms Does it still take that long after running ANALYZE on the partitioned table? -- David Rowley

Re: SQL Perfomance during autovacuum

n to the number of buffers read and how long they took to read. If you find that these don't explain the variation then something else is at fault, perhaps CPU contention, or perhaps swapping due to high memory usage. It also seems pretty strange that you should need to use DIST

Re: Query Performance Issue

be good to know what random_page_cost is set to, and also if effective_cache_size isn't set too high. Increasing random_page_cost would help reduce the chances of this nested loop plan, but it's a pretty global change and could also have a negative effect on other queries. -- David Row

Re: Query Performance Issue

On Sat, 29 Dec 2018 at 20:15, Justin Pryzby wrote: > On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote: > > Unfortunately, I don't think that'll help this situation. Extended > > statistics are currently only handled for base quals

Re: select query does not pick up the right index

dex, we could have rewritten the query as such automatically but it's not / we don't. I believe I've mentioned about improving this somewhere in the distant past of the -hackers mailing list, although I can't find it right now. I recall naming the idea "scalar value lookup joins", but development didn't get much beyond thinking of that name) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: select query does not pick up the right index

On Fri, 4 Jan 2019 at 01:57, Abadie Lana wrote: > 4) name is unique, constraint and index created. Right index is picked up and > query time is rather constant there 40sec. That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that? -- David Rowley

Re: select query does not pick up the right index

> From: David Rowley > Sent: 03 January 2019 14:01 > That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of > that? > > explain (analyze,buffers) select > 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.st

Re: select query does not pick up the right index

On Fri, 4 Jan 2019 at 02:20, Abadie Lana wrote: > > From: David Rowley > > Sent: 03 January 2019 14:01 > Right, so you need to check your indexes on sample_ctrl_year and > sample_buil_year. You need an index on (channel_id, smpl_time) on those. > These indexes exist alread

Re: select query does not pick up the right index

HERE c.channel_id = (select channel_id from channel where name = '...'). That's pretty different to what you have above. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Parallel stats in execution plans

Hi folks - I'm having trouble understanding what some of the stats mean in the execution plan output when parallel workers are used. I've tried to read up about it, but I haven't been able to find anything that explains what I'm seeing. Apologies in advance if there's documentation I've been t

Re: Parallel stats in execution plans

It seems like no-one has any ideas on this - does anyone know anywhere else I can try to look/ask to find out more? Is it possible that this is a bug? Thanks Dave On 16/01/2019 11:31, David Conlin wrote: Hi folks - I'm having trouble understanding what some of the stats mean i

Re: Parallel stats in execution plans

On Thu, 17 Jan 2019 at 00:31, David Conlin wrote: > How the time values combine with parallelism. For example, each execution of > the sort node takes an average of 48.5s, over three loops. This makes a total > running time of 145.5s. Even if this was perfectly distributed between t

Re: Zero throughput on a query on a very large table.

builds take? It would certainly be good to look at psql's \d tmp_outpatient_rev output to ensure that the index is not marked as INVALID. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: autovacuum big table taking hours and sometimes seconds

to-vacuum workers are busy more often than not, then they're likely running too slowly and should be set to run more quickly. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: autovacuum big table taking hours and sometimes seconds

after vacuum started. I'd recommend reading the manual or Tomas Vondra's blog about vacuum costs. It's not overly complex, once you understand what each of the vacuum settings does. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Why isn't an index scan being used?

g like 75% of the machine's memory, and/or tweak random page cost down, if it's set to the standard 4 setting. modern SSDs are pretty fast at random reads. HDDs, not so much. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

y find that all of the workers are busy most of the time. If so, that indicates that the cost limits need to be raised. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: IS NOT DISTINCT FROM statement

clause, if you know NULLs are not possible? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: IS NOT DISTINCT FROM statement

e costs would have to be off, which might cause you some pain. The transformation mentioned earlier could only work if the arguments of the IS NOT DISTINCT FROM were Vars or Consts. It couldn't work with Params since the values are unknown to the planner. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Poor man's partitioned index .... not being used?

Const. If it had been another Var then it wouldn't be safe to use. What other unsafe cases are there? Is there a way we can always identify unsafe cases during planning? ... are the sorts of questions someone implementing this would be faced with. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Poor man's partitioned index .... not being used?

n a case like this, it is best to > just go with the partitioned table anyway. It sounds like you might want something like partition-wise join that exists in PG11. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Postgresql Sort cost Poor performance?

ARPASSEDSTATION (SMTOC, SVIN, SSTATIONCD, DWORKDATE); Should help speed up the subquery and provide pre-sorted input to the outer aggregate. If you like, you could add SLINENO to the end of the index to allow an index-only scan which may result in further performance improvements. Without the index, you're forced to sort, but at least it's just one sort instead of two. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Temporarily very slow planning time after a big delete

ant it fixed, just VACUUM the table. You should likely be doing that anyway directly after your bulk delete. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Analyze results in more expensive query plan

hough, that's likely only going to make a very small difference, if any, than getting rid of the planning completely. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Temporarily very slow planning time after a big delete

olved after a vacuum. Maybe run VACUUM VERBOSE on the table and double check there's not some large amount of tuples that are "nonremovable". -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: scans on table fail to be excluded by partition bounds

4:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-02 > 05:00:00-05'::timestamp with time zone)) > > Is there some reason why the partition constraints aren't excluding any of the > index scans ? Yeah, we don't do anything to remove base quals that are redundant due to the partition constraint. There was a patch [1] to try and fix this but it's not seen any recent activity. [1] https://commitfest.postgresql.org/19/1264/ -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Planner performance in partitions

QL Team: can You do this? You'll need to either reduce the number of partitions down to something realistic or wait for 12.0. The work done to speed up the planner with partitioned tables for v12 won't be going into v11. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Get the planner used by a query?

geqo_seed did change the plan. (And you could be certain the plan did not change for some other reason like an auto-analyze). -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)

nerated by Hibernate, then that sounds like a problem with Hibernate. PostgreSQL does not currently attempt to do any rewrites which convert OR clauses to use UNION or UNION ALL. No amount of tweaking the planner settings is going to change that fact. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Change in CTE treatment in query plans?

Hi folks - Does anyone know if there's been a change in the way values for CTEs are displayed in query plans? I think that it used to be the case that, for keys that include the values of child nodes values (eg "Shared Hit Blocks", or "Actual Total Time"), CTE scans included the CTE itself,

Re: Change in CTE treatment in query plans?

you were absolutely right - it was all to do with multiple call sites - the postgres version was just a red herring. Thanks for your help & all the best, Dave On 17/10/2019 10:04, Tom Lane wrote: David Conlin writes: Does anyone know if there's been a change in the way value

Slow planning, fast execution for particular 3-table query

ction that may be holding up autovac, no sign of bloated indexes. TIA! Best regards, David Wheeler General Manager Bali Office Bali T +62 361 475 2333 M +62 819 3660 9180 E dwhee...@dgitsystems.com<mailto:dwhee...@dgitsystems.com> Jl. Pura Mertasari No. 7, Sunset Road Abian

Re: Slow planning, fast execution for particular 3-table query

I'm not sure what "unusually large" is, but they're all < 1mb which is a little larger than some of our other comparable databases (mostly <300kb) but seems reasonable to me. Regards, David On 4/11/19, 3:37 pm, "Laurenz Albe" wrote: On Mon, 2019

Re: Slow planning, fast execution for particular 3-table query

I'll try reindexing each of the tables just to make sure it's not strange index imbalance or something causing the issue. Regards, David On 4/11/19, 4:01 pm, "Tom Lane" wrote: David Wheeler writes: > We’re having trouble working out why the planning time

Re: Slow planning, fast execution for particular 3-table query

a non-prod environment so we can muck about a bit more. If we can reproduce it in a safe place, is there a tool we can use to get more info out of the query planner to find what it’s doing to take so long? Regards, David From: Pavel Stehule Date: Monday, 4 November 2019 at 4:53 pm To: Da

Re: Slow planning, fast execution for particular 3-table query

has set the target to 10k so that’s going to wildly increase planning time. Thanks for your help! And thanks to the others who chipped in along the way 😊 Regards, David

Re: Slow planning, fast execution for particular 3-table query

On Thu, 7 Nov 2019 at 11:59, Justin Pryzby wrote: > > On Mon, Nov 04, 2019 at 03:04:45AM +, David Wheeler wrote: > > Postgres version 9.5.19 > > Each of the tables has between 3-4 indexes, and all the indexes include tid > > as first parameter. > But note th

Re: Slow performance with trivial self-joins

and reproducible benchmarks should be used as evidence to support discussion. Doing worst-case and average-case benchmarks initially will save you time, as someone will almost certainly ask if you don't do it. (I've not been following the thread for the patch) -- David Rowley

Re: pg12 partitions show bad performance vs pg96

doing so little work. The most basic guidelines for table partitioning are, don't partition your tables unless it's a net win. If partitioning was always faster, we'd just have designed Postgres to implicitly partition all of your tables for you. There are some other guidelines in [1]. [1] https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES David

Re: pg12 partitions show bad performance vs pg96

On Tue, 10 Mar 2020 at 02:08, Mariel Cherkassky wrote: > PG12 - 3 PARTITIONS > > QUERY > PLAN > ---

Re: Duplicate WHERE condition changes performance and plan

ve a suitable foreign key from the schema you posted. You might want to add that to the list of reasons to upgrade. David

  1   2   3   >