Re: Query became very slow after 9.6 -> 10 upgrade

2017-11-25 Thread Tom Lane
vendor's SRPM (or equivalent concept if you're not on Red Hat) is a good skill to have. regards, tom lane

Re: Query became very slow after 9.6 -> 10 upgrade

2017-11-22 Thread Tom Lane
s behavior. In short, I propose the attached fixes. I've checked this and it seems to fix Dmitry's original problem according to the test case he sent off-list. regards, tom lane diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/anal

Re: Query became very slow after 9.6 -> 10 upgrade

2017-11-23 Thread Tom Lane
succeeded at 3393 (offset -91 lines). Hunk #5 succeeded at 3570 (offset -91 lines). regards, tom lane

Re: Bad plan chosen for union all

2017-11-28 Thread Tom Lane
iction clause on the "cim" relation, not a join clause. So it will get pushed down into the subquery without creating any join order constraints on the outer query. regards, tom lane

Re: Query became very slow after 9.6 -> 10 upgrade

2017-11-24 Thread Tom Lane
/developer/roadmap/ regards, tom lane

Re: Bitmap scan is undercosted? - boolean correlation

2017-12-03 Thread Tom Lane
his is desirable? Uh ... what do you propose doing instead? We'd have to do something with ties, and it's not so obvious this way is wrong. regards, tom lane

Re: PostgreSQL database size is not reasonable

2017-12-12 Thread Tom Lane
per-database directories. Somebody mentioned pg_largeobject upthread --- that would definitely be a candidate to be big, if you're using large objects at all. regards, tom lane

Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

2017-11-21 Thread Tom Lane
just captures short-duration snapshots, which might not be representative of a 10-hour run. XCode's Instruments feature would probably be better about giving a full picture, but it has a steep learning curve. regards, tom lane

Re: Using GROUPING SETS with more than one set disables predicate pushdown?

2017-11-21 Thread Tom Lane
all sets, and then allow the optimization for clauses that reference only such columns. Or maybe I'm misreading the comment (but then it needs clarification). regards, tom lane

Re: Bitmap scan is undercosted? - boolean correlation

2017-12-05 Thread Tom Lane
Jeff Janes <jeff.ja...@gmail.com> writes: > On Dec 3, 2017 15:31, "Tom Lane" <t...@sss.pgh.pa.us> wrote: >> Jeff Janes <jeff.ja...@gmail.com> writes: >>> But I do see that ties within the logical order of the column values are >>> broken

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Tom Lane
lete takes forever as you can see here: The usual suspect for this is not having an index on some FK referencing column, thus forcing the FK check trigger to seq-scan the entire referencing table for each referenced row that is to be deleted. regards, tom lane

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Tom Lane
is because you lack indexes on the referencing columns, so > the query that scans the table to find the referencing rows is a > seqscan. Actually though ... the weird thing about this is that I'd expect to see a separate line in the EXPLAIN output for time spent in the FK trigger. Where'd that go? regards, tom lane

Re: Bitmap scan is undercosted?

2017-12-03 Thread Tom Lane
improved some comments, got rid of a NumRelids() test that's redundant with the preceding bms_membership() test, and fixed dependencies_clauselist_selectivity so that estimatedclauses actually is a pure output argument as stated by its API contract. regards, tom lane diff --git a/src/backend/s

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

2017-12-07 Thread Tom Lane
le is a member of a whole lot of roles? regards, tom lane

Re: PostgreSQL database size is not reasonable

2017-12-12 Thread Tom Lane
gest individual files in the database directory ... regards, tom lane

Re: Bad plan for ltree predicate <@

2017-12-01 Thread Tom Lane
if that doesn't improve matters. (BTW, what tipped me off to this was that the "buffers hit" count for the seqscan node was so high, several times more than the actual size of the table. I couldn't account for that until I realized that the function itself would be adding a few buffer hit

Re: Bitmap scan is undercosted?

2017-12-02 Thread Tom Lane
es around with converting "flag = true" to just "flag" and sometimes back again. But I've not looked closer yet. regards, tom lane

Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan

2018-06-05 Thread Tom Lane
search condition, which would help even more. There's relevant advice about index design in the manual, https://www.postgresql.org/docs/current/static/indexes.html (see 11.3 and 11.5 particularly) regards, tom lane

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

2018-06-27 Thread Tom Lane
erted in step 2 and this is > expected to run very very quickly. Do I have any other options? I am not following your aversion to sticking an ANALYZE in there, either. It's not like inserting 30 million rows would be free. regards, tom lane

Re: GEQO and join_collapse_limit correlation

2018-01-05 Thread Tom Lane
llapse_limit to force the planner to follow the syntactic join order. In this way you'd get rid of most of the run-time join order search effort. Don't know how cooperative your ORM would be with such an approach though. regards, tom lane

Re: GEQO and join_collapse_limit correlation

2018-01-05 Thread Tom Lane
, regardless of geqo_threshold.) regards, tom lane

Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used

2018-02-02 Thread Tom Lane
there won't be nulls in the column, why are you insisting on specifying a nondefault value of NULLS FIRST/LAST in the query? regards, tom lane

Re: bad plan using nested loops

2018-02-01 Thread Tom Lane
10, you could very possibly fix that by installing extended statistics on that pair of columns. See https://www.postgresql.org/docs/current/static/planner-stats.html#PLANNER-STATS-EXTENDED regards, tom lane

Re: postgresql 10.1 wrong plan in when using partitions bug

2018-02-04 Thread Tom Lane
with barely 30 hours remaining before the planned release wrap, it's unlikely that anything but the most trivial fixes could get done in time. regards, tom lane

Re: postgresql 10.1 wrong plan in when using partitions bug

2018-02-04 Thread Tom Lane
me other GUC settings). So there's a limited amount that the planner can do with it. regards, tom lane

Re: dsa_allocate() faliure

2018-01-29 Thread Tom Lane
t;dsa_allocate could not find %zu free pages", npages); Now maybe that comment is being unreasonably optimistic, but it sure appears that this is supposed to be a can't-happen case, in which case you've found a bug. cc'ing the DSA authors for comment. regards, tom lane

Re: blending fast and temp space volumes

2018-02-21 Thread Tom Lane
re's no concept of different temp spaces having different performance characteristics, and anyway we don't really have enough info to make accurate predictions of temp space consumption. So it's hard to see the planner doing this for you automagically. regards, tom lane

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

2018-07-29 Thread Tom Lane
n have to be fixed-width datatypes. Otherwise, the offset to the target column is uncertain, and we have to search for it. regards, tom lane

Re: Query with "ILIKE ALL" does not use the index

2018-07-26 Thread Tom Lane
not "op ALL (array)". regards, tom lane

Re: where can I download the binaries of plpython extension

2018-07-08 Thread Tom Lane
it to run in? regards, tom lane

Re: Improving Performance of Query ~ Filter by A, Sort by B

2018-07-11 Thread Tom Lane
easing the statistics target for the parent_id column in hopes of getting better estimates for the number of matches. regards, tom lane

Re: Need help with optimising simple query

2018-07-09 Thread Tom Lane
d_id = child.id order by child.name limit 10; you're asking to sort by a column that will include null values for child.name anywhere that there's a parent row without a match for child_id. Those rows aren't even represented in the index on child.name, much less placed in the right order. regards, tom lane

Re: Too many .history file in pg_xlog takes lots of space

2018-03-14 Thread Tom Lane
start. I wonder if the OP's restart process involves calling pg_resetxlog or something like that (which would be risky as heck). regards, tom lane

Re: Table order at FROM clause affects performance?

2018-04-12 Thread Tom Lane
tps://wiki.postgresql.org/wiki/Slow_Query_Questions regards, tom lane

Re: SeqScan vs. IndexScan

2018-04-18 Thread Tom Lane
y providing better stats or reformulating the filter conditions in a way the optimizer understands better. regards, tom lane

Re: Slow performance after restoring a dump

2018-03-19 Thread Tom Lane
on people will ask is did you re-ANALYZE the new database? pg_dump doesn't take care of that for you, and auto-analyze might not think it needs to process the smaller tables. regards, tom lane

Re: Slow performance after restoring a dump

2018-03-19 Thread Tom Lane
o improve that by raising the statistics target for stock_trans.product_id. I'm not sure why you weren't getting bitten by the same issue in 9.1; but the cost estimates aren't that far apart for the two plans, so maybe you were just lucky ... regards, tom lane

Re: DB corruption

2018-03-23 Thread Tom Lane
some newer PG release; 8.4.x has been out of support for years, and there are lots of known-and-unfixed bugs in it. regards, tom lane

Re: Should from_collapse be switched off? (queries 10 times faster)

2018-03-23 Thread Tom Lane
o ask this type of question effectively. regards, tom lane

Re: why does this query not use a parallel query

2018-03-02 Thread Tom Lane
time=0.062..3716.105 > rows=4625123 loops=1) I don't think we have parallel IOS yet (I might be wrong). If so, it probably thinks this is cheaper than the best available parallel plan. > If I just get the count it will use a parallel query Likely a parallelized aggregation. regards, tom lane

Re: High CPU Usage of "SET ROLE"

2018-10-22 Thread Tom Lane
300 > tables. It seems plausible to guess that you've hit some behavior that's O(N^2) in the number of objects (for some object type or other). Perhaps "perf" or a similar tool would give some insight into where the bottleneck is. https://wiki.postgresql.org/wiki/Profiling_with_perf regards, tom lane

Re: Gained %20 performance after disabling bitmapscan

2018-10-19 Thread Tom Lane
-is --- but this is the query you're complaining about... regards, tom lane

Re: High CPU Usage of "SET ROLE"

2018-10-30 Thread Tom Lane
m. It's well known that the first query executed in a *session* takes a pretty big performance hit, because of the need to populate the backend's catalog caches. I'm not very sure however why "RESET ROLE" would result in a mass cache flush, if indeed that's what's happening. regards, tom lane

Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes

2018-09-27 Thread Tom Lane
not sure how much it'd help if the rowcount estimates aren't improved. Since you haven't told us much of anything about the actual query or the data, it's hard to offer concrete advice beyond that. regards, tom lane

Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes

2018-09-27 Thread Tom Lane
did --- see the Gather node. regards, tom lane

Re: database crash during pgbench run

2018-12-11 Thread Tom Lane
o guess, I'd wonder whether the postmaster was manually started, and if so whether it was properly dissociated from the user's terminal (with nohup or the like). If it wasn't, then a control-C typed at the terminal would SIGINT the postmaster as well as whatever it was meant to terminate. regards, tom lane

Re: database crash during pgbench run

2018-12-10 Thread Tom Lane
Mariel Cherkassky writes: > 2018-12-10 19:11:56 IST 23647 LOG: received fast shutdown request > any idea what can cause it ? Something sent SIGINT to the postmaster. regards, tom lane

Re: Why Postgres doesn't use TID scan?

2018-12-17 Thread Tom Lane
so, this comment is ancient, predating the current approach with parameterized paths --- in fact best_inner_indexscan doesn't exist as such anymore. So maybe that old judgment that it'd take a lot of added code is wrong.) regards, tom lane

Re: Why Postgres doesn't use TID scan?

2018-12-17 Thread Tom Lane
it for a long time. DELETE doesn't lock the whole table. What problem are you actually facing? regards, tom lane

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

2018-11-27 Thread Tom Lane
ange. I think you've got some external factor causing long planning times --- maybe something taking an exclusive lock on one of the tables, or on pg_statistic? regards, tom lane

Re: autovacuum run but last_autovacuum is empty

2018-11-26 Thread Tom Lane
t_all_tables doesnt show the real data ? Hm, are you sure the stats collector is working at all? Are other fields in the pg_stat data updating? (If not, you may have a problem with firewall rules blocking traffic on the stats loopback port.) regards, tom lane

Re: autovacuum run but last_autovacuum is empty

2018-11-26 Thread Tom Lane
flicts. regards, tom lane

Re: postgresql unix socket connections

2019-01-09 Thread Tom Lane
off removing any packages that provide libpq versions that don't match your server. Alternatively, you could configure the server to create socket files in both places. regards, tom lane

Re: postgresql unix socket connections

2019-01-09 Thread Tom Lane
sounds like there is, which one gets used depends on the dynamic linker's configuration -- see /etc/ld.so.conf and "man ldconfig". regards, tom lane

Re: LEFT JOIN LATERAL optimisation at plan time

2018-09-18 Thread Tom Lane
hypothetical future where we're able to flatten sub-selects even though they contain GROUP BY, it would get easier/cheaper to detect this case. But that's just pie in the sky at the moment. regards, tom lane

Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours

2018-09-13 Thread Tom Lane
_number > (4 rows) Oh, hmm ... the 100ns figure I mentioned was for a seqscan. IOS could be a lot worse for a number of reasons, foremost being that if the table isn't mostly all-visible then it'd involve a lot of random heap access. It might be interesting to try forcing a seqscan plan (see enable_indexscan). regards, tom lane

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

2019-01-24 Thread Tom Lane
em to help the sorts run faster won't end up being the better idea in the end. regards, tom lane PS: On the third hand, you mention having created new indexes on this table with apparently not a lot of pain, which is a tad surprising if you don't have the patience to wa

Re: How can sort performance be so different

2019-01-29 Thread Tom Lane
ons that there's something wrong with your VPS --- but it might account for something like 10X of it. regards, tom lane

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

2019-01-25 Thread Tom Lane
e maintenance work to keep both of these indexes, though that's not related to your immediate problem. regards, tom lane

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

2019-01-25 Thread Tom Lane
true" means "local to the current transaction", which is just the one statement if you don't have a BEGIN. regards, tom lane

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

2019-01-25 Thread Tom Lane
redundant COLLATE clauses in e.g. psql \d. regards, tom lane

Re: pgstattupple vs pg_total_relation_size

2019-01-30 Thread Tom Lane
case, the other page presumably belongs to the toast table's index, which will have a metapage even if the table is empty. regards, tom lane

Re: Bloom index cost model seems to be wrong

2019-02-12 Thread Tom Lane
m regression test script is only testing queries where it forces the choice of plan type, so it really doesn't prove anything about whether the cost estimates are sane. regards, tom lane

Re: Bloom index cost model seems to be wrong

2019-02-12 Thread Tom Lane
t is not a good idea anyway. regards, tom lane