Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.
Oliver Mattos <omat...@gmail.com> writes:
>> Can you be more elaborate how you'd want to go about it?
> ... If another candidate plan is now lower cost, the current plan would be
> terminated[1] by setting a flag instructing each execnode to return as
> if it had reached the end of the input, although still caching the
> node selectivity values, and the new plan started from scratch.
Quite aside from the implementation difficulties you'll have, that
approach is a show-stopper right there. You can't just restart from
scratch, because we may already have shipped rows to the client, or
for DML cases already inserted/updated/deleted rows (and even if you
could roll those back, we've possibly fired triggers with unpredictable
side effects). Queries containing volatile functions are another no-fly
zone for this approach.
I can't see any way around that without unacceptable performance costs
(i.e. buffering all the rows until we're done) or wire-protocol breakage.
I think that a more practical way to address the class of problems
you're talking about is to teach the planner to have some notion of
worst-case as well as expected-case costs, and then 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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] DB slowness after upgrade from Postgres 9.1 to 9.4
p kirti <kirti...@gmail.com> writes:
> We have recently upgraded our project with a huge DB from Postgres v9.1 to
> v9.4. The whole system performance has degraded alarmingly after the
> upgrade. Simple operations that were taking only a few seconds in Postgres
> 9.1 are now taking minutes of time.
Are you certain nothing else changed? Same hardware, same OS, same
database configuration settings?
Once you've eliminated issues like that, you'd need to drill down deeper.
There's useful advice to help crystallize the situation at
https://wiki.postgresql.org/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
Justin Pryzby <pry...@telsasoft.com> 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 the physical size is zero. >> Dunno if that would confuse people. > What about adding && rel->rd_rel->reltuples==0, and make VACUUM/ANALYZE > instead > set only reltuples=1, since that's already done at costsize.c: clamp_row_est() > and therefor no additional confusion? 1 tuple in 0 pages is a physically impossible situation, so I'm quite sure that way *would* confuse people. 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
Justin Pryzby <pry...@telsasoft.com> writes: > As the queued_alters table is typically empty (and autoanalyzed with > relpages=0), I see "why": > ./src/backend/optimizer/util/plancat.c > |if (curpages < 10 && > |rel->rd_rel->relpages == 0 && > |!rel->rd_rel->relhassubclass && > |rel->rd_rel->relkind != RELKIND_INDEX) > |curpages = 10; So I'm sure you read the comment above that, too. I'm loath to abandon the principle that the planner should not believe that tables are empty/tiny without some forcing function. There are going to be way more people screaming about the plans they get from too-small rowcount estimates than the reverse. However, maybe we could do better about detecting whether a vacuum or analyze has really happened. (Autovacuum won't normally touch a table until a fair number of rows have been put in it, so if a table is tiny but has been vacuumed, we can presume that that was a manual action.) 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 the physical size is zero. Dunno if that would confuse people. Or we could bite the bullet and add a "relanalyzed" bool flag to pg_class. It'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 mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE
=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <ulf.lohbrue...@gmail.com> writes:
> I just ran "check_postgres.pl --action=bloat" and got the following output:
> ...
> Looks fine, doesn't it?
A possible explanation is that something is taking an exclusive lock
on some system catalog and holding it for a second or 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
Benjamin Coutu <ben.co...@zeyos.com> writes: > Please consider the following three semantically equivalent, but differently > written queries: > ... > Queries A + B generate the same plan and execute as follows: > -> Finalize HashAggregate (cost=32879.78..33102.62 rows=22285 > width=12) (actual time=450.724..458.667 rows=15521 loops=1) > Group Key: b.item > Filter: (sum(b.amount) >= '1'::double precision) > Rows Removed by Filter: 48277 > Plan C though, thanks to the "offset optimization fence", executes the > following, more efficient plan: > -> Subquery Scan on c (cost=32768.35..33269.76 rows=7428 width=12) > (actual time=456.591..475.204 rows=15521 loops=1 total=475.204) > Filter: (c.stock >= '1'::double precision) > Rows Removed by Filter: 48277 > -> Finalize HashAggregate (cost=32768.35..32991.20 rows=22285 > width=12) (actual time=456.582..468.124 rows=63798 loops=1 total=468.124) > Group Key: b.item Huh. So we can see that the grouping step produces 63798 rows in reality, of which 15521 pass the >= filter condition. In Plan C, the planner estimates the total number of group rows at 22285; then, having no information about the statistics of c.stock, it uses DEFAULT_INEQ_SEL (0.333) as the filter selectivity estimate, arriving at 7428 as the estimated number of result rows for the subquery. In Plan A+B, the planner presumably estimated the number of group rows at 22285 as well, but then it comes up with 22285 as the overall result. Uh, what about the HAVING? Evidently, the difference between 7428 and 22285 estimated rows out of the subquery is enough to prompt a change in join plan for this query. Since the true number is in between, it's just luck that Plan C is faster. I don't put any great amount of stock in one join plan or the other having been chosen for this case based on those estimates. But ... what about the HAVING? I took a quick look around and couldn't find anyplace where the selectivity of an aggregate's filter condition gets accounted for, which explains this observed behavior. That seems like a big oversight :-( Now, it's true that we're basically never gonna be able to do better than default selectivity estimates for post-aggregation filter conditions. Maybe, at some point in the dim past, somebody intentionally decided that applying the standard selectivity estimation logic to HAVING clauses was a loser. But I don't see any comments to that effect, and anyway taking the selectivity as 1.0 all the time 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?
Tomas Vondra <tomas.von...@2ndquadrant.com> writes: > On 10/17/2017 10:44 PM, Joe Carlson wrote: >> What I was wondering is what is the performance differences between a >> row level security implementation: >> ... >> and an implementation where I add on the constraints as part of each >> select statement: > The main point of the RLS is enforcing an order in which the conditions > are evaluated. Yeah. Because of that, I would *not* recommend RLS if you can equally well stick the equivalent conditions into your queries. There is way too much risk of taking a serious performance 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-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Rowcount estimation changes based on from clause order
Ants Aasma <ants.aa...@eesti.ee> writes: > I stumbled upon a severe row count underestimation that confusingly > went away when two inner joins in the from clause were reordered. Hm, looks more like an overestimate in this example, but anyway ... > Does anybody have any idea what is going on here? set_joinrel_size_estimates says * Since there is more than one way to make a joinrel for more than two * base relations, the results we get here could depend on which component * rel pair is provided. In theory we should get the same answers no matter * which pair is provided; in practice, since the selectivity estimation * routines don't handle all cases equally well, we might not. But there's * not much to be done about it. In this example I think the core of the issue is actually not so much bad selectivity estimates as rowcount roundoff error. If we first consider joining "small" with "big", we get an estimate of 2000 rows (which is dead on for what would happen if we just joined those). Then we estimate the final result size as the join of that to "lookup". The selectivity number for that step is somewhat hogwash but happens to yield a result that's not awful (8 rows). In the other case we first estimate the size of the join of "small" with the "lookup" subquery, and we get a rounded-off estimate of one row, whereas without the roundoff it would have been probably about 0.01. When that's joined to "big", we are computing one row times 1 million rows times a selectivity estimate that's about right for the "small.id = big.small_id" clause; but because the roundoff already inflated the first join's size so much, you end up with an inflated final result. This suggests that there might be some value in considering the sub-relations from largest to smallest, so that roundoff error in the earlier estimates is less likely to contaminate the final answer. Not sure how expensive it would be to do that or what sort of instability it might introduce into plan choices. 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. 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] Regression from 9.4-9.6
Jim Nasby <j...@nasby.net> 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 9.6 database is copied from the 9.4 one once a week and then
> pg_upgraded. I'm pretty sure an ANALYZE is part of that process.
Hm, now that I see the SubPlan in there, I wonder whether 9.6 is
accounting more conservatively for the cost of the subplan. It
probably is assuming that the subplan gets run for each row fetched
from the index, although the loops and rows-removed counts show
that the previous filter conditions reject 99% of the fetched rows.
But that code looks the same in 9.4, so I don't understand why
the 9.4 estimate isn't equally large ...
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] Regression from 9.4-9.6
Jim Nasby <j...@nasby.net> writes: > I've got a query that's regressed from 9.4 to 9.6. I suspect it has > something to do with the work done around bad plans from single-row > estimates. Why has this indexscan's cost estimate changed so much? >> -> Index Scan using bdata_filed_departuretime on bdata_forks >> (cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777 >> rows=508 loops=1) >> -> Index Scan using bdata_filed_departuretime on bdata_forks >> (cost=0.57..14894236.06 rows=1 width=36) (actual time=892.664..3025.653 >> 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 of some secondary criterion. 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] select with max functions
Mariel Cherkassky <mariel.cherkas...@gmail.com> writes: > explain analyze SELECT Ma.User_Id, > COUNT(*) COUNT >FROM Manuim Ma >WHERE Ma.Bb_Open_Date = > (SELECT Bb_Open_Date >FROM Manuim Man >WHERE Man.User_Id = Ma.User_Id order > by bb_open_date desc limit 1 > ) >GROUP BY Ma.User_Id >HAVING COUNT(*) > 1; The core problem with this query is that the sub-select has to be done over again for each row of the outer table, since it's a correlated sub-select (ie, it refers to Ma.User_Id from the outer table). Replacing a max() call with handmade logic doesn't do anything to help that. I'd try refactoring it so that you calculate the max Bb_Open_Date just once for each user id, perhaps along the lines of SELECT Ma.User_Id, COUNT(*) COUNT FROM Manuim Ma, (SELECT User_Id, max(Bb_Open_Date) as max FROM Manuim Man GROUP BY User_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. 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] Pageinspect bt_metap help
Peter Geoghegan <p...@bowt.ie> writes: > On Mon, Sep 18, 2017 at 7:31 AM, Neto pr <neto...@gmail.com> wrote: >> In my example, the values of fast_root, fast_root are equal to root, level, >> I believe that due to the newly created index and no delete operations >> occurred in the table. > Fast root and true root will probably never be different, even when > there are many deletions, including page deletions by VACUUM. As I > understand it, the fast root thing is for a fairly rare, though still > important edge case. It's a way of working around the fact that a > B-Tree can never become shorter due to the locking protocols not > allowing it. We can instead just pretend that it's shorter, knowing > that upper levels don't contain useful information. My (vague) recollection is that it's actually useful in cases where the live key-space constantly migrates to 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-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] max partitions behind a view?
Rick Otten <rottenwindf...@gmail.com> writes:
> The challenge is that because of an exponential rate of data growth, I
> might have to significantly increase the number of partitions I'm working
> with - to several hundred at a minimum and potentially more than 1000...
> This leads me to the question how many 'union all' statements can I have in
> one view?
I don't think there's a hard limit short of INT32_MAX or so, but I'd be
worried about whether there are any O(N^2) algorithms that would start
to be noticeable at the O(1000) level.
> Should I create a hierarchy of views to gradually roll the data
> up instead of putting them all in one top-level view?
That would likely make things worse not better; the planner would flatten
them anyway and would expend extra cycles doing so. You could perhaps
stop the flattening with optimization fences (OFFSET 0) but 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
Andres Freund <and...@anarazel.de> writes:
> I'm wondering if we should extend the new CREATE STATISTICS framework to
> be able to do that without requiring an index.
I think that's already on the roadmap --- it's one of the reasons we
ended up with a SELECT-like syntax for CREATE STATISTICS. But 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.
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
Mike Broers <mbro...@gmail.com> writes: > If Im reading this correctly postgres thinks the partition will return 6.5 > million matching rows but actually comes back with 162k. Is this a case > where something is wrong with the analyze job? You've got a lot of scans there that're using conditions like > │ -> Seq Scan on event__ e_1 > (cost=0.00..2527828.05 rows=11383021 width=778) (actual > time=25522.389..747238.885 rows=42 loops=1) > │ Filter: (((body ->> 'SID'::text) IS NOT > NULL) AND (validation_status_code = 'P'::bpchar)) > │ Rows Removed by Filter: 12172186 While I'd expect the planner to be pretty solid on estimating the validation_status_code condition, it's not going to have any idea about that JSON field test. That's apparently very selective, but you're just getting a default estimate, which is not going to think that a NOT NULL test will exclude lots of rows. One thing you could consider doing about this is creating an index on (body ->> 'SID'::text), which would prompt ANALYZE to gather statistics about that expression. Even if the index weren't actually used in the plan, this might improve the estimates and the resulting planning choices enough to make it worth maintaining such an index. Or you could think about pulling that field out and storing it on its own. 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
Mariel Cherkassky <mariel.cherkas...@gmail.com> writes: > My app does the next things on 20 tables in a loop : > 1.truncate table. > 2.drop constraints on table > 3.drop indexes on table > 4.insert into local_table select * from remote_oracle_table > 4.1.Recently I'm getting an error in this part : SQLERRM = could not extend >file "base/16400/124810.23": wrote only 4096 of 8192 bytes at block >3092001 > 5.create constraints on table > 6.create indexes on table. Hm, are you committing anywhere in this loop? If not, the old data remains on disk till you do end the 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
Neto pr <neto...@gmail.com> 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-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
Neto pr <neto...@gmail.com> writes:
> After analyzing, I saw that in some places of the plan, it is being used
> Parallelism. Does this explain why the final value spent (in minutes) to go
> through the index (184 minutes) is greater than the total query time (66
> minutes)?
I was just about to ask you 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.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge
=?utf-8?Q?Felix_Geisend=C3=B6rfer?= <fe...@felixge.de> writes: > I recently came across a performance difference between two machines that > surprised me: > ... > As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine > B when looking at the "Exclusive" time with explain.depesz.com [3][4]. I.e. > Machine B is ~10x faster at sorting than Machine B (for this particular > query). I doubt this is a hardware issue, it's more likely that you're comparing apples and oranges. The first theory that springs to mind is that the sort keys are strings 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 subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Dump - Creating index never stops
Hans Braxmeier <hans.braxme...@outlook.com> writes: > After restarting postgres (even with a new cluster) and creating a new > database, postgres is hanging while extracting the dump: gunzip -c pixabay.gz > | psql pixabay > The log file shows that the autovacuum task is running (almost) endless... > 2017-07-12 18:05:52.367 CEST [19586] hans@pixabay LOG: duration: 11.609 ms > statement: CREATE INDEX photos_indexphoto_created ON photos_indexphoto USING > btree (created); > 2017-07-12 20:34:58.943 CEST [19626] ERROR: canceling autovacuum task > 2017-07-12 20:34:58.943 CEST [19626] CONTEXT: automatic analyze of table > "pixabay.public.photos_photo" > 2017-07-12 20:34:59.942 CEST [19586] hans@pixabay LOG: duration: 8947575.013 > ms statement: CREATE INDEX photos_photo_approved_by_id ON photos_photo USING > btree (approved_by_id); What that looks like 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? 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 delete due to reference
Rikard Pavelic <rik...@ngs.hr> writes:
> Is it possible to speed up deletes which have null references so they don't
> check if a reference is valid?
You're thinking about the problem backwards. Since the table is
self-referential, each row is both a PK (referenced) row and an FK
(referencing) row. In its role as an FK row, a delete requires no work,
null referencing column or otherwise --- but in its role as a PK row, a
delete does require work. The PK column here is "id" which is not null in
any row, so for every row, the FK trigger must check to see whether that
id is referenced by any FK row. With no index on the FK column (undo_id)
that requires an expensive seqscan.
There are optimizations to skip the check when deleting a null PK value,
but that case never occurs in your example.
> --create unique index ix_undo on large_table(undo_id) where undo_id is not
> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Efficiently merging and sorting collections of sorted rows
Clint Miller <clint.mill...@gmail.com> writes: > That's a good plan because it's not doing a quick sort. Instead, it's just > reading the sort order off of the index, which is exactly what I want. (I > had to disable enable_sort because I didn't have enough rows of test data > in the table to get Postgres to use the index. But if I had enough rows, > the enable_sort stuff wouldn't be necessary. My real table has lots of rows > and doesn't need enable_sort turned off to do the sort with the index.) TBH, I think this whole argument is proceeding from false premises. Using an indexscan as a substitute for an explicit sort of lots of rows isn't all that attractive, because it implies a whole lot of random access to the table (unless the table is nearly in index order, which isn't a condition you can count on without expending a lot of maintenance effort to keep it that way). seqscan-and-sort is often a superior alternative, especially if you're willing to give the sort a reasonable amount of work_mem. > What I'd really like Postgres to do is use the index to get a sorted list > of rows where s = 'a'. Then, use the index again to get a sorted list of > rows where s = 'b'. Then it seems like Postgres should be able to merge the > sorted lists into a single sorted result set in O(n) time and O(1) memory > using a single merge operation. If there's no duplicates to remove, I think this will work: explain (select * from foo a where s = 'a' order by i) union all (select * from foo b where s = 'b' order by i) order by i; Merge Append (cost=0.32..48.73 rows=12 width=36) Sort Key: a.i -> Index Only Scan using foo_idx on foo a (cost=0.15..24.26 rows=6 width=36) Index Cond: (s = 'a'::text) -> Index Only Scan using foo_idx on foo b (cost=0.15..24.26 rows=6 width=36) Index Cond: (s = 'b'::text) In this case it's pretty obvious that the two union arms can never return the same row, but optimizing OR into 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
Sumeet Shukla <sumeet.k.shu...@gmail.com> writes:
> Yes, but when I actually execute the query in pgAdmin3, it takes exactly
> the same time of 19.5 secs.
pgAdmin is well known to be horribly inefficient at displaying large
query results (and 121788 rows qualifies as "large" for this purpose,
I believe). 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 make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] substring index what is better way to query
Tieson Molly <tvm...@gmail.com> writes:
> is there a different construct than the Similar To that would work?
> I know for certain that the first few characters could be different due to
> the nature of geohashes. So I may not be able to optimize the prefix
> aspect in some cases.
Depending on what 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.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] substring index what is better way to query
Tieson Molly <tvm...@gmail.com> writes: > I have an index I created on the prefix of a column: > create index location_geo_idx ON locations( substring(geohash, 0, 5)); > I currently use a similar to query, but I wanted to know if there is a > faster way to query multiple value using this index than this? > select l.geohash from locations l where l.geohash similar to '(dr7g|dr7e)%'; Well, you've got a couple of problems there. The most basic one is that that index doesn't match that query at all. You need to arrange things so that the lefthand side of the SIMILAR TO operator is exactly the indexed value, not something that's related to it. (Yes, in principle that index could be used to answer this query, but it would require a great deal more intimate knowledge than the planner has about the semantics of both substring() and SIMILAR TO.) IOW, you need to write select l.geohash from locations l where substring(l.geohash, 0, 5) similar to '(dr7g|dr7e)%'; The other possible solution would be to just index the geohash strings verbatim; unless they are quite long, that's what I'd recommend, usually. Secondly, if you're using a non-C locale, you're likely not getting an indexscan plan anyway; check it with EXPLAIN. To get an indexed prefix search out of a pattern match, the index has to use C sorting rules, which you can force with a COLLATE or text_pattern_ops option if the database's prevailing locale isn't C. Thirdly, if you experiment with EXPLAIN a little bit, you'll soon realize that the planner is not great at extracting common prefix strings out of OR'd pattern branches: regression=# create table loc (f1 text unique); CREATE TABLE regression=# explain select * from loc where f1 similar to '(dr7g|dr7e)%'; QUERY PLAN - Bitmap Heap Scan on loc (cost=4.22..14.37 rows=1 width=32) Filter: (f1 ~ '^(?:(?:dr7g|dr7e).*)$'::text) -> Bitmap Index Scan on loc_f1_key (cost=0.00..4.22 rows=7 width=0) Index Cond: ((f1 >= 'd'::text) AND (f1 < 'e'::text)) (4 rows) The useful part of this for speed purposes is the "Index Cond", and you can see that it's only enforcing that the first character be "d". I don't remember that code very well at the moment, but I'm a bit surprised that it's even figured out that the "d" is common to both branches. You can get a lot more traction if you factor the common prefix manually: regression=# explain select * from loc where f1 similar to 'dr7(g|e)%'; QUERY PLAN - Bitmap Heap Scan on loc (cost=4.22..14.37 rows=1 width=32) Filter: (f1 ~ '^(?:dr7(?:g|e).*)$'::text) -> Bitmap Index Scan on loc_f1_key (cost=0.00..4.22 rows=7 width=0) Index Cond: ((f1 >= 'dr7'::text) AND (f1 < 'dr8'::text)) (4 rows) or maybe even regression=# explain select * from loc where f1 similar to 'dr7g%' or f1 similar to 'dr7e%'; QUERY PLAN --- Bitmap Heap Scan on loc (cost=8.45..19.04 rows=2 width=32) Recheck Cond: ((f1 ~ '^(?:dr7g.*)$'::text) OR (f1 ~ '^(?:dr7e.*)$'::text)) Filter: ((f1 ~ '^(?:dr7g.*)$'::text) OR (f1 ~ '^(?:dr7e.*)$'::text)) -> BitmapOr (cost=8.45..8.45 rows=14 width=0) -> Bitmap Index Scan on loc_f1_key (cost=0.00..4.22 rows=7 width=0) 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
"l...@laurent-hasson.com" <l...@laurent-hasson.com> writes: > I have a query with many joins, something like: > Select c1, c2, c3, sum(c5) > From V1 >Join V2 on ... >Left join V3 on ... >Left join T4 on ... >Join T5 on ... >Join T6 on ... >Left join T7 on ... >Join T8 on ... >Left join T9 on ... > Where ... > Group by c1, c2, c3 > The join clauses are fairly innocuous and work directly on foreign key > relationships, so there is no voodoo there. Same for the where clause. The > views are similar and also join 3-4 tables each. All in all, there are 3 of > all the tables involved that have millions of rows and all the other tables > have thousands of rows. In particular, T9 is totally empty. > If I remove T9 from the query, it takes 9s to run. If I keep T9, the query > takes over 30mn to run! If I switch the order of T8/T9, then the same happens > with T8. So I don't think this has to do with the tables themselves. I have > updated all the statistics and reindexed all involved tables. You need to raise join_collapse_limit to keep the planner from operating 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.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] index of only not null, use function index?
Merlin Moncure <mmonc...@gmail.com> writes: > On Mon, May 22, 2017 at 10:17 AM, Ariel <aspostgre...@dsgml.com> wrote: >> Should I do: >> >> CREATE INDEX ON table ((col IS NOT NULL)) WHERE col IS NOT NULL >> >> or: >> >> CREATE INDEX ON table (col) WHERE col IS NOT NULL >> >> I'm thinking the first index will make a smaller, simpler, index since I >> don't actually need to index the value of the column. But are there any >> drawbacks I may not be aware of? Or perhaps there are no actual benefits? > You are correct. I don't see any downside to converting to bool; this > will be more efficient especially if 'col' is large at the small cost > of some generality. Depends on the datatype really. Because of alignment considerations, the index tuples will be the same size for any column value <= 4 bytes, or <= 8 bytes on 64-bit hardware. So if this is an integer column, or even bigint on 64-bit, you won't save any space with the first index definition. If it's a text column with an average width larger than what I just mentioned, you could save some space that way. In general, indexes on expressions are a tad more expensive to maintain than indexes on plain column values. And the second index at least has the potential to be useful for other queries than the one you're thinking about. So personally I'd go with the second definition unless you can show that there's a really meaningful space savings with the first one. > Having said that, what I typically do in such > cases (this comes a lot in database driven work queues) something like > this: > CREATE INDEX ON table (OrderCol) WHERE col IS NOT NULL; Right, you can frequently get a lot of mileage out of indexing 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
Justin Pryzby <pry...@telsasoft.com> writes: > I dug into this some more; I can mitigate the issue with this change: > diff --git a/src/backend/utils/adt/selfuncs.c > b/src/backend/utils/adt/selfuncs.c > index 6a4f7b1..962a5b4 100644 > --- a/src/backend/utils/adt/selfuncs.c > +++ b/src/backend/utils/adt/selfuncs.c > @@ -2279,6 +2279,22 @@ eqjoinsel_inner(Oid operator, > nd1 = get_variable_numdistinct(vardata1, ); > nd2 = get_variable_numdistinct(vardata2, ); > + elog(DEBUG4, "nd %lf %lf", nd1 ,nd2); > + if (nd1>vardata1->rel->rows) nd1=vardata1->rel->rows; > + if (nd2>vardata1->rel->rows) nd2=vardata2->rel->rows; > + > + elog(DEBUG4, "nd %lf %lf", nd1 ,nd2); > + elog(DEBUG4, "rows %lf %lf", vardata1->rel->rows > ,vardata2->rel->rows); > + elog(DEBUG4, "tuples %lf %lf", vardata1->rel->tuples > ,vardata2->rel->tuples); I don't like this change too much. I agree that intuitively you would not expect the number of distinct values to exceed the possibly-restricted number of rows from the input relation, but I think this falls foul of the problem mentioned in eqjoinsel_semi's comments, namely that it's effectively double-counting the restriction selectivity. It happens to improve matters in the test case you show, but it's not exactly producing a good estimate even so; and the fact that the change is in the right direction seems like mostly an artifact of particular ndistinct and rowcount values. I note for instance that this patch would do nothing at all for the toy example you posted upthread, because nd1/nd2 are already equal to the rows estimates in that case. The core reason why you get good results for select * from a join b using (x) where x = constant is that there's a great deal of intelligence in the planner about transitive equality deductions and what to do with partially-redundant equality clauses. The reason you don't get similarly good results for select * from a join b using (x) where x < constant is that there is no comparable machinery for inequalities. Maybe there should be, but it'd be a fair bit of work to create, and we'd have to keep 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
Simon Riggs <si...@2ndquadrant.com> writes:
> It sounds like you don't know about anonymous code blocks with DO
> https://www.postgresql.org/docs/devel/static/sql-do.html
No, the problem was that there are also some parameters to be passed
in from the application, and DO doesn't take any parameters; 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
--
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
Riaan Stander <rstan...@exa.co.za> writes:
> The intended use is use-once. The reason is that the statements might
> differ per call, especially when we start doing updates. The ideal would
> be to just issue the sql statements, but I was trying to cut down on
> network calls. To batch them together and get output from one query as
> input for the others (declare variables), I have to wrap them in a
> function in Postgres. Or am I missing something? In SQL Server TSQL I
> could declare variables in any statement as required.
Hm, well, feeding data forward to the next query without a network
round trip is a valid concern.
How stylized are these commands? Have you considered pushing the
generation logic into the function, so that you just have one (or
a few) persistent functions, and the variability slack is taken
up through EXECUTE'd strings? That'd likely be significantly
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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bulk persistence strategy
Riaan Stander <rstan...@exa.co.za> writes:
> I've come up with generating functions on the go, but I'm concerned about
> the performance impact of this. I first wanted to use an anonoumys code
> block, but then I cannot do parameter binding from npgsql.
> ...
> Is there a better way I'm missing and is "temp" function creation in
> Postgres a big performance concern, especially if a server is under load?
The function itself is only one pg_proc row, but if you're expecting
to do this thousands of times a minute you might have to adjust autovacuum
settings to 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Index not being used on composite type for particular query
Zac Goldstein <gol...@gmail.com> writes:
> This uses the index:
> ...
> But this doesn't:
> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shot
> WHERE lower(shot.matchsecond) <@ ((shot.matchsecond).match_id,
> numrange(5, 10))::matchsecond_type;
Well, yeah. After inlining the SQL functions, what you have is
> Filter: matchsecond).match_id)::integer =
> ((matchsecond).match_id)::integer) AND
> ((numrange(lower(((matchsecond).second)::numrange),
> lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@
> ('[5,10)'::numrange)::numrange))
and neither half of the AND has the form "indexed_value indexable_operator
constant", which is the basic requirement for an index condition. We're a
little bit permissive about what "constant" means, but that most certainly
doesn't extend to expressions 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 justify maintaining another index.
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] GIN index not used if created in the same transaction as query
Adam Brusselback <adambrusselb...@gmail.com> writes:
> Is there any easy way I can know if an index is usable or not? Are there
> any catalog views or anything I could check that in?
IIRC, you can look at pg_index.indcheckxmin --- if that's set, then
the index had broken HOT chains during creation and may not 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 mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] GIN index not used if created in the same transaction as query
Adam Brusselback <adambrusselb...@gmail.com> writes: > I have a function which builds two temp tables, fills each with data (in > multiple steps), creates a gin index on one of the tables, analyzes each > table, then runs a query joining the two. > My issue is, I am getting inconsistent results for if the query will use > the index or not (with the exact same data each time, and no differences in > the stats stored on the table between using the index or not). Does the "multiple steps" part involve UPDATEs on pre-existing rows? Do the updates change the column(s) used in the gin index? What this sounds like is that you're getting "broken HOT chains" in which there's not a unique indexable value among the updated versions of a given row, so there's an interval in which the new index isn't usable for queries. If that's the correct diagnosis, 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, 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] postgres_fdw and column casting shippability
Jeff Janes <jeff.ja...@gmail.com> writes:
> I've tried versions 9.6.3 and 10dev, and neither do what I expected. It
> doesn't seem to be a planning problem where it thinks the fast plan is
> slower, it just doesn't seem to consider the faster plans as being options
> at all. Is there some setting to make it realize the cast is shippable?
AFAICS, postgres_fdw doesn't have any knowledge of CoerceViaIO parse
nodes, so it's never going to consider this type of brute-force cast
as shippable. Normal casts would presumably be shippable if the
underlying function is considered safe.
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
plukovic <pe...@sobot.biz> writes:
> I have a weird case of query execution performance here.
My first thought is that you are getting a bad plan because of this
estimation error:
> -> Index Scan using uk_TBL_sk0_account_code on TBL_sk
> sk2_ (cost=0.14..12.53 rows=1 width=426) (actual time=1.036..1.084 rows=50
> loops=1)
> Index Cond: (acco_id = 1)
That rowcount estimate is off by 50X, resulting in 50X errors for the
joins above it too, and in misguided choices of nestloops when some
other join method would be better. Probably things would 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 your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres 9.5 / 9.6: Restoring PG 9.4 dump is very very slow
Hans Braxmeier <hans.braxme...@outlook.com> writes:
> Restoring these dumps in PG 9.4 takes less than 20 minutes, restoring them in
> PG 9.5/9.5 takes several hours on the same system (even if I make a PG
> 9.5/9.6 dumps and try to restore this one)!
Can you provide a test case demonstrating this sort of 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.
rverghese <ri...@hotmail.com> writes:
> Will play around with those settings as well. Maybe start with default which
> is 50 I believe.
If you're on 9.5, auto-analyze does not result in a pending list flush,
so it's irrelevant to fixing your problem. (Assuming I've identified
the problem correctly.) 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://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Table not using tsvector gin index and performance much worse than when it uses it.
rverghese <ri...@hotmail.com> writes: > I have a table with 22k rows - not large at all. I have a couple of indices > on it as well as a gin index on a tsvector column. If I reindex the table > and run a query it takes ~20ms to execute using the tsvector-gin index. By > the end of the day, the planner decides not to use the gin index and uses > the other indices on the table and the query takes ~80ms. If I reindex, the > pattern repeats-it uses the gin index for a while for superior performance > and then drops back to using the alternate ones. > The ibloat on the index shows as 0.4 and wastedibytes is 0. Less than 2K > rows have been updated of the 22K since the last reindex but the performance > has dropped since it is no longer using the gin index by mid-day. > Any thoughts on why it chooses to use alternate indices with hardly any > updates? And is there a way to force it to use the gin index without having > to reindex it twice a day. You haven't mentioned what PG version this is, nor specified how many updates is "hardly any", so you shouldn't expect any very precise answers. But I'm suspicious that the problem is bloat of the index's pending list; the planner's cost estimate is (correctly) pretty sensitive to the length of that list. If so, you need to arrange for the pending list to get flushed into the main index structure more often. Depending on your PG version, that can be done by * vacuum * auto-analyze (but I bet your version doesn't, or you would not be complaining) * gin_clean_pending_list() (but you probably ain't got that either) Or you could reduce gin_pending_list_limit to cause insert-time flushes to happen more often, or in the extremum even disable fastupdate for that index. Those options would slow down updates to make search performance more stable, so they're not panaceas. See https://www.postgresql.org/docs/current/static/gin-implementation.html#GIN-FAST-UPDATE for your version, also the "GIN Tips" on the next page. Personally 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...
Dinesh Chandra 12108 <dinesh.chan...@cyient.com> writes:
> The below is the output for psql=> EXPLAIN ANALYZE SELECT DISTINCT feature_id
> FROM evidence.point p INNER JOIN evidence.observation_evidence oe ON
> p.feature_id = oe.evd_feature_id WHERE p.domain_class_id IN (11) AND
> (p.modification_time > '2015-05-10 00:06:56.056 IST' OR oe.modification_time
> > '2015-05-10 00:06:56.056 IST') ORDER BY feature_id
I think that's a fundamentally slow query and you're not going to be able
to make it better without rethinking your requirements and/or data
representation. As written, that requires the server to form the entire
join of p to oe on feature_id, with the only filter before the join being
the evidently-none-too-selective domain_class_id condition. Only after
joining can it apply the OR condition. So this is inherently processing a
lot of rows.
If the OR arms were individually pretty selective you could rewrite this
into a UNION of two joins, a la the discussion at
https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05c-f0b4-2fdfc8873...@bluetreble.com
but given the dates involved I'm betting that won't help very much.
Or maybe you could try
select feature_id from p where domain_class_id IN (11) AND p.modification_time
> '2015-05-10 00:06:56.056 IST'
intersect
select feature_id from oe where oe.modification_time > '2015-05-10 00:06:56.056
IST'
order by feature_id
although I'm not entirely 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
Stefan Andreatta <s.andrea...@synedra.com> writes:
> The same anti-join using the text fields, however estimates just 1
> resulting row, while there are still of course 9,999 of them:
> =# explain analyze
> select tmp_san_1.id
> from tmp_san_1
> left join tmp_san_2 on tmp_san_1.text = tmp_san_2.text
> where tmp_san_2.id is null;
That is not an anti-join. To make it one, you have to constrain the RHS
join column to be IS NULL, not some random other column. Note the join
type isn't getting shown as Anti:
> Hash Left Join (cost=1.02..192.53 rows=1 width=4) (actual
> time=0.020..3.091 rows= loops=1)
As written, the query could return some rows that weren't actually
antijoin rows, ie tmp_san_1.text *did* have a match in tmp_san_2,
but that row chanced to have a null value of id.
Possibly the planner could be smarter about estimating for 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
Mike Beaton <mjsbea...@gmail.com> writes:
> New TL;DR (I'm afraid): PostgreSQL is always generating a huge buffer file
> on `FETCH ALL FROM CursorToHuge`.
I poked into this and determined that it's happening because pquery.c
executes FETCH statements the same as it does with any other
tuple-returning utility statement, ie "run it to completion and put
the results in a tuplestore, then send the tuplestore contents to the
client". I think the main reason nobody worried about that being
non-optimal was that we weren't expecting people to FETCH very large
amounts of data in one go --- if you want the whole query result at
once, why are you bothering with a cursor?
This could probably be improved, but it would (I think) require inventing
an additional PortalStrategy specifically for FETCH, and writing
associated code paths in pquery.c. Don't know when/if someone might get
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
Mike Beaton <mjsbea...@gmail.com> writes: > One outstanding question I have. Based on a lot of helpful responses given > to the SO question I can now test and see what disk buffers are generated > (by setting `log_temp_files` to `0` and then `tail -f log`), as well as how > long it takes for results to start arriving. > With a large (10,000,000 row) test table, if I do `SELECT * FROM table` on > psql it starts to return results immediately with no disk buffer. If I do > `FETCH ALL FROM cursortotable` on psql it takes about 7.5 seconds to start > returning results, and generates a 14MB buffer. If I do `SELECT * FROM > table` on a correctly coded streaming client, it also starts to return > results immediately with no disk buffer. But if I do `FETCH ALL FROM > cursortotable` from my streaming client, it takes about 1.5 seconds for > results to start coming... but again 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 -- 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] Number of characters in column preventing index usage
"David G. Johnston" <david.g.johns...@gmail.com> writes: > On Fri, Feb 17, 2017 at 3:49 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> > wrote: >> That may seem a bit strange, but I'd bet it finds the short value in some >> statistic (MCV, histogram) ans so can provide very accurate estimate. > I'm not seeing how any of the statistic columns would capture a value that > doesn't actually appear in the table...(actual ... row=0) I think it's the other way around. It found '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' in the stats, concluded (accurately) that there would be five matches, and on the strength of that decided that a seqscan over this very tiny table would be faster than an indexscan. In the other case, the short string exists neither in the table nor the stats, and the default estimate is turning out to be that there's a single match, for which it likes the indexscan solution. This is all pretty unsurprising if '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' is in the most-common-values list. Anything that's *not* in that list is going to get a smaller rowcount estimate. (I don't think that the string length, per se, has anything to do with it.) I'm not sure what performance problem the OP was looking to solve, but expecting experiments on toy-sized tables to give the same plans as you get on large tables is a standard mistake when learning to work with the PG planner. Also, if toy-sized tables are all you've got, meaning 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 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
Mike Beaton <mjsbea...@gmail.com> writes: > [ generally accurate information ] > **WARNINGS** > It would *never* make sense to do `FETCH ALL FROM cursor` for > astronomically large data, if your client side code (including your data > access layer) has any bottleneck at all at which means that all the data > from a command is fetched before any processing can be done. Many data > access layers (and especially data access wrappers) are like this. So > beware. But it is also true that not all client side code is made this way. It would probably be good to point out that most client-side libraries will do it that way, including libpq, because then they can make success or failure of the query look atomic to the application. If you use an API that lets you see rows as they come off the wire, it's up to you to recover properly from a query failure that occurs after some/many rows have already been returned. > Returning huge data using a `TABLE` or `SETOF` return type from within a > PostgeSQL function will *always* be broken (i.e. will create a huge buffer > and take a very long time to start). This will be so whether the function > is called from SQL to SQL or called over the wire. I believe this is false in general. I think it's probably true for all the standard PL languages, because they don't want to bother with suspending/resuming execution, so they make "RETURN NEXT" add the row to a tuplestore not return it immediately. But it's definitely possible to write a C function that returns a row at a time, and depending on what the calling SQL statement looks like, that could get streamed back to the client live rather than being buffered first. As a trivial example, if you do select generate_series(1,1); in psql and watch what's happening with "top", you'll see psql's memory usage going through the roof (because libpq tries to buffer the result) but the connected backend's memory usage is steady as a rock --- nor does it dump the data into a temporary file. On the 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
Titus von Boxberg <ti...@elbe-informatik.de> writes:
> I got the following problem for which I could not find a solution by
> searching the archives:
> I have Tables Ta, Tb, Tc with primary keys as bigserials.
> Ta references Tb references Tc.
> Not all but most rows in Ta reference exactly one row in Tb.
Hm, your problem query has 11 table scans (not to mention a couple of
subplans) so you're oversimplifying here. Anyway, I think that increasing
join_collapse_limit and/or from_collapse_limit to at least 11 might help.
As-is, you're more or less at the mercy of whether your textual query
structure 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
Vucomir Ianculov <vuko...@os-ux.com> writes:
> i'm seeing a lot of connection time out in postgresql log
> 2017-01-25 11:09:47 EET [6897-1] XXX@YYY FATAL: canceling authentication due
> to timeout
> 2017-01-25 11:10:15 EET [6901-1] XXX@YYY FATAL: canceling authentication due
> to timeout
> 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 to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Optimization inner join
Gustavo Rezende Montesino <gustavo.montes...@trtsp.jus.br> writes: > Being the client in question, I would like to make a little remark: What > we thought could be optimized here at first is on the row estimate of > the index scan; which could take null_frac into account. To put things > into perspective, our similar case in production has a table with 6 > million lines where only 9.5k aren´t null for the join field, an the > over-estimation is throwing away good plans (like ~150ms execution time) > in favor of pretty bad ones (~80s execution time). Please provide a concrete test case for that. AFAIK the null fraction should be accounted for in join size estimates. Here's a little test case showing that it is: regression=# create table t1 as select generate_series(1,100) as f1; SELECT 100 regression=# analyze t1; ANALYZE regression=# create table t2 as select generate_series(1,100) as f1; SELECT 100 regression=# analyze t2; ANALYZE regression=# explain select * from t1,t2 where t1.f1=t2.f1; QUERY PLAN Hash Join (cost=30832.00..70728.00 rows=100 width=8) Hash Cond: (t1.f1 = t2.f1) -> Seq Scan on t1 (cost=0.00..14425.00 rows=100 width=4) -> Hash (cost=14425.00..14425.00 rows=100 width=4) -> Seq Scan on t2 (cost=0.00..14425.00 rows=100 width=4) (5 rows) regression=# insert into t2 select null from generate_series(1,100); INSERT 0 100 regression=# analyze t2; ANALYZE regression=# explain select * from t1,t2 where t1.f1=t2.f1; QUERY PLAN Hash Join (cost=30832.00..95727.00 rows=100 width=8) Hash Cond: (t2.f1 = t1.f1) -> Seq Scan on t2 (cost=0.00..27862.00 rows=200 width=4) -> Hash (cost=14425.00..14425.00 rows=100 width=4) -> Seq Scan on t1 (cost=0.00..14425.00 rows=100 width=4) (5 rows) The join size estimate is still correct even though it knows there are many more rows in t2. As for inserting a not-null test at the scan level, I'm not exactly convinced that it's a win: regression=# \timing Timing is on. regression=# select count(*) from t1,t2 where t1.f1=t2.f1; count - 100 (1 row) Time: 562.914 ms regression=# select count(*) from t1,t2 where t1.f1=t2.f1 and t2.f1 is not null; count - 100 (1 row) Time: 564.896 ms [ ftr, these times are best-of-three-trials ] It's possible that in the case where an explicit 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?
Karl Czajkowski <kar...@isi.edu> writes:
> Is there a correctness hazard with pretending our function is
> IMMUTABLE, even though we will change the underlying config parameter
> in the same connection?
You could probably get away with that if you never ever use prepared
queries (beware that almost anything 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.org/mailpref/pgsql-performance
Re: [PERFORM] optimizing immutable vs. stable function calls?
"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 still can't get it to stick. I think part of the > problem is this sentence from the docs: > https://www.postgresql.org/docs/current/static/xfunc-volatility.html > (Stable): "This category allows the optimizer to optimize multiple calls > of the function to a single call" > I read that sentence (and the surrounding paragraph) and wonder why then > doesn't it do so in this case. It says "allows", it doesn't say "requires". The reason we have this category is that without it, it would be formally invalid to optimize an expression involving a non-immutable function into an index comparison value, because in that context the function is indeed only evaluated once (before the comparison value is fed into the index machinery). But there isn't a mechanism for that behavior outside of index scans. > If PostgreSQL cannot execute it only once at query start then all this talk > about optimization seems misleading. At worse there should be an sentence > explaining 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 optimizer details since they change all the time. 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?
Karl Czajkowski <kar...@isi.edu> writes:
> The query planner does not seem to
> recognize that it can eliminate redundant calls to a STABLE function.
No, it doesn't.
> In my case, the function call does not take any arguments and is thus
> trivially independent of row data, and appears in a WHERE clause being
> compared to constants. Why wouldn't the optimizer treat this case the
> same as IMMUTABLE?
"The same as IMMUTABLE" would be to reduce the function to a constant at
plan time, which would be the wrong thing. It would be valid to execute
it only once at query start, but there's no built-in mechanism for that.
But you could force it by putting it in a sub-SELECT, that is if you
don't like the performance of
SELECT ... slow_stable_function() ...
try this:
SELECT ... (SELECT slow_stable_function()) ...
That works because it's 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.
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] out of range error while restore using pgdump
Samir Magar <samirmag...@gmail.com> writes:
> pg_restore: [archiver (db)] COPY failed for table "tcb_test": ERROR:
> "5.40593839802118076e-315" is out of range for type double precision
That's ... weird. I don't have RHEL7 installed to test, but I don't
see any error for that value on RHEL6 or Fedora 25, which ought to
bracket that version.
I suppose your version of strtod() must be refusing to do gradual
underflow, or else you're running on hardware that doesn't do
IEEE-compliant arithmetic. But I didn't think RHEL supported any
such hardware (unless 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 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 with castings args of the function
=?UTF-8?B?0JDQvdC00YDQtdC5INCl0L7Qt9C+0LI=?= <avkho...@gmail.com> writes:
> create table t1 (id serial, str char(32));
> create function f1(line text) returns void as $$
> begin
> perform * from t1 where str = line;
> end;
> $$ language plpgsql;
This query is specifying a text comparison (text = text operator).
Since the table column isn't text, a char-to-text conversion must
happen at each line.
> create function f2(line char) returns void as $$
> begin
> perform * from t1 where str = line;
> end;
> $$ language plpgsql;
This query is specifying a char(n) comparison (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
Gabliver Faluker <gabli...@gmail.com> writes:
> It runs for ~5 seconds .
I'm a little skeptical that a 12-way join producing 340K rows
and executing in 5 seconds should be considered "bad performance".
It looks like it'd help some if you increased work_mem enough to let
both sorts happen in-memory rather than externally. But really, this
is going to take awhile no matter what. Do you really need all 340K
rows of the result? Can you improve your data representation so that
you don't need to join quite so many tables to get the answer, and
(probably even more importantly) so that 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Querying with multicolumn index
Tomas Vondra <tomas.von...@2ndquadrant.com> 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 input data. The planner likely concludes that for a
> driver with a lot of data we'll find the first row using ix_updates_time
> very quickly, and that it will be cheaper than inspecting the larger
> multi-column index. But imagine a driver with a lots of data long time
> ago. That breaks the LIMIT fairly quickly.
The fact that it's slow enough to be a problem is doubtless related to
that effect. But AFAICS, the planner should never prefer that index
for this query, because even with a uniform-density assumption, the
index that really matches the query ought to look better.
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] Querying with multicolumn index
Eric Jiang <e...@doublemap.com> 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.
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] Querying with multicolumn index
Eric Jiang <e...@doublemap.com> 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
--
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 question
Andrey Povazhnyi <w0rs...@gmail.com> writes: > We’ve got a strange planner behavior on a query to one of our bigger tables > after we upgraded to postgres 9.6.1 recently. The basic problem with this query is that there are no good alternatives. The planner believes there are about 53K rows matching the WHERE condition. (I assume this estimate is roughly in line with reality, else we have different problems to talk about.) It can either scan down the "id" index and stop when it finds the 30th row matching WHERE, or it can use the "symbol" index to read all 53K rows matching WHERE and then sort them by "id". Neither one of those is going to be speedy; but the more rows there are matching WHERE, the better the first way is going to look. If you're worried about doing this a lot, it might be worth your while to provide a 2-column index on (source, id) --- in that order --- which would allow a query plan that directly finds the required 30 rows as consecutive index entries. Possibly this could replace your index on "source" alone, depending on how much bigger the 2-col index is and how many queries have no use for the second column. See https://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
Bill Measday <b...@measday.com> writes:
> Substantial different index use between 9.5 and 9.6
Maybe you missed an ANALYZE after migrating? The plan difference
seems to be due to a vast difference in rowcount estimate for the
m_elevations condition:
> -> Bitmap Heap Scan on m_elevations e
> (cost=282802.21..37401439.43 rows=3512160 width=8)
> -> Seq Scan on m_elevations e
> (cost=100.00..13296950520.12 rows=3512159563 width=8)
If you don't know where that factor-of-1000 came from, maybe take
it up with the postgis folk. It'd mostly be coming out 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] Performance decrease after upgrade to 9.6.1
Gabriela Serventi <gabrielaserve...@hotmail.com> writes: > $ pgbench -l -c 100 -T 30 pgbench > starting vacuum...end. > transaction type: > scaling factor: 1 > query mode: simple > number of clients: 100 > number of threads: 1 > duration: 30 s > number of transactions actually processed: 27428 > latency average = 110.104 ms > tps = 908.234296 (including connections establishing) > tps = 908.278187 (excluding connections establishing) That's not a tremendously exciting benchmark case, for a number of reasons: * 100 sessions in a scale-factor-1 database are all going to be fighting over updating the single row in the pgbench_branches table. * 100 sessions driven by a single pgbench thread are probably going to be bottlenecked by that thread, not by the server. * 100 sessions on a machine with only 2 cores is going to be all about process-swap contention anyhow. My first thought about why the difference from 8.4 to 9.6 is that pgbench has grown a lot more measurement apparatus since then (for example, the transaction latency numbers, which weren't there at all in 8.4). You might try testing 9.6 server with 8.4 pgbench and vice versa to tease out how much 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 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] Why is the optimiser choosing a sub-optimal plan?
Stephen Cresswell <pgsql-performa...@stephen-cresswell.net> writes:
> I have the a table with two indexes...
(1) Tell us about the other table, mobile_summary_type.
(2) Did you transcribe the second query plan correctly? I have a hard
time believing that EXPLAIN printed two Index Cond lines for the same
indexscan.
(3) What PG version is this, exactly?
(4) Are you doing anything funny like disabling autovacuum/autoanalyze?
The rowcount estimates in the "good" plan seem rather far away from
reality, and it's not obvious why, particularly here:
> -> Seq Scan on mobile_summary_type mst
> (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)
"l...@laurent-hasson.com" <l...@laurent-hasson.com> writes:
> I wish there were a way to force inlining, or some other mechanism as the
> performance difference is large here. I'll be using the inlining approach
> when possible, but the SQL Function approach is simpler and will likely be
> more suitable for some developers.
I'm not sure that there's any fundamental reason why we don't inline SQL
functions containing sub-selects. It may just be not having wanted to put
any effort into the case way-back-when. Inlining happens too late to
allow a resulting WHERE EXISTS to 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)
"l...@laurent-hasson.com" <l...@laurent-hasson.com> writes: > I tried "exists", but won't work in the Function, i.e., > CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bigint > AS 'exists (select * from unnest($1) a where a like $2)' > LANGUAGE SQL STRICT IMMUTABLE LEAKPROOF Syntax and semantics problems. This would work: regression=# CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bool regression-# as 'select exists (select * from unnest($1) a where a like $2)' regression-# LANGUAGE SQL STRICT IMMUTABLE; CREATE FUNCTION regression=# create table tt (f1 text[]); CREATE TABLE regression=# explain select * from tt where ArrayLike(f1, 'foo'); QUERY PLAN --- Seq Scan on tt (cost=0.00..363.60 rows=453 width=32) Filter: arraylike(f1, 'foo'::text) (2 rows) But we don't inline SQL functions containing sub-selects, so you're still stuck with the rather high overhead of a SQL function. A plpgsql function might be a bit faster: CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bool as 'begin return exists (select * from unnest($1) a where a like $2); end' LANGUAGE plpgSQL STRICT IMMUTABLE; BTW, I'd be pretty suspicious of marking this function leakproof, because the underlying LIKE operator isn't leakproof according to pg_proc. > It's as expected though. As for the GIN indices, I tried and it didn't make a > difference, which I guess is expected as well because of the Like operator. I > don't expect regular indices to work on regular columns for Like operations, > especially '%xxx' ones, so I didn't expect GIN indices 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? 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 much slower after upgrade to 9.6.1
Adam Brusselback <adambrusselb...@gmail.com> writes: >> If the problem 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). > I actually was speaking with Stephen Frost in the slack channel, and tested > both of those theories. > The work_mem was the same between the two servers (12MB), but he suggested > I play around with it. I tried 4MB, 20MB, and 128MB. There was no > difference from 12MB with any of them. > I have my default_statistics_target set to 300, and ran a VACUUM ANALYZE > right after the upgrade to 9.6.1. He suggested I lower it, so I put it > back down to 100, ran a VACUUM ANALYZE, and observed no change in query. I > also tried going the other way and set it to 1000, VACUUM ANALYZE, and > again, no difference to query. Did you pay attention to the estimated number of groups (ie, estimated output rowcount for the aggregation plan node) while fooling around with the statistics? How does it compare to reality, and to 9.5's estimate? 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query much slower after upgrade to 9.6.1
Adam Brusselback <adambrusselb...@gmail.com> writes:
> As suggested in the Postgres slack channel by lukasfittl, I disabled
> hashagg on my old server, and ran the query again. That changed one piece
> to a groupagg (like was used on the new server) and the performance was
> similar to the 9.6.1 box.
If the problem 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
--
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
Justin Pryzby <pry...@telsasoft.com> writes:
>> With only two rows 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?
> Sigh, yes, but understand that it's a legacy report which happens to currently
> be near the top of my list of things to improve:
> https://explain.depesz.com/s/5rN6
Hmm, I wonder what you have join_collapse_limit and from_collapse_limit
set to. There's an awful lot of tables in that query.
Also, it seems like most of the rowcount misestimations have to do with
inheritance child tables, eg
Append (cost=0.000..50,814.990 rows=2,156 width=36) (actual
time=9.054..1,026.409 rows=429,692 loops=1)
Seq Scan on delta_mike golf_six (cost=0.000..0.000 rows=1 width=36) (actual
time=0.009..0.009 rows=0 loops=1)
Filter: ((four_charlie >= 'alpha_six'::timestamp without time zone) AND
(four_charlie <= 'four_three'::timestamp without time zone) AND
(echo_tango('seven_november'::text, four_charlie) >= 'november_golf'::double
precision) AND (echo_tango('seven_november'::text, four_charlie) <=
'papa_quebec'::double precision))
Index Scan using bravo on papa_two four_delta (cost=0.430..50,814.990
rows=2,155 width=36) (actual time=9.043..848.063 rows=429,692 loops=1)
Index Cond: ((four_charlie >= 'alpha_six'::timestamp without time zone)
AND (four_charlie <= 'four_three'::timestamp without time zone))
Filter: ((echo_tango('seven_november'::text, four_charlie) >=
'november_golf'::double precision) AND (echo_tango('seven_november'::text,
four_charlie) <= 'papa_quebec'::double precision))
There's not a lot of point in worrying about your two-row table when these
other estimates are off by multiple orders of magnitude. In this
particular case my first bet would be that the planner has no idea about
the 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
Justin Pryzby <pry...@telsasoft.com> writes:
> I believe the join is being (badly) underestimated, leading to a crappy plan
> involving multiple nested loop joins, which takes 2.5 hours instead of a
> handful of seconds; I believe that might be resolved by populating its MCV
> list..
With only two rows 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.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows
Lars Aksel Opsahl <lars.ops...@nibio.no> writes:
> In this example I have two tables one with 4 billion rows and another with
> 5 rows and then I try to do a standard simple join between this two
> tables and this takes 397391 ms. with this SQL (the query plan is added is
> further down)
This particular query would work a lot better if you had an index on
nora_bc25_observation (point_uid_ref, epoch), ie both join columns
in one index. I get the impression that that ought to be the primary
key of the table, which would be an even stronger reason to have a
unique 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
Kyle Moser <moser.k...@gmail.com> writes: > Thanks so much for the response. They are the same data, that was due to > deidentification on my part. So even though the second Hibernate query says > "index only scan" (in addition to the filter, as you said) it is > inefficient. Why does it say index only scan if it can't use the index due > to the types being numeric and the index being bigint? (I suppose my > question here is how to interpret the output properly - so I don't make > this mistake again). The key thing to notice about that is that it says "Filter" not "Index Cond". That means it's pulling data from the index but not making use of the index's search ability --- that is, it's scanning every index entry and applying the "IN" condition to the value, in much the same way as it'd do with heap entries in 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
Kyle Moser <moser.k...@gmail.com> writes:
> The depesz link for explain (analyze, buffers) is shown below for 3
> different queries. The first two queries show a log dump of the postgres
> log, showing a query that was generated by Java Hibernate. The third query
> was one I wrote and ran in pgadmin that I think is similar to what
> Hibernate is doing.
It's not all that similar: according to the EXPLAIN output, the condition
Hibernate is generating is
Filter: ((FK_USER)::numeric = ANY ('{213,382,131,...,717}'::numeric[]))
whereas your handwritten query is generating
Index Cond: (fk_user = ANY ('{70,150,1248,1269,1530,...,199954}'::bigint[]))
IOW, Hibernate is telling the server that the parameters it's supplying
are NUMERIC not INTEGER, which results in a query using numeric_eq, which
can't be indexed by a bigint index.
If you can't find a hammer big enough to persuade Hibernate that it's
dealing with integers/bigints rather than numerics, you could probably
regain most of the performance by creating an index on (FK_USER::numeric).
BTW, why is one of your EXPLAINs showing the identifiers in upper case
and the other in lower case? One 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
Cea Stapleton <c...@healthfinch.com> writes:
> Weâre using pg_restore (PostgreSQL) 9.5.4 for the restores. Weâve used
> variations on the job number:
> /usr/bin/pg_restore -j 6 -Fc -O -c -d DBNAME RESTORE_FILEâ
OK ... do you actually need the -c, and if so why?
> Weâll take a look at the memory 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, 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
Cea Stapleton <c...@healthfinch.com> writes: > We are having a baffling problem we hope you might be able to help with. We > were hoping to speed up postgres restores to our reporting server. First, we > were seeing missing indexes with pg_restore to our reporting server for one > of our databases when we did pg_restore with multiple jobs (a clean restore, > we also tried dropping the database prior to restore, just in case something > was extant and amiss). The indexes missed were not consistent, and we were > only ever seeing errors on import that indicated an index had not yet been > built. For example: > pg_restore: [archiver (db)] could not execute query: ERROR: index > "index_versions_on_item_type_and_item_id" does not exist >Command was: DROP INDEX public.index_versions_on_item_type_and_item_id; Which PG version is that; particularly, which pg_restore version? What's the exact pg_restore command you were issuing? > We decided to move back to a multi-job regular restore, and then the restores > began crashing thusly: > [2016-09-14 02:20:36 UTC]LOG: server process (PID 27624) was terminated > by signal 9: Killed 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 -- 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] Unexpected expensive index scan
[ Please don't re-quote the entire damn thread in each followup. Have
some respect for your readers' time, and assume that they have already
seen the previous traffic, or could go look it up if they haven't.
The point of quoting at all is just to quickly remind people where we
are in the discussion. ]
Jake Nielsen <jake.k.niel...@gmail.com> writes:
> So... it seems that setting the userId to one that has less rows in the
> table results in the index actually being used...
> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM SyncerEvent WHERe userId =
> '57d35db7353b0d627c0e592f' AND ID > 12468 ORDER BY ID LIMIT 4000;
It looks from the numbers floating around in this thread that the
userId used in your original query actually matches about 50% of
the table. That would make it unsurprising that the planner doesn't
want to use an index. A rule of thumb is that a seqscan is going
to be cheaper than an indexscan if your query retrieves, or even
just has to fetch, more than a few percent of the table.
Now, given the existence of an index on (userID, ID) --- in that
order --- I would expect the planner to want to use that index
for a query shaped exactly as you show above. Basically, it knows
that that just requires starting at the ('57d35db7353b0d627c0e592f',
12468) position in the index and scanning forward for 4000 index
entries; no extraneous table rows will be fetched at all. If you
increased the LIMIT enough, it'd go over to a seqscan-and-sort to
avoid doing so much random access to the table, but I'd think the
crossover point for that is well above 4000 out of 3.3M rows.
However, as soon as you add any other unindexable conditions,
the situation changes because rows that fail the additional
conditions represent useless fetches. Now, instead of fetching
4000 rows using the index, it's fetching 4000 times some multiplier.
It's hard to tell for sure given the available info, but I think
that the extra inequalities in your original query reject a pretty
sizable proportion of rows, resulting in the indexscan approach
needing to fetch a great deal more than 4000 rows, making it look
to be more expensive than a seqscan.
I'm not sure why it's preferring the pkey index to the one on
(userID, ID), but possibly that has something to do with that
index being better correlated to the physical table order, resulting
in a prediction of less random I/O when using that index.
So the bottom line is that given your data statistics, there may
well be no really good plan for your original query. It just
requires fetching a lot of rows, and indexes can't help very much.
If you say "well yeah, but 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 to 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] Millions of tables
Jeff Janes <jeff.ja...@gmail.com> writes:
> A problem is that those statistics are stored in one file (per database; it
> used to be one file per cluster). With 8 million tables, that is going to
> be a pretty big file. But the code pretty much assumes the file is going
> to be pretty small, and so it has no compunction about commanding that it
> be read and written, in its entirety, quite often.
I don't know that anyone ever believed it would be small. But at the
time the pgstats code was written, there was no good alternative to
passing the data through files. (And I'm not sure we envisioned
applications that would be demanding fresh data constantly, anyway.)
Now that the DSM stuff exists and has been more or less shaken out,
I wonder how practical it'd be to use a DSM segment to make the stats
collector's data available to backends. You'd need a workaround for
the fact that not all the DSM implementations support resize (although
given the lack of callers of dsm_resize, one could be forgiven for
wondering whether any of that code has been tested at all). But you
could imagine abandoning one DSM segment and creating a new one of
double the 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
Mike Broers <mbro...@gmail.com> writes:
> This is 9.5, sorry I didnt mention that in the initial post.
Hmm, that's odd then.
> I am guessing the issue is that the secondary non-indexed criteria is a
> search through a jsonb column?
Doubt it; it should have considered the plan you are thinking of anyway.
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 subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher
Igor Neyman <iney...@perceptron.com> writes:
> table_a is too small, just 50 records.
> Optimizer decided (correctly) that Seq Scan is cheaper than using an index.
Yeah. The given test case is quite useless for demonstrating that you
have a problem, since it's actually *faster* on 9.5 than 9.1.
What I suspect is happening is that 9.2 and up assume that an unnest()
will produce 100 rows, whereas 9.1 assumed it would produce only 1 row.
The latter happened to be more accurate for this specific case, though
in general it could result in selection of very bad plans.
If you are intending only one value be selected, don't use unnest();
you'd be better off with "(string_to_array('501001',','))[1]"
or something like that.
In the long run we should teach the planner how to produce better
estimates for unnest-on-a-constant-array, though I'm unsure whether
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
Mike Broers <mbro...@gmail.com> writes:
> Hello, I am curious about the performance of queries against a master table
> that seem to do seq scans on each child table. When the same query is
> issued at a partition directly it uses the partition index and is very
> fast.
What PG version is that? For me, 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
Pietro Pugni <pietro.pu...@gmail.com> writes:
> Iâve jsut discovered the issue.. I set "logging_collector=offâ in the
> previous email but didnât comment the other log* parameters, so Postgres
> was logging every single INSERT! This was caused the disk to fill up.
Ah.
> The strange issue is that the log file didnât exists when the disk filled
> up. I personally looked for it but it wasnât where it should have been (
> /var/log/postgesql/ ), so I canât exactly confirm that the issue was the
> log file getting bigger and bigger.
Seems like the log file must have gotten unlinked while still active,
or at least, *something* had an open reference to it. It's hard to
speculate about the cause for that without more info about how you've got
the logging set up. (Are you using the log collector? Are you rotating
logs?) But I seriously doubt it represents a 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-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
Rick Otten <rottenwindf...@gmail.com> writes:
> I don't know why something still has an open file descriptor on something
> you believe has been removed, but at least that explains why you are
> experiencing the discrepancy between "du" and the real available space on
> the disk.
Yeah, the reported behavior clearly indicates that some PG process is
holding open files that should have been dropped (and were unlinked).
That's a bug, but there's not enough info here to find and fix it.
If we're really lucky, this is the same bug that Andres found and fixed
last week:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=26ce63ce76f91eac7570fcb893321ed0233d62ff
but that guess is probably too optimistic, especially if it's a background
process (such as the checkpointer process) that is holding the open files.
If you can reproduce this, which I'm guessing 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 to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange nested loop for an INSERT
phb07 <ph...@apra.asso.fr> writes: > The performance issue, encountered in very specific situations, is the > time needed to cancel a significant number of insertions. > I have build a simple test case that reproduces the problem without the > need of the extension. It just mimics the behaviour. At least for this example, the problem is that the DELETE enormously alters the statistics for the t1_log.tuple column (going from 100% "NEW" to 50% "NEW" and 50% "OLD"), but the plan for your last command is generated with stats saying there are no "OLD" entries. So you get a plan that would be fast for small numbers of "OLD" entries, but it sucks when there are lots of them. The fix I would recommend is to do a manual "ANALYZE t1_log" after such a large data change. Auto-ANALYZE would fix it for you after a minute or so, probably, but 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
Craig James <cja...@emolecules.com> writes:
> Straight hash-table indexes (which Postgres doesn't use) have O(1) access
> time. The amount of data has no effect on the access time.
This is wishful thinking --- once you have enough data, O(1) goes out the
window. For example, a hash index is certainly not going to continue to
scale linearly once you reach its maximum possible number of buckets
(2^N for N-bit hashes, and remember you can't get very many useful hash
bits out of small objects like integers). But even before that, large
numbers of buckets put enough stress on your storage system that you will
see some not very O(1)-ish behavior, just because too little of the index
fits in whatever cache and RAM you have. Any storage hierarchy is
ultimately going to impose O(log N) access costs, that's the way they're
built.
I think it's fairly pointless to discuss such matters 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:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Estimates on partial index
Victor Yegorov <vyego...@gmail.com> writes:
> Settings:
> random_page_cost 2.5 ¤
> seq_page_cost1¤
> Question is â why IndexScan over partial index is estimated less than
> BitmapHeap + BitmapIndex scan. And how can I tell Planner, that IndexScan
> over 1/3 of table 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.
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] Planner do seq scan on empty master partitioned table
Andrey Zhidenkov <andrey.zhiden...@gmail.com> writes:
> I have a table (registry.entry) which has ~ 100 inherited tables. This
> is a master table and it's empty:
As long as it's empty, a seqscan should be essentially free. Don't
worry about it. And definitely don't create indexes, 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
Suya Huang <shu...@connexity.com> 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 things if your table is bigger
than RAM, which it apparently is.
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
Suya Huang <shu...@connexity.com> writes:
>-> Index Scan using idx_order_1_us on order o
> (cost=0.56..8.58 rows=1 width=30) (actual time=5.814..5.814 rows=0 loops=526)
4 or so ms per row fetched is well within expectation for random access to
spinning-rust media. For example, a 15K RPM drive spins at 4 ms per
revolution, so rotational delay alone would probably explain this number,
never mind needing to do any seeks. So I see nothing even slightly
unexpected here, assuming that the "order" table is large enough that none
of what you need is in RAM already. If you need 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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Very poor performance with Nested Loop Anti Join
Andreas Joseph Krogh <andr...@visena.com> writes: > This query performs terribly slow (~26 minutes, 1561346.597ms): Seems like the key misestimation is on the inner antijoin: >-> Hash Anti Join (cost=654.21..4008.72 rows=1 width=8) > (actual time=9.016..40.672 rows=76174 loops=1) > Hash Cond: (il.invoice_id = creditnote.credit_against) > -> Seq Scan on onp_crm_invoice_line il > (cost=0.00..3062.01 rows=78001 width=8) (actual time=0.005..11.259 rows=78614 > loops=1) > -> Hash (cost=510.56..510.56 rows=11492 width=8) > (actual time=8.940..8.940 rows=372 loops=1) >Buckets: 16384 Batches: 1 Memory Usage: 143kB >-> Seq Scan on onp_crm_invoice creditnote > (cost=0.00..510.56 rows=11492 width=8) (actual time=0.014..7.882 rows=11507 > loops=1) > Filter: ((sent_date <= '2016-06-27'::date) > AND ((status_key)::text = 'INVOICE_STATUS_INVOICED'::text)) > Rows Removed by Filter: 149 If it realized that this produces 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 different from the rest of that table? 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] Very slow query (3-4mn) on a table with 25millions rows
Abadie Lana <lana.aba...@iter.org> writes:
> I'm having a problem with a slow query - I tried several things to optimize
> the queries but didn't really help. The output of explain analyse shows
> sequential scan on a table of 25 million rows. Even though it is indexed and
> (I put a multi-column index on the fields used in the query), the explain
> utility shows no usage of the scan...
That index looks pretty useless judging from the rowcounts, so I'm not
surprised that the planner didn't use it. You might have better luck with
an index on the split_part expression
split_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.)
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] Performance index and table
Oscar Camuendo <oscar.dn...@hotmail.com> writes:
> I'm working on Postgresql 9.5.3 and executed a query which takes 5 or 7
> seconds and it should not take more than 0.30 milliseconds, the query is:
Have you ANALYZEd your tables lately? Some of these estimated row counts
seem awfully far off for no very 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?
Jim Nasby <jim.na...@bluetreble.com> 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 callers are concerned. > I thought we already broke out time spent in triggers as part of > EXPLAIN, ... yes ... > and that the FK "triggers" were specifically ignored? No. You get something like # explain analyze insert into cc values(1); QUERY PLAN -- Insert on cc (cost=0.00..0.01 rows=1 width=4) (actual time=0.192..0.192 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1) Planning time: 0.035 ms Trigger for constraint cc_f1_fkey: time=1.246 calls=1 Execution time: 1.473 ms (5 rows) EXPLAIN does know enough about FK triggers to label them with the associated constraint name rather than calling them something like "RI_ConstraintTrigger_c_81956"; but it does not have any ability to reach inside them. > As for function plans, ISTM that could be added to the PL handlers if we > wanted to (allow a function invocation to return an array of explain > outputs). Where would you put those, particularly for functions executed many times in the query? Would it include sub-functions recursively? I mean, yeah, in principle we could do something roughly like that, but it's not easy and presenting the results intelligibly seems almost impossible. 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?
Jim Nasby <jim.na...@bluetreble.com> writes:
> On 7/5/16 7:14 AM, Robert Klemme wrote:
>> I was wondering whether there are any plans to include the plan of the
>> FK check in EXPLAIN output. Or is there a different way to get to see
>> all the plans of triggers as well as of the main SQL?
> Unfortunately, this isn't the type of thing that would excite most of
> the core hackers, so it's unlikely any of them will pick this up.
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 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?
trafdev <traf...@mail.ru> writes: > CREATE INDEX ix_feed_sub_date >ON stats.feed_sub >USING brin >(date); > CREATE UNIQUE INDEX ixu_feed_sub >ON stats.feed_sub >USING btree >(date, gran, aid, pid, sid, fid, subid COLLATE pg_catalog."default"); > HashAggregate (cost=901171.72..912354.97 rows=344100 width=86) (actual > time=7207.825..7335.473 rows=126044 loops=1) > " Group Key: subid, sid" >Buffers: shared hit=3635804 >-> Index Scan using ixu_feed_sub on feed_sub (cost=0.56..806544.38 > rows=3440994 width=86) (actual time=0.020..3650.208 rows=3578344 loops=1) > Index Cond: ((date >= '2016-06-01 00:00:00'::timestamp without > time zone) AND (date <= '2016-06-30 00:00:00'::timestamp without time > zone) AND (gran = '1 day'::interval) AND (aid = 3)) > Buffers: shared hit=3635804 > Planning time: 0.150 ms > Execution time: 7352.009 ms Neither of those indexes is terribly well designed for this query. A btree index on (aid, gran, date) or (gran, aid, date) would work much better. See https://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html You could rearrange the column order in that giant unique index and get some of the benefit. But if you're desperate to optimize this particular query, an index not bearing so many irrelevant columns would probably be better for it. An alternative way of thinking would be to create an index with those three leading columns and then all of the other columns used by this query as later columns. That would be an even larger index, but it would allow an index-only scan, which might be quite a lot faster. The fact that you seem to be hitting about one page 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 end of the performance question too. 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
Jeff Janes <jeff.ja...@gmail.com> writes:
> On Tue, Jun 28, 2016 at 6:24 PM, <devel.brai...@xoxy.net> wrote:
>> PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit
> The current minor version of that branch is 9.3.13, so you are 9 bug
> fix releases behind.
Definitely a fair complaint.
> I don't know if this matters, because I see that my first guess of
> your problem was fixed in commit 4162a55c77cbb54acb4ac442e, which was
> already included in 9.3.4.
That commit could have helped if the problem were simply slow planning.
But I do not see how it explains a *consistent* 122-second delay.
That sounds 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
devel.brai...@xoxy.net writes:
> As you can see from the logs I posted, it appears the execution plan was
> cached (LOG: duration: 122006.000 ms bind cached-1453392550: select).
> Maybe those aren't processed by auto_explain?
In that, "cached-1453392550" is a statement name given by the client;
you'd know better than we do where it's coming from, but it has no
particular significance to the server.
The real information here is that what is taking 122 seconds is the BIND
step of extended query protocol. That explains why auto_explain doesn't
notice it; auto_explain only instruments the execution phase. Typically,
what takes time in the BIND step is planning the query, so it seems like
we have to conclude that something in planning is getting hung up. That
doesn't get us very much closer to an explanation though :-(.
Don't know 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
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] can't explain commit performance win7 vs linux : 8000/s vs 419/s
"t.dalpo...@gmail.com" <t.dalpo...@gmail.com> writes:
> Performance:
> Win7: 8000 write/sec
> Linux: 419 write/sec
My immediate reaction to that is that Windows isn't actually writing
the data to disk when it should in order to guarantee that commits
are persistent. There are multiple layers that might be trying to
optimize away the writes, and I don't know enough about Windows to
help you debug it. But see
https://www.postgresql.org/docs/9.5/static/wal-reliability.html
for some discussion.
> I don't figure out why such a difference. Also what should I expect?
> Which 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-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
Adam Brusselback <adambrusselb...@gmail.com> writes:
> I finally managed to get it compiled, patched, and working. It gave the
> same plan with the same estimates as when I turned fkey_estimates off.
OK, well, at least it's not making things worse ;-). But I think that
this estimation method isn't very helpful for antijoin cases anyway.
> I was wondering if I did things properly though, as i don't see the
> enable_fkey_estimates GUC any more. Was it removed?
Yes, that was only intended for debugging, and the consensus was that
it probably shouldn't have been committed in the first 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
Adam Brusselback <adambrusselb...@gmail.com> writes:
> Alright with that off I get:
> ...
> Way better.
OK, that confirms the suspicion that beta1's FK-join-estimation logic
is the culprit here. We had already decided that that logic is broken,
and there's a rewrite in progress:
https://www.postgresql.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-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
Adam Brusselback <adambrusselb...@gmail.com> 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 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
Adam Brusselback <adambrusselb...@gmail.com> writes:
> Hey all, testing out 9.6 beta 1 right now on Debian 8.5.
> I have a query that is much slower on 9.6 than 9.5.3.
The rowcount estimates in 9.6 seem way off. Did you ANALYZE the tables
after loading them into 9.6? Maybe you forgot some statistics target
settings?
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-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Index not used
meike.talb...@women-at-work.org writes: > When I query this through pgsql, the queries are fast as expected. > select * from push_topic where guid = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5' > Index Scan using push_topic_idx_topicguid on push_topic (cost=0.42..8.44 > rows=1 width=103) (actual time=0.117..0.121 rows=1 loops=1) > Index Cond: ((guid)::bpchar = > 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::bpchar) > Buffers: shared hit=3 read=1 > Total runtime: 0.191 ms > However when I run the exact query through a different application > (CodeSynthesis ORM) the query is very slow (~ 115ms logged) > I noted this is due to a sequential scan happening on the table instead of an > index scan. It looks like what that app is actually issuing is something different from what you tested by hand, to wit select * from push_topic where guid = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::text which causes the comparison to be resolved as texteq not bpchareq, ie you effectively have select * from push_topic where guid::text = 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::text and that doesn't match a bpchar index. 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
Re: [PERFORM] Many-to-many performance problem
Yves Dorfsman <y...@zioup.com> writes: > On 2016-06-10 08:13, Tom Lane wrote: >> It looks like everything is fine as long as all the data the query needs >> is already in PG's shared buffers. As soon as it has to go to disk, >> you're hurting, because disk reads seem to be taking ~10ms on average. > -> Index Scan using msgs_message_pkey on msgs_message > (cost=0.43..8.04 rows=1 width=47) (actual time=18.550..18.559 rows=0 > loops=3556) > Index Cond: (id = msgs_message_labels.message_id) > Filter: (is_active AND is_handled AND has_labels > AND (NOT is_archived) AND (created_on < '2016-06-10 > 07:11:06.381+00'::timestamp with time zone) AND (org_id = 7)) > Rows Removed by Filter: 1 > Buffers: shared hit=11032 read=3235 dirtied=5 > Do you mean that it reads the index from disk? Or that it looks things up in > the index, and fetch data on disk (based on that lookup)? The "reads" here might be either index pages or table pages; we can't tell from EXPLAIN's statistics. It's probably a good bet that more of them are table pages than index pages though, just because the index should be a lot smaller than the table and more fully represented in cache. As for the numbers, we see that 18.559 * 3556 = 65995 ms were spent in this indexscan plan node, versus negligible time for the same plan node when no reads happened. So we can blame pretty much all that time on the 3235 disk reads, giving an average per read of just over 20ms. Some of the other plan nodes show lower averages, though, so I was conservative and said "~10 ms". 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
