Re: [PERFORM] CREATE STATISTICS and join selectivity

the base rel selectivities. That's all there was time for with PG10. This is highly likely to be improved sometime in the future. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mai

Re: [PERFORM] Unnecessary DISTINCT while primary key in SQL

other columns" So, if you were to write the query as: explain analyze select col, k from test_tbl group by col, k order by k limit 1000; It should run much more quickly, although still not as optimal as it could be. [1] https://www.postgresql.org/docs/9.6/static/release-9-6.html --

Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

e. Is this some modified version of PostgreSQL? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http:

Re: [PERFORM] How does max_parallel_workers_per_gather change load averages?

On 4 October 2017 at 08:48, Ben Nachtrieb wrote: > I have 2 cores and my max_parallel_workers_per_gather = 2 and > max_worker_processes = 8, but my load averages are between 8 and 5 with > scheduled at 1/189 to 5/195. Are these so high because I increased >

Re: [PERFORM] query of partitioned object doesnt use index in qa

d in both cases, then the only variable part is validation_status_code. Perhaps validation_status_code = 'P' is much more common in QA than in production. You can look at the stats as gathered by ANALYZE with: \x on select * from pg_stats where tablename = 'event__' and attname = 'valida

Re: [PERFORM] query of partitioned object doesnt use index in qa

n much more useful so that we could have seen if those are accurate or not. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to y

Re: [PERFORM] Hi

tuff like that. For now I'll just leave them as two columns. ​SELECT status, payment_status, count(*) FROM ud_document WHERE uniqueid <> '201708141701018' GROUP BY 1, 2; David J.

Re: [PERFORM] Query plan for views and WHERE clauses, Luke is not using the index

use then * there are no non-DISTINCT output columns, so we needn't check. Note that * subquery_is_pushdown_safe already reported that we can't use volatile * quals if there's DISTINCT or DISTINCT ON.) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Sup

Re: [PERFORM] Unlogged tables

o go ahead and omit truncating that particular unlogged table since the data files are known to be accurate. David J.

Re: [PERFORM] join estimate of subqueries with range conditions and constraint exclusion

, combined with the somewhat specialized nature of the problem, a response should be forthcoming even though its taking a bit longer than usual. David J.

Re: [PERFORM] Can postgresql plan a query using multiple CPU cores?

ible for evaluating all (possibly partial) plans and picking the best one - flagging those plan steps that can leverage parallelism for possible execution. David J.

Re: [PERFORM] Inefficient max query when using group by

>=to_timestamp(0) ORDER BY released desc LIMIT 1) b; or you could write a function which just runs that query. Although, with the above or the function method, if you give this enough authors, then it'll eventually become slower than the problem query. Perhaps if you know the number of au

Re: [PERFORM] Slow query with 3 table joins

ALTER COLUMN field_name SET STATISTICS ; 100 is the default, and 1 is the maximum. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)

Re: [PERFORM] Insert Concurrency

>> To understand recursion, one must first understand recursion. This makes no sense unless you also provide the base case. David -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] Insert Concurrency

s://www.postgresql.org/docs/9.6/static/pgbench.html -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscrip

Re: [PERFORM] Create a materialized view in PostgreSQL which can be access by all the user account

www.postgresql.org/docs/9.6/static/sql-grant.html David J.

Re: [PERFORM] Filter certain range of IP address.

. > > xxx 172.23.110.175 > yyy 172.23.110.178 > zzz 172.23.110.177 > aaa 172.23.110.176 > bbb 172.23.111.180 > ccc 172.23.115.26 > ​SELECT ... WHERE substring(ip_addr::text, 1, 10) = '172.23.110' David J. ​

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

On Wed, Mar 1, 2017 at 5:24 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta <s.andrea...@synedra.com >> > wrot

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

u check for "tmp_san_2.text is null"? David J.

Re: [PERFORM] Number of characters in column preventing index usage

lookup (whether on the visibility map or the heap). ​https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html​ ​David J.​

Re: [PERFORM] Number of characters in column preventing index usage

the only reason the first query cares to use the index is because it can perform an Index Only Scan and thus avoid touching the heap at all. If it cannot avoid touching the heap the planner is going to just use a sequential scan to retrieve the records directly from the heap and save the index lookup step. David J.

Re: [PERFORM] Backup taking long time !!!

m surprised you didn't consider > just having a format along the lines of: magic+offset+page, > magic+offset+page, magic+offset+page, etc... > > I'd have to defer to David on this, but I think he was considering > having some kind of a bitmap to indicate which pages changed instea

Re: [PERFORM] optimizing immutable vs. stable function calls?

On Wed, Jan 18, 2017 at 4:23 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > ​I'm feeling a bit dense here but even after having read a number of > these > > kinds of interchanges I st

Re: [PERFORM] optimizing immutable vs. stable function calls?

raph cannot occur - and probably examples of both as well since its not clear when it can occur. Some TLC to the docs here would be welcomed. David J.

Re: [PERFORM] why we do not create indexes on master

On Tue, Dec 27, 2016 at 10:38 AM, Valerii Valeev <valerii.val...@mail.ru> wrote: > Thank you David, > > I used same rationale to convince my colleague — it didn’t work :) > Sort of “pragmatic” person who does what seems working no matter what > happens tomorrow. > S

Re: [PERFORM] why we do not create indexes on master

a long time to execute when obtaining rows from the master table. If this is the case then you've gotten away from the expected usage of partitions and so need to do things that aren't in the manual to make them work. David J. David J.

Re: [PERFORM] [HACKERS] temporary table vs array performance

can be replaced temp table in function > > ​I have no difficulty using arrays in functions. As for "other methods" - you can use CTE (WITH) to create a truly local table - updating the catalogs by using a temp table is indeed quite expensive. WITH vals AS ( VALUES (1, 'lw'), (2, 'lw2

Re: [PERFORM] Performance problems with 9.2.15

On Thu, Jul 21, 2016 at 2:24 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > That cross join doesn't look right. It has no join condition. ​That is that the definition of a "CROSS JOIN"... David J.

Re: [PERFORM] Capacitors, etc., in hard drives and SSD for DBMS machines...

On 07/08/2016 07:44 AM, vincent wrote: > > > Op 7/8/2016 om 12:23 PM schreef Jean-David Beyer: >> Why all this concern about how long a disk (or SSD) drive can stay up >> after a power failure? >> >> It seems to me that anyone interested in maintaining an

[PERFORM] Capacitors, etc., in hard drives and SSD for DBMS machines...

seconds, my natural gas fueled backup generator picks up the load very quickly. Am I overlooking something? -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key:166D840A 0C610C8B Registered Machine 1935521. /( )\ Shrewsbury, New Jerseyhttp://linuxcounter.net

Re: [PERFORM] Index not used

n "guid::text". > ​Or, better, persuade the app to label the value " ​ public.push_guid ​" since that is the column's type​...a type you haven't defined for us. If you get to add explicit casts this should be easy...but I'm not familiar with the framework you are using. David J.

Re: [PERFORM] pg_restore seems very slow

need to get to a point where you are seeing feedback from the pg_restore process. Once you get it telling you what it is doing (or trying to do) then diagnosing can begin. ​David J. ​

Re: [PERFORM] Performance of LIKE/NOT LIKE when used in single query

gt; Try AND...where col like '' and col not like '' Or a CTE (with) With likeqry as ( select where like ) Select from likeqry where not like (sorry for brevity but not at a pc) David J.

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 8:42 AM, Nicolas Paris <nipari...@gmail.com> wrote: > > > 2016-06-07 14:39 GMT+02:00 David G. Johnston <david.g.johns...@gmail.com>: > >> On Tue, Jun 7, 2016 at 8:36 AM, Nicolas Paris <nipari...@gmail.com> >> wrote: >>

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 8:36 AM, Nicolas Paris <nipari...@gmail.com> wrote: > 2016-06-07 14:31 GMT+02:00 David G. Johnston <david.g.johns...@gmail.com>: > >> On Tue, Jun 7, 2016 at 7:44 AM, Nicolas Paris <nipari...@gmail.com> >> wrote: >> >>> He

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

set) I get this error: > > SQL ERROR[54000] > ERROR: array size exceeds the maximum allowed (1073741823) > > ​https://www.postgresql.org/about/​ ​Maximum Field Size: 1 GB​ ​It doesn't matter that the data never actually is placed into a physical table. David J.

Re: [PERFORM] similarity and operator '%'

even if it compiles (not tested): CREATE FUNCTION similarity_80(col, val) RETURNS boolean SET similarity_threshold = 0.80 LANGUAGE sql AS $$ ​SELECT ​col % val; $$; ​David J.​

Re: [PERFORM] Fast HashJoin only after a cluster/recreate table

al time=0.016..1625.031 > rows=7160921 loops=1) >Index Cond: (es09codemp = 1) >Heap Fetches: 51499 >Buffers: shared hit=75342 > Planning time: 50.129 ms > Execution time: 380419.435 ms > -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [PERFORM] Merge joins on index scans

handy, it would be nice if you could test the patch on the current git head to see if this fixes your problem. The findings would be quite interesting for me. Please note this patch is for test environments only at this stage, not for production use. -- David Rowley http://www.2ndQu

Re: [PERFORM] Odd behavior with indices

4091090 > 3103877 10633764 11994442 1849232 2014935 4563638 132955919 7 > > ​Ok...again its beyond my present experience ​but its what the planner thinks about the distribution, and not what actually is present, that matters. David J.

Re: [PERFORM] Odd behavior with indices

he parameters query the planner thinks it needs 1.5 million of the rows and will have to check each of them for visibility. It decided that scanning the entire table was more efficient. The LIMIT 1 in both queries should not be necessary. The planner is smart enough to stop once it finds what it is looking for. In fact the LIMIT's presence may be a contributing factor...but I cannot say for sure. A better query seems like it would be: WITH active_sites AS ( SELECT DISTINCT site_id FROM datavalues; ) SELECT * FROM sites JOIN active_sites USING (site_id); David J.

Re: [PERFORM] PostgreSQL seems to create inefficient plans in simple conditional joins

but at the same time understand the annoying poor optimisation in this area. Although please remember the patch I proposed was merely a first draft proposal. Not for production use. [1] http://www.postgresql.org/message-id/flat/cakjs1f9fk_x_5hkcpcseimy16owe3empmmgsgwlckkj_rw9...@mail.gmail.com#cakjs1

Re: [PERFORM] PostgreSQL seems to create inefficient plans in simple conditional joins

ore aiming for some agreement for generic infrastructure to do exactly as you describe. [1] https://wiki.postgresql.org/wiki/FOSDEM/PGDay_2016_Developer_Meeting -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Service

Re: [PERFORM] Query order of magnitude slower with slightly different where clause

his no longer happens: # explain select * from pg_class pc inner join pg_attribute pa on pc.oid = pa.attrelid where pc.oid in(1,2); QUERY PLAN Nested Loop (cost=8.84..54.84 rows=15 width=422) -> Bitmap Heap Scan on pg_class pc (cost=8.56..14.03 rows=2 width=223) Recheck Cond: (oid = ANY ('{1,2}'::oid[])) -> Bitmap Index Scan on pg_class_oid_index (cost=0.00..8.56 rows=2 width=0) Index Cond: (oid = ANY ('{1,2}'::oid[])) -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute pa (cost=0.28..20.33 rows=8 width=203) Index Cond: (attrelid = pc.oid) (7 rows) In your case the claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid was pushed down into the subqueries, thus giving them less work to do, and also the flexibility of using indexes on claim_id in the tables contained within the subqueries. PostgreSQL currently does not push any inequality predicates down at all. A few months ago I did a little bit of work to try and lift this restriction, although I only made it cover the >=, >, < and <= operators as a first measure. Details here: http://www.postgresql.org/message-id/flat/cakjs1f9fk_x_5hkcpcseimy16owe3empmmgsgwlckkj_rw9...@mail.gmail.com#cakjs1f9fk_x_5hkcpcseimy16owe3empmmgsgwlckkj_rw9...@mail.gmail.com If you didn't have the VIEW, you could manually push these predicates into each subquery. However this is not really possible to do with the VIEW. Perhaps something could be done with a function and using dynamic SQL to craft a query manually, or you could just get rid of the view and have the application build the query. If that's not an option then maybe you could response to the thread above to mention that you've been hit by this problem and would +1 some solution to fix it, and perhaps cross link to this thread. I did have a little bit of a hard time in convincing people that this was in fact a fairly common problem in the above thread, so it would be nice to see people who have hit this problem respond to that. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [PERFORM] Getting an optimal plan on the first execution of a pl/pgsql function

al time=0.401..0.402 rows=1 loops=1)" > "Planning time: 0.058 ms" > "Execution time: 0.423 ms" > > ​I'm doubting the query inside of the function is the problem here...it is the function usage itself. Calling a function has overhead in that the body of function needs to be processed. This only has to happen once per session. The first call of the function incurs this overhead while subsequent calls do not. Pending others correcting me...I fairly certain regarding my conclusions though somewhat inexperienced in doing this kind of diagnostics. David J.

Re: [PERFORM] No index only scan on md5 index

table? AFAIK The system is designed to return data from the heap, not an index. While it possibly can in some instances if you need to return data you should store it directly in the table. David J.

Re: [PERFORM] Why is now()::date so much faster than current_date

runtime application * of type-input conversion functions. (As of PG 7.3 * that is actually possible, but not clear that we want * to rely on it.) * * The token location is attached to the run-time * typecast, not to the Const, for the convenience of * pg_stat_statements (which doesn

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

Sorry Igor - yes wrong plan. Here's the new one ... (running a wee bit slower this morning - still 20x faster that before however) http://explain.depesz.com/s/64YM QUERY PLAN

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

Thanks very much Tom. Doesn't seem to quite do the trick. I created both those indexes (or the missing one at least) Then I ran analyse on stocksales_ib and branch_purchase_order. I checked there were stats held in pg_stats for both indexes, which there were. But the query plan still predicts 1

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

Ok - wow. Adding that index, I get the same estimate of 1 row, but a runtime of ~450ms. A 23000ms improvement. http://explain.depesz.com/s/TzF8h This is great. So as a general rule of thumb, if I see a Join Filter removing an excessive number of rows, I can check if that condition can be added

[PERFORM] Slow 3 Table Join with v bad row estimate

version --- PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit Regards, -- David

Re: [PERFORM] GroupAggregate and Integer Arrays

2015 at 17:15, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Fri, Oct 23, 2015 at 7:29 AM, David Osborne <da...@qcode.co.uk> wrote: > > >> Hi, >> >> Wondering if anyone could suggest how we could improve the performance of >> this type of query? >>

[PERFORM] GroupAggregate and Integer Arrays

version() ; version -- PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit (1 row) This is running on an AWS RDS instance. Thanks for any pointers -- David

Re: [PERFORM] Having some problems with concurrent COPY commands

e same way if you perform the COPY before adding the indexes to the table? -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services

[PERFORM] Re: [PERFORM] Query > 1000× slowdown after adding datetime comparison

I'm not sure if the planner could be smarter because you are asking a question it is not particularly suited to estimating - namely cross-table correlations. Rethinking the model is likely to give you a better outcome long-term though it does seem like there should be room for improvement within the stated query and model. As Tomas said you likely will benefit from increased working memory in order to make materializing and hashing/bitmapping favorable compared to a nested loop. David J.

[PERFORM] Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

ough, until 1,000 translations are identified. I'm not sure why the nested loop executor is not intelligent enough to do this... The important number in these plans is "loops", not "rows" David J.

[PERFORM] Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

that makes it so > slow to use? Is it because it involves the *date()* function call that it > makes it difficult for the planner to guess the data distribution in the > DOCUMENT table? > What happens if you pre-compute the date condition and hard code it? ​ ​David J. ​

Re: [PERFORM] Most efficient way of querying M 'related' tables where N out of M may contain the key

On Fri, Aug 21, 2015 at 8:07 AM, Stephane Bailliez sbaill...@gmail.com wrote: On Thu, Aug 20, 2015 at 8:19 PM, David G. Johnston david.g.johns...@gmail.com wrote: ​SELECT [...] FROM (SELECT reference_id, [...] FROM table_where_referenced_id_is_a_pk WHERE reference_id EXISTS/IN/JOIN

Re: [PERFORM] Most efficient way of querying M 'related' tables where N out of M may contain the key

proposed. David J.

Re: [PERFORM] Slow HashAggregate/cache access

, but that particular patch won't help as it only allows pushdown of predicate into subqueries with window functions where the predicate is part of all of the subquery's PARTITION BY clauses. The query in question has no window clauses, so qual pushdown is not disabled for that reason. Regards David

Re: [PERFORM] Slow HashAggregate/cache access

=21175 Just how selective is fr01codemp = '1'::smallint ? Is there an index on that column ? Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Training Services

Re: [PERFORM] hyperthreadin low performance

performance is better with 32 cores that 64 cores?. You might be interested in http://www.postgresql.org/message-id/53f4f36e.6050...@agliodbs.com Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 12:16 PM, Robert DiFalco robert.difa...@gmail.com wrote: First off I apologize if this is question has been beaten to death. I've looked around for a simple answer and could not find one. Given a database that will not have it's PKEY or indices modified, is it

Re: [PERFORM] Insert vs Update

On Wednesday, July 15, 2015, Robert DiFalco robert.difa...@gmail.com wrote: First off I apologize if this is question has been beaten to death. I've looked around for a simple answer and could not find one. Given a database that will not have it's PKEY or indices modified, is it generally

Re: [PERFORM] Insert vs Update

relation space and thus the update would likely end up writing an entirely new​ record upon each event category recording. David J.

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 1:56 PM, Robert DiFalco robert.difa...@gmail.com wrote: On Wed, Jul 15, 2015 at 10:33 AM, David G. Johnston david.g.johns...@gmail.com wrote: On Wednesday, July 15, 2015, Robert DiFalco robert.difa...@gmail.com wrote: First off I apologize if this is question has

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 4:53 PM, Michael Nolan htf...@gmail.com wrote: On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco robert.difa...@gmail.com wrote: Thanks David, my example was a big simplification, but I appreciate your guidance. The different event types have differing amounts

Re: [PERFORM] Are there tuning parameters that don't take effect immediately?

. David J.

Re: [PERFORM] How to reduce writing on disk ? (90 gb on pgsql_tmp)

You should repost this directly and not through Nabble. It has wrapped your code in raw tags which the PostgreSQL mailing list software strips. On Wednesday, June 3, 2015, ben.play benjamin.co...@playrion.com wrote: Hi all, We have a big database (more than 300 Gb) and we run a lot of

Re: [PERFORM] Postgres is using 100% CPU

rows at once, you are doing it as 273 transactions instead of one? -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key:166D840A 0C610C8B Registered Machine 1935521. /( )\ Shrewsbury, New Jerseyhttp://linuxcounter.net ^^-^^ 09:00:01 up 3 days, 9:57, 2 users, load

Re: [PERFORM] Fastest way / best practice to calculate next birthdays

On Thursday, May 21, 2015, Bosco Rama postg...@boscorama.com wrote: On 05/20/15 20:22, David G. Johnston wrote: On Monday, May 18, 2015, er.tejaspate...@gmail.com javascript:; er.tejaspate...@gmail.com javascript:; wrote: If I have to find upcoming birthdays in current week

Re: [PERFORM] Fastest way / best practice to calculate next birthdays

boundaries but do start on Monday. David J.

[PERFORM] Index Scan Backward Slow

= 'XX'::bpchar) Buffers: shared hit=765 Total runtime: 184.043 ms (13 rows) http://explain.depesz.com/s/E9VE Thanks in advance for any help. Regards, -- David Osborne Qcode Software Limited http://www.qcode.co.uk

Re: [PERFORM] Index Scan Backward Slow

: On 01 May 2015, at 13:54, David Osborne da...@qcode.co.uk wrote: Hi, We have a query which finds the latest row_id for a particular code. We've found a backwards index scan is much slower than a forward one, to the extent that disabling indexscan altogether actually improves

Re: [PERFORM] unlogged tables

whether there were changes since the last checkpoint, the system truncated the relation. What use case is there for a behavior that the last checkpoint data is left on the relation upon restarting - not knowing whether it was possible the other data could have been written subsequent? David J.

Re: [PERFORM] unlogged tables

On Mon, Apr 13, 2015 at 4:49 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Apr 13, 2015 at 1:49 PM, David G. Johnston david.g.johns...@gmail.com wrote: On Monday, April 13, 2015, Matheus de Oliveira matioli.math...@gmail.com wrote: On Mon, Apr 13, 2015 at 4:31 PM, dgabriel

Re: [PERFORM] unlogged tables

On Mon, Apr 13, 2015 at 7:45 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 4/13/15 7:32 PM, David G. Johnston wrote:​ That particular use-case would probably best be served with a separate replication channel which pushes data files from the primary to the slaves and allows for the slave

Re: [PERFORM] Weird CASE WHEN behaviour causing query to be suddenly very slow

to make more specific suggestions. David J.​

Re: [PERFORM] Query RE: Optimising UUID Lookups

it, then perform a join between the 2 tables, using IN() or EXISTS()... Is that any faster? Also how well does it perform with: set enable_bitmapscan = off; ? Regards David Rowley

Re: [PERFORM] query - laziness of lateral join with function

in improving things. Unfortunately this report is too limited to really make a dent; lacking even the name of the ORM that is being used and the entire queries that are being generated - and why. David J. -- View this message in context: http://postgresql.nabble.com/query-laziness-of-lateral-join

[PERFORM] Re: Migrating a FoxPro system and would like input on the best way to achieve optimal performance

models; everything else (e.g. partitioning) are physical implementation details. David J. -- View this message in context: http://postgresql.nabble.com/Migrating-a-FoxPro-system-and-would-like-input-on-the-best-way-to-achieve-optimal-performance-tp5837211p5837241.html Sent from the PostgreSQL

Re: [PERFORM] Postgres does not use indexes with OR-conditions

. I think the material would fit well in the tutorial section but having some kind of quick synopsis and cross reference in the performance chapter would aid someone whose looking to solve a problem and not in general education mode. David J. -- View this message in context: http://postgresql

Re: [PERFORM] Postgres does not use indexes with OR-conditions

write the query with the = operator? Regards David Rowley

Re: [PERFORM] unnecessary sort in the execution plan when doing group by

on them. Regards David Rowley

Re: [PERFORM] IS NOT NULL and LEFT JOIN

way to get PostgreSQL to run the 1st query faster? Or are you asking if both queries are equivalent? Regards David Rowley

Re: [PERFORM] IS NOT NULL and LEFT JOIN

so. But such work takes resources that would be expended for every single query while manually rewriting the sub-optimal query solves the problem once-and-for-all. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/IS-NOT-NULL-and-LEFT-JOIN-tp5823591p5823737

Re: [PERFORM] IS NOT NULL and LEFT JOIN

David G Johnston wrote Laurent Martelli wrote Le 20/10/2014 15:58, Tom Lane a écrit : Laurent Martelli lt; laurent.martelli@ gt; writes: Do we agree that both queries are identical ? No, they *aren't* identical. Go consult any SQL reference. Left join conditions don't work the way

Re: [PERFORM] IS NOT NULL and LEFT JOIN

, as it would with INNER JOINs. The planner sees that these left joins are pointless, so just removes them from the plan. Regards David Rowley

Re: [PERFORM] Slow query

should define these functions as SQL functions and see if that helps. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-tp5820086p5820096.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance

Re: [PERFORM] weird execution plan

be evaluated in the less efficient count(DISTINCT) expression - the time saved there more than offset by the fact that you are effectively passing over that subset of the data a second time. HashAggregate(1M rows) + Aggregate(200k rows) Aggregate(1M rows) David J. -- View this message

Re: [PERFORM] weird execution plan

Huang, Suya wrote Can someone figure out why the first query runs so slow comparing to the second one? They generate the same result... Try: EXPLAIN (ANALYZE, BUFFERS) I believe you are only seeing caching effects. David J. -- View this message in context: http://postgresql.1045698.n5

Re: [PERFORM] query performance with hstore vs. non-hstore

to avoid the double-call - you have to use a subquery / a CTE one to ensure that it is not collapsed (offset 0 should work too but I find the CTE one a little cleaner personally). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/query-performance-with-hstore-vs

Re: [PERFORM] autocommit (true/false) for more than 1 million records

On Wed, Aug 27, 2014 at 1:02 AM, Alex Goncharov alex.goncharov@gmail.com wrote: Thank you, Kevin -- this is helpful. Thank you David, too. But it still leaves questions for me. Still... Alex Goncharov alex.goncharov@gmail.com wrote: How do I decide, before starting a COPY

Re: [PERFORM] autocommit (true/false) for more than 1 million records

specific and thus something you have to test yourself if you are operating at the margin of your system's resources. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/autocommit-true-false-for-more-than-1-million-records-tp5815943p5816460.html Sent from

Re: [PERFORM] autocommit (true/false) for more than 1 million records

exhaust RAM you might as well commit and free up that RAM for the next batch. David J.

Re: [PERFORM] autocommit (true/false) for more than 1 million records

should also consider upgrading to a newer, supported, version of PostgreSQL. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/autocommit-true-false-for-more-than-1-million-records-tp5815943p5815946.html Sent from the PostgreSQL - performance mailing list archive

Re: [PERFORM] query on parent partition table has bad performance

that is only available in supported versions that is not an option for you. Still, it is the most likely explanation for what you are seeing. There is time involved to process the partition constraint exclusion but I'm doubting it accounts for a full 3 seconds... David J. -- View this message

Re: [PERFORM] two table join with order by on both tables attributes

- then improvements can be made. At worse you will come to understand why the planner is right and can then explore alternative models. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/two-table-join-with-order-by-on-both-tables-attributes-tp5814135p5814137.html Sent

Re: [PERFORM] Query performing very bad and sometimes good

nodes but I didn't dig that deep. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Query-performing-very-bad-and-sometimes-good-tp5813831p5813847.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance

[PERFORM] Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

level it is unable to push down LIMIT into a WHERE clause and it cannot add additional sub-queries that do not exist in the original plan - which includes adding a UNION node. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-with-indexed-ORDER

  1   2   3   4   5   6   7   8   9   10   >