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

2017-11-13 Thread Tom Lane
incorporate some perhaps-configurable amount of risk aversion in its choices. regards, tom lane PS: please do not top-post, and do not quote the entire darn thread in each message. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make ch

Re: [PERFORM] DB slowness after upgrade from Postgres 9.1 to 9.4

2017-11-12 Thread Tom Lane
g/wiki/Slow_Query_Questions regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] overestimate on empty table

2017-11-11 Thread Tom Lane
Justin Pryzby writes: > On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote: >> One idea is to say that relpages = reltuples = 0 is only the state that >> prevails for a freshly-created table, and that VACUUM or ANALYZE should >> always set relpages to at least 1 even if

Re: [PERFORM] overestimate on empty table

2017-11-10 Thread Tom Lane
;s not like that's going to be a noticeable percentage increase in the row width ... > But is there a better way (I don't consider adding a row of junk to be a > significant improvement). Not ATM. regards, tom lane -- Sent via pgsql-performance m

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

2017-11-07 Thread Tom Lane
two. If so, turning on log_lock_waits might provide some useful info. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

2017-10-29 Thread Tom Lane
me doesn't seem very bright either. Changing this in back branches might be too much of a behavioral change, but it seems like we oughta change HEAD to apply standard selectivity estimation to the HAVING clause. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?

2017-10-17 Thread Tom Lane
hit due to a bad plan. An alternative you might consider, if simplifying the input queries is useful, is to put the fixed conditions into a view and query the view instead. That way there's not an enforced evaluation order. regards, tom lane -- Sent via pgsql

Re: [PERFORM] Rowcount estimation changes based on from clause order

2017-10-12 Thread Tom Lane
. Whether that's got anything directly to do with your original problem is hard to say. Joins to subqueries, which we normally lack any stats for, tend to produce pretty bogus selectivity numbers in themselves; so the original problem might've been more of that nature.

Re: [PERFORM] Regression from 9.4-9.6

2017-10-08 Thread Tom Lane
Jim Nasby writes: > On 10/8/17 2:34 PM, Tom Lane wrote: >> Why has this indexscan's cost estimate changed so much? > Great question... the only thing that sticks out is the coalesce(). Let > me see if an analyze with a higher stats target changes anything. FWIW, > the

Re: [PERFORM] Regression from 9.4-9.6

2017-10-08 Thread Tom Lane
>> rows=508 loops=1) I think the reason it's discarding the preferable plan is that, with this huge increment in the estimated cost getting added to both alternatives, the two nestloop plans have fuzzily the same total cost, and it's picking the one you don't want on the basis o

Re: [PERFORM] select with max functions

2017-10-02 Thread Tom Lane
er_Id) ss WHERE Ma.User_Id = ss.User_Id AND Ma.Bb_Open_Date = ss.max GROUP BY Ma.User_Id HAVING COUNT(*) > 1; This is still not going to be instantaneous, but it might be better. It's possible that an index on (User_Id, Bb_Open_Date) would help, but I'm not sure.

Re: [PERFORM] Pageinspect bt_metap help

2017-09-18 Thread Tom Lane
o the right, so that the original upper-level key splits would become impossibly unbalanced. This isn't all that unusual a situation; consider timestamp keys for instance, in a table where old data gets flushed regularly. regards, tom lane -- Sent via pgsql-performa

Re: [PERFORM] max partitions behind a view?

2017-09-18 Thread Tom Lane
ut I really doubt you want the side-effects of that. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

2017-09-15 Thread Tom Lane
ut it didn't get done for v10. If we do look at that as a substitute for "make an expression index just so you get some stats", it would be good to have a way to specify that you only want the standard ANALYZE stats on that value and not the extended ones. re

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

2017-09-15 Thread Tom Lane
n. JSON columns are great for storing random unstructured data, but they are less great when you want to do relational-ish things on subfields. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] postgresql 9.6 data directory fs becomes full

2017-09-11 Thread Tom Lane
transaction. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Explain Analyze - actual time in loops

2017-09-08 Thread Tom Lane
Neto pr writes: > I need to know the height of a B-tree index (level of the leaf node > farthest from the root). pageinspect's bt_metap() will give you that --- it's the "level" field, I believe. regards, tom lane -- Sent via pgsql-perfo

Re: [PERFORM] Explain Analyze - actual time in loops

2017-09-08 Thread Tom Lane
u about that. If this is under a Gather node, I believe that the numbers include time expended in all processes. So if you had three or more workers these results would make sense. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.

Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

2017-08-25 Thread Tom Lane
s and you're using C locale on the faster machine but some non-C locale on the slower. strcoll() is pretty darn expensive compared to strcmp() :-( regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your su

Re: [PERFORM] Postgres Dump - Creating index never stops

2017-07-12 Thread Tom Lane
e is it took the system an unusually long time to notice that it needed to cancel the autovacuum to avoid a deadlock with the CREATE INDEX. Was either process consuming a noticeable amount of CPU during that interval? Do you have deadlock_timeout set higher than the default 1s?

Re: [PERFORM] slow delete due to reference

2017-06-24 Thread Tom Lane
ot > null; > (I was actually expecting that commented out index to exists, but for some > reason it didn't) It would've done the job if you'd had it, I believe. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postg

Re: [PERFORM] Efficiently merging and sorting collections of sorted rows

2017-06-23 Thread Tom Lane
to UNION in general is difficult because of the possibility of duplicates. I wouldn't recommend holding your breath waiting for the planner to do this for you. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Dataset is fetched from cache but still takes same time to fetch records as first run

2017-06-22 Thread Tom Lane
The circa-tenth-of-a-second savings on the server side is getting swamped by client-side processing. It's possible that pgAdmin4 has improved matters in this area. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To

Re: [PERFORM] substring index what is better way to query

2017-06-20 Thread Tom Lane
hat patterns you're looking for, it's possible that a trigram index (contrib/pg_trgm) would work better. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgre

Re: [PERFORM] substring index what is better way to query

2017-06-20 Thread Tom Lane
Index Cond: ((f1 >= 'dr7g'::text) AND (f1 < 'dr7h'::text)) -> Bitmap Index Scan on loc_f1_key (cost=0.00..4.22 rows=7 width=0) Index Cond: ((f1 >= 'dr7e'::text) AND (f1 < 'dr7f'::text)) (8 rows) Whether this is worth the trouble depends a lot on your data distribution, but any of them are probably better than the seqscan you're no doubt getting right now. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sudden drastic change in performance

2017-06-15 Thread Tom Lane
erating with its stupid cap on. Usually people also increase from_collapse_limit if they have to touch either, but I think for this specific query syntax only the former matters. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or

Re: [PERFORM] index of only not null, use function index?

2017-06-08 Thread Tom Lane
xing something that's unrelated to the predicate condition, but is also needed by the query you want to optimize. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

2017-06-05 Thread Tom Lane
p one eye firmly fixed on whether it slows planning down even in cases where no benefit ensues. In the meantime, I'm not sure that there are any quick-hack ways of materially improving the situation :-( regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Bulk persistence strategy

2017-05-22 Thread Tom Lane
s; so that would require inserting them manually into the DO text, with all the attendant hazards of getting-it-wrong. We've speculated before about letting DO grow some parameter handling, but it's not gotten to the top of anyone's to-do list. regards, tom lane

Re: [PERFORM] Bulk persistence strategy

2017-05-21 Thread Tom Lane
icantly more efficient than one-use functions. Even disregarding the pg_proc update traffic, plpgsql isn't going to shine in that usage because it's optimized for repeated execution of functions. regards, tom lane -- Sent via pgsql-performance mailing list (

Re: [PERFORM] Bulk persistence strategy

2017-05-21 Thread Tom Lane
avoid bad bloat in pg_proc. If you're intending that these functions be use-once, it's fairly unclear to me why you bother, as opposed to just issuing the underlying SQL statements. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@

Re: [PERFORM] Index not being used on composite type for particular query

2017-05-20 Thread Tom Lane
sions involving columns of the table. So the first clause loses because it's got variables on both sides, and the second loses because the LHS expression is not what the index is on. You could build an additional index on that expression, if this shape of query is important enough to you to

Re: [PERFORM] GIN index not used if created in the same transaction as query

2017-05-19 Thread Tom Lane
t be usable right away. Telling whether your own transaction can use it is harder from SQL level, but if you're in the same transaction that made the index then the answer is probably always "no" :-( regards, tom lane -- Sent via pgsql-performance maili

Re: [PERFORM] GIN index not used if created in the same transaction as query

2017-05-19 Thread Tom Lane
s, what you need to do is create the gin index before you start populating the table. Fortunately, that shouldn't create a really horrid performance penalty, because gin index build isn't optimized all that much anyway compared to just inserting the data serially. regards,

Re: [PERFORM] postgres_fdw and column casting shippability

2017-05-15 Thread Tom Lane
fe. Looks like a round-tuit-shortage issue rather than anything fundamental. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Speed differences between in executing the same query

2017-05-11 Thread Tom Lane
uld improve with a better estimate. Maybe you need to increase the stats target for that table ... or maybe it just hasn't been ANALYZEd lately? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to

Re: [PERFORM] Postgres 9.5 / 9.6: Restoring PG 9.4 dump is very very slow

2017-04-14 Thread Tom Lane
slowdown? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: -EXT-[PERFORM] Re: Table not using tsvector gin index and performance much worse than when it uses it.

2017-04-05 Thread Tom Lane
rrectly.) But you do have gin_pending_list_limit, so see what that does for you. Note you can set it either globally or per-index. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Table not using tsvector gin index and performance much worse than when it uses it.

2017-04-05 Thread Tom Lane
lly I'd try tweaking gin_pending_list_limit first, if you have a version that has that ... but YMMV. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Performance issue in PostgreSQL server...

2017-03-03 Thread Tom Lane
ely certain that that has exactly the same semantics (-ENOCAFFEINE), and it might still be none too quick. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

2017-03-01 Thread Tom Lane
r this case, but it doesn't look much like a typical use-case to me. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Correct use of cursors for very large result sets in Postgres

2017-02-21 Thread Tom Lane
t excited enough about it to do that. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Correct use of cursors for very large result sets in Postgres

2017-02-18 Thread Tom Lane
with no disk buffer, as hoped Seems odd. Is your cursor just on "SELECT * FROM table", or is there some processing in there you're not mentioning? Maybe it's a cursor WITH HOLD and you're exiting the source transaction? regards, tom lane -

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

2017-02-17 Thread Tom Lane
the whole database can be expected to stay RAM-resident at all times, it'd be a good idea to reduce random_page_cost to reflect that. The default planner cost settings are meant for data that's mostly on spinning rust. regards, tom lane -- Sent via

Re: [PERFORM] Correct use of cursors for very large result sets in Postgres

2017-02-17 Thread Tom Lane
e other hand, select * from generate_series(1,1); does dump the data into a temp file, something we ought to work on improving. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange and slow joining of nested views

2017-02-03 Thread Tom Lane
tructure corresponds to a good join order. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pgsql connection timeone

2017-01-25 Thread Tom Lane
gt; 2017-01-25 11:10:17 EET [6902-1] xxx@YYY FATAL: canceling authentication due > to timeout So ... what authentication method are you using? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] Optimization inner join

2017-01-19 Thread Tom Lane
plicit sort has to be inserted, reducing the amount of data passing through the sort would be worth doing; but in the general case that's unproven. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

2017-01-18 Thread Tom Lane
ything in plpgsql is a prepared query). It's a trick that's likely to bite you eventually though. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql

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

2017-01-18 Thread Tom Lane
xplaining when the optimizations noted in that paragraph cannot occur - > and probably examples of both as well since its not clear when it can occur. If you want an exact definition of when things will happen or not happen, start reading the source code. I'm loath to document small optimiz

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

2017-01-18 Thread Tom Lane
an uncorrelated sub-query, which gets evaluated just once per run. But the overhead associated with that mechanism is high enough that forcing it automatically for every stable function would be a loser. I'd recommend doing it only where it *really* matters. regar

Re: [PERFORM] out of range error while restore using pgdump

2017-01-16 Thread Tom Lane
maybe it's s/390?). And I can't find any documentation suggesting that glibc supports turning off gradual underflow, either. Perhaps you're using some extension that fools around with the hardware floating-point options? regards, tom lane -- Sent via pg

Re: [PERFORM] Performance issue with castings args of the function

2017-01-02 Thread Tom Lane
arison (char = char operator). No type conversion step needed, so it's faster. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] bad performance

2016-12-17 Thread Tom Lane
you don't need to use SELECT DISTINCT? The sort/unique steps needed to do DISTINCT are eating a large part of the runtime, and they also form an optimization fence IIRC. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgr

Re: [PERFORM] Querying with multicolumn index

2016-12-10 Thread Tom Lane
Tomas Vondra writes: > On 12/10/2016 12:51 AM, Tom Lane wrote: >> I tried to duplicate this behavior, without success. Are you running >> with nondefault planner parameters? > My guess is this is a case of LIMIT the matching rows are uniformly > distributed in the in

Re: [PERFORM] Querying with multicolumn index

2016-12-10 Thread Tom Lane
Eric Jiang writes: > We aren't using any special planner settings - all enable_* options are "on". No, I'm asking about the cost settings (random_page_cost etc). The cost estimates you're showing seem impossible with the default settings.

Re: [PERFORM] Querying with multicolumn index

2016-12-09 Thread Tom Lane
Eric Jiang writes: > I have a query that I *think* should use a multicolumn index, but > sometimes isn't, resulting in slow queries. I tried to duplicate this behavior, without success. Are you running with nondefault planner parameters? regards, tom lane -

Re: [PERFORM] Slow query question

2016-12-06 Thread Tom Lane
www.postgresql.org/docs/current/static/indexes.html particularly 11.3 - 11.5. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Substantial different index use between 9.5 and 9.6

2016-12-01 Thread Tom Lane
of their selectivity estimation routines. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to tune Postgres to take advantage of 256GB RAM hardware

2016-11-24 Thread Tom Lane
g as long as the group counts are similar, so maybe you could post a script that generates junk test data that causes this, rather than needing 27M rows of real data. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To

Re: [PERFORM] Performance decrease after upgrade to 9.6.1

2016-11-15 Thread Tom Lane
of this is actually on pgbench changes not the server. But in the end, what you're measuring here is mostly contention, and you'd need to alter the test parameters to make it not so. The "Good Practices" section at the bottom of the pgbench reference page has some tips about tha

Re: [PERFORM] Why is the optimiser choosing a sub-optimal plan?

2016-11-14 Thread Tom Lane
(cost=0.00..18.80 rows=880 width=64) (actual time=0.014..0.016 rows=4 > loops=1) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Inlining of functions (doing LIKE on an array)

2016-11-12 Thread Tom Lane
o get mutated into a semijoin, but in this example that couldn't happen anyway, so it's not much of an objection. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Inlining of functions (doing LIKE on an array)

2016-11-11 Thread Tom Lane
ndices to work either for > Array columns with Like. Am I wrong? Plain GIN index, probably not. A pg_trgm index could help with LIKE searches, but I don't think we have a variant of that for array columns. Have you considered renormalizing the data so that you don't have arrays

Re: [PERFORM] Query much slower after upgrade to 9.6.1

2016-11-07 Thread Tom Lane
e? There were several different changes in the planner's number-of-distinct- values estimation code in 9.6, so maybe the the cause of the difference is somewhere around there. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postg

Re: [PERFORM] Query much slower after upgrade to 9.6.1

2016-11-07 Thread Tom Lane
blem is "new server won't use hashagg", I'd wonder whether the work_mem setting is the same, or whether maybe you need to bump it up some (the planner's estimate of how big the hashtable would be might have changed a bit). regards, tom lane -- Se

Re: [PERFORM] no MCV list of tiny table with unique columns

2016-11-02 Thread Tom Lane
selectivity of the conditions on "echo_tango('seven_november'::text, four_charlie)". Reformulating that, or maybe making an index on it just so that ANALYZE will gather stats about it, could help. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] no MCV list of tiny table with unique columns

2016-11-02 Thread Tom Lane
in the table, I'm not real sure why you'd need an MCV list. Could we see the actual problem query (and the other table schemas), rather than diving into the code first? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.o

Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows

2016-10-24 Thread Tom Lane
index on it. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hibernate generated query slow compared to 'equivalent' hand written one

2016-10-14 Thread Tom Lane
a plain seqscan. That's a pretty silly plan, which in most cases you would not get if you hadn't forced it. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Hibernate generated query slow compared to 'equivalent' hand written one

2016-10-14 Thread Tom Lane
could be forgiven for wondering if these were really against the same data. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Failing Multi-Job Restores, Missing Indexes on Restore

2016-09-29 Thread Tom Lane
y overcommit - would that also explain the > index issues we were seeing before we were seeing the crashes? Unlikely. I'm guessing that there's some sort of race condition involved in parallel restore with -c, but it's not very clear what. regards,

Re: [PERFORM] Failing Multi-Job Restores, Missing Indexes on Restore

2016-09-29 Thread Tom Lane
lled This is probably the dreaded Linux OOM killer. Fix by reconfiguring your system to disallow memory overcommit, or at least make it not apply to Postgres, cf https://www.postgresql.org/docs/9.5/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT regards, tom lane --

Re: [PERFORM] Unexpected expensive index scan

2016-09-28 Thread Tom Lane
it seems to perform fine when I force it to use that index anyway", the answer may be that you need to adjust random_page_cost. The default value is OK for tables that are mostly sitting on spinning rust, but if your database is RAM-resident or SSD-resident you probably want a value closer t

Re: [PERFORM] Millions of tables

2016-09-26 Thread Tom Lane
size anytime the hash tables got too big. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] query against single partition uses index, against master table does seq scan

2016-09-22 Thread Tom Lane
yway. Maybe it did, but threw it away on some bogus cost estimate. If you could produce a self-contained test case, I'd be willing to take a look. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

2016-09-22 Thread Tom Lane
ther that would help your real application as opposed to this test case. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] query against single partition uses index, against master table does seq scan

2016-09-21 Thread Tom Lane
, everything since 9.0 seems to be willing to consider the type of plan you're expecting. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Disk filled-up issue after a lot of inserts and drop schema

2016-09-14 Thread Tom Lane
Postgres bug. Unlike the situation with data files, it's very hard to see how PG could be holding onto a reference to an unused log file. It only ever writes to one log file at a time. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performanc

Re: [PERFORM] Disk filled-up issue after a lot of inserts and drop schema

2016-09-14 Thread Tom Lane
essing you can, please use "lsof" or similar tool to see which Postgres process is holding open references to lots of no-longer-there files. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] Strange nested loop for an INSERT

2016-09-12 Thread Tom Lane
if your script doesn't want to wait around then an extra ANALYZE is the ticket. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow query with big tables

2016-08-27 Thread Tom Lane
rs in the abstract. If you want to make useful engineering tradeoffs you have to talk about specific data sets and available hardware. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: htt

Re: [PERFORM] Estimates on partial index

2016-08-18 Thread Tom Lane
able is not a good thing — IndexScan is touching 10x more > pages and in a typical situation those are cold. In that case you've got random_page_cost too far down. Values less than the default of 4 are generally only appropriate if the bulk of your database stays in RAM.

Re: [PERFORM] Planner do seq scan on empty master partitioned table

2016-08-11 Thread Tom Lane
s, that will just add cost. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] what's the slowest part in the SQL

2016-08-09 Thread Tom Lane
Suya Huang writes: > Thank you Tom very much, that’s the piece of information I miss. > So, should I expect that the nested loop join would be much faster if I cache > both tables (use pg_prewarm) into memory as it waives the disk read? pg_prewarm is not going to magically fix thing

Re: [PERFORM] what's the slowest part in the SQL

2016-08-09 Thread Tom Lane
ed more performance, look into SSDs. (If you have storage kit for which you'd expect better performance than this, you should start by explaining what it is.) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make chan

Re: [PERFORM] Very poor performance with Nested Loop Anti Join

2016-08-01 Thread Tom Lane
ces 78k rows not 1, it'd likely do something smarter at the outer antijoin. I have no idea why that estimate's so far off though. What PG version is this? Stats all up to date on these two tables? Are the rows excluded by the filter condition on "creditnote" significantly

Re: [PERFORM] Very slow query (3-4mn) on a table with 25millions rows

2016-07-25 Thread Tom Lane
_part(split_part((s.attvalue)::text, ' '::text, 1), '.'::text, 1) since it's the join of that to e.name that seems to be actually selective. (The planner doesn't appear to realize that it is, but ANALYZE'ing after creating the index should fix that.)

Re: [PERFORM] [PERFORMANCE] Performance index and table

2016-07-22 Thread Tom Lane
good reason. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Seeing execution plan of foreign key constraint check?

2016-07-21 Thread Tom Lane
Jim Nasby writes: > On 7/19/16 3:10 PM, Tom Lane wrote: >> It's not so much that people don't care, as that it's not apparent how to >> improve this without breaking desirable system properties --- in this >> case, that functions are black boxes so far as ca

Re: [PERFORM] Seeing execution plan of foreign key constraint check?

2016-07-19 Thread Tom Lane
this case, that functions are black boxes so far as callers are concerned. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] less than 2 sec for response - possible?

2016-07-01 Thread Tom Lane
age for each row retrieved says that the data you need is pretty badly scattered, so constructing an index that concentrates everything you need into one range of the index might be the ticket. Either of these additional-index ideas is going to penalize table insertions/updates, so keep an eye on that e

Re: [PERFORM] Random slow queries

2016-06-29 Thread Tom Lane
unds very much like a timeout expiring someplace, and I have no idea where. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Random slow queries

2016-06-29 Thread Tom Lane
w if it would be practical for you at all, but if you could attach to a process that's stuck like this with a debugger and get a stack trace, that would probably be very informative. https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend rega

Re: [PERFORM] can't explain commit performance win7 vs linux : 8000/s vs 419/s

2016-06-25 Thread Tom Lane
ich one is reasonable? The lower number sounds a lot more plausible for laptop-grade hardware. If you weren't using an SSD I wouldn't believe that one was doing persistent commits either. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-p

Re: [PERFORM] 9.6 query slower than 9.5.3

2016-06-17 Thread Tom Lane
st place. Thanks for taking the trouble to check this! regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] 9.6 query slower than 9.5.3

2016-06-16 Thread Tom Lane
esql.org/message-id/15245.1466031608%40sss.pgh.pa.us I wonder though whether the rewrite will fix your example. Could you either make some test data available, or try HEAD + aforesaid patch to see if it behaves sanely on your data? regards, tom lane -- Sent via pgsql-perfo

Re: [PERFORM] 9.6 query slower than 9.5.3

2016-06-16 Thread Tom Lane
Adam Brusselback writes: > Gah, hit send too soon... Hm, definitely a lot of foreign keys in there. Do the estimates get better (or at least closer to 9.5) if you do "set enable_fkey_estimates = off"? regards, tom lane -- Sent via pgsql-performance

Re: [PERFORM] 9.6 query slower than 9.5.3

2016-06-16 Thread Tom Lane
s? If it's not that, I wonder whether the misestimates are connected to the foreign-key-based estimation feature. Are there any FKs on the tables involved? May we see the table schemas? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-

Re: [PERFORM] Index not used

2016-06-16 Thread Tom Lane
. If you can't persuade the app to label the comparison value as bpchar not text, the easiest fix would be to create an additional index on "guid::text". regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

  1   2   3   4   5   6   7   8   9   10   >