xplain verbose select lower('TEST');
QUERY PLAN
---
Result (cost=0.00..0.01 rows=1 width=32)
Output: 'test'::text
(2 rows)
Would be interesting to see what changes without the IMMUTABLE flag.
--
David Rowley
on, but not planning to personally.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
stimation for BRIN may realise
that the bitmap heap scan is not a good option, although I'm not sure
it'll be better than what the current v10 plan is using.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Wed, 27 Apr 2022 at 19:54, Emil Iggland wrote:
>
> > You've got the wrong column order (for this query anyway) in that
> > index. It'd work a lot better if dataview were the first column;
> I might be misunderstanding you, but I assume that you are suggesting an
> index on (dataview, valuet
On Tue, 3 May 2022 at 23:05, Benjamin Coutu wrote:
> -> Memoize (cost=0.58..0.67 rows=1 width=16) (actual time=0.002..0.002
> rows=0 loops=4067215)
> Cache Key: e2.field, e2.index
> Cache Mode: logical
> Hits: 0 Misses: 4067215 Evictions: 3228355 Overflows: 0 Memor
On Wed, 4 May 2022 at 00:21, Benjamin Coutu wrote:
> Thanks David, using extended statistics for both (and only for both) tables
> solved this problem.
Oh, whoops. I did get that backwards. The estimate used by the
Memoize costing code is from the outer side of the join, which is the
extdataemp
On Wed, 4 May 2022 at 06:11, Levi Aul wrote:
> It is our expectation that this query “should” be able to be cheap-to-compute
> and effectively instantaneous. (It’s clear to us how we would make it so,
> given a simple LMDB-like sorted key-value store: prefix-match on
> holder_address; take the
On Thu, 5 May 2022 at 11:15, André Hänsel wrote:
>
> Quick(?) question... why is there a Sort node after an Index Only Scan?
> Shouldn't the index already spit out sorted tuples?
>
> CREATE INDEX ON orders_test(shipping_date, order_id);
>
> EXPLAIN ANALYZE SELECT
> FROM orders_test
> WHERE TRUE
>
On Tue, 2 Aug 2022 at 19:48, Ameya Bidwalkar
wrote:
> We have a Postgresql 13 database where we have a single table with several
> millions of rows . We plan to partition it based on timestamp .
> We have been seeking advice for best practices for building this.
> This table will get lots of u
On Wed, 30 Nov 2022 at 03:31, Tom Lane wrote:
>
> Alvaro Herrera writes:
> > IMO it was a mistake to turn JIT on in the default config, so that's one
> > thing you'll likely want to change.
>
> I wouldn't necessarily go quite that far, but I do think that the
> default cost thresholds for invokin
On Tue, 6 Dec 2022 at 18:28, James Pang (chaolpan) wrote:
>-> Index Scan using idx_x_time on x (cost=0.44..8.48 rows=1
> width=2923) (actual time=8136.242..8136.242 rows=0 loops=1)
> Index Cond: ((starttime = $7) AND (endtime = $8))
> Filter: ((password IS NULL) AN
On Tue, 6 Dec 2022 at 20:17, James Pang (chaolpan) wrote:
> Could you provide the function name for generic plan selectivity estimation?
If you look at eqsel_internal(), you'll see there are two functions
that it'll call var_eq_const() for Consts and otherwise
var_eq_non_const(). It'll take the
On Thu, 15 Dec 2022 at 21:12, James Pang (chaolpan) wrote:
>We had some load test ( DML inserts/deletes/updates/ on tens of hash
> partition tables) and found that PGV14 slow down 10-15% compared with PGV13.
> Same test server, same schema tables and data. From pg_stat_statements, sql
> e
On Sun, 18 Dec 2022 at 23:44, João Paulo Luís wrote:
> Meanwhile, as a one-time workaround I've disabled the hashagg algorithm,
The way the query planner determines if Hash Aggregate's hash table
will fit in work_mem or not is based on the n_distinct estimate of the
columns being grouped on. You
On Wed, 1 Feb 2023 at 18:39, Alex Kaiser wrote:
> postgres=# set force_parallel_mode = on;
There's been a bit of debate about that GUC and I'm wondering how you
came to the conclusion that it might help you. Can you share details
of how you found out about it and what made you choose to set it to
On Thu, 2 Feb 2023 at 14:49, Thomas Munro wrote:
> If I had more timerons myself, I'd like to try to make parallel
> function scans, or parallel CTE scans, work...
I've not really looked in detail but I thought parallel VALUES scan
might be easier than those two.
David
On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle wrote:
> Basically- window partition functions don't take advantage of existing table
> partitions. I use window functions as a more powerful GROUP BY clause that
> preserves row-by-row information- super handy for a lot of things.
>
> In particular,
message-id/flat/caaphdvojkdbr3mr59jxmacybyhb6q_5qpru+dy93en8wm+x...@mail.gmail.com
> Ben
>
> On Wed, Feb 8, 2023 at 2:36 PM David Rowley wrote:
>>
>> On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle wrote:
>> > Basically- window partition functions don't take advantage of existing
>> >
On Wed, 15 Mar 2023 at 10:41, Laurenz Albe wrote:
> I think you want subpartitioning, like
>
> CREATE TABLE humans (
> hash bytea,
> fname text,
> dob date
> ) PARTITION BY LIST (EXTRACT (YEAR FROM dob));
This may be perfectly fine, but it is also important to highlight that
pa
On Thu, 16 Mar 2023 at 00:47, James Robertson wrote:
> or do we get?
>
> TopLevelTable
> |
> |> worker-thread 1 (default catch)
> |> worker thread 2 -> sub-table 1.1
> |> worker thread 3 -> sub-table 1.2
> |> worker thread 4 -> sub-table 1.n
> |
> |> wor
On Mon, 17 Apr 2023 at 05:00, Clemens Eisserer wrote:
> Example:
> table smartmeter with non-null column ts (timestamp with time zone)
> and brinc index on ts, no pk to avoid a btree index.
> Sensor values are stored every 5s, so for 1 month there are about 370k
> rows - and in total the table cur
On Tue, 18 Apr 2023 at 12:35, peter plachta wrote:
> I increased work_mem to 2Gb
maintenance_work_mem is the configuration option that vacuum uses to
control how much memory it'll make available for storage of dead
tuples. I believe 1GB would allow 178,956,970 tuples to be stored
before multiple
On Thu, 10 Aug 2023 at 20:36, Dorian Hoxha wrote:
> > Do Not Use Partial Indexes as a Substitute for Partitioning
> > While a search in this larger index might have to descend through a couple
> > more tree levels than a search in a smaller index, that's almost certainly
> > going to be cheaper
On Mon, 28 Aug 2023 at 19:21, Les wrote:
> More important question is, how can I find out why the index was not auto
> vacuumed.
You should have a look at pg_stat_user_tables. It'll let you know if
the table is being autovacuumed and how often. If you're concerned
about autovacuum not running
On Tue, 29 Aug 2023 at 19:40, Philippe Pepiot wrote:
> I'm trying to implement some range partitioning on timeseries data. But it
> looks some queries involving date_trunc() doesn't make use of partitioning.
>
> BEGIN;
> CREATE TABLE test (
> time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> va
On Thu, 31 Aug 2023 at 06:32, Rondat Flyag wrote:
> I tried VACUUM ANALYZE for three tables, but without success. I also tried to
> set enable_seqscan=off and the query took even more time. If I set
> enable_sort=off then the query takes a lot of time and I cancel it.
>
> Please see the attached
On Mon, 11 Sept 2023 at 18:16, Laurenz Albe wrote:
> Also, there are quite a lot of indexes on "test_db_bench_1". On a test
> database, drop some
> indexes and see if that makes a difference.
Yeah, I count 3 that either have the key columns as some prefix of
another index or are just a duplicat
On Mon, 11 Sept 2023 at 21:54, Mikhail Balayan wrote:
> Could it be a regression? I'll check it on PG14 when I get a chance.
I'm not sure if you're asking for help here because you need planning
to be faster than it currently is, or if it's because you believe that
planning should always be faste
On Tue, 12 Sept 2023 at 02:27, Tom Lane wrote:
>
> David Rowley writes:
> > I'm not sure if you're asking for help here because you need planning
> > to be faster than it currently is, or if it's because you believe that
> > planning should always be
On Mon, 6 Nov 2023 at 08:37, Abraham, Danny wrote:
>
> Both plans refer to the same DB.
JDBC is making use of PREPARE statements, whereas psql, unless you're
using PREPARE is not.
> #1 – Fast – using psql or old JDBC driver
The absence of any $1 type parameters here shows that's a custom plan
t
On Tue, 23 Jan 2024 at 20:45, Jean-Christophe Boggio
wrote:
> explain says actual time between 1.093→1.388 but final execution time says
> 132.880ms?!?
The 1.388 indicates the total time spent in that node starting from
just before the node was executed for the first time up until the node
retur
On Fri, 26 Jan 2024 at 02:31, Jean-Christophe Boggio
wrote:
> You are absolutely correct : the EXPLAIN without ANALYZE gives about the same
> results. Also, minimizing the amount of workmem in postgresql.conf changes
> drastically the timings. So that means memory allocation is eating up a lot
On Fri, 26 Jan 2024 at 17:23, Jean-Christophe Boggio
wrote:
> Let me know if I can do anything to provide you with more useful
> benchmark. The DB is still very small so it is easy to do tests.
What I was looking to find out was if there was some enable_* GUC that
you could turn off that would ma
On Wed, 31 Jan 2024 at 09:09, Philip Semanchuk
wrote:
> So in your case those 5m rows that you deleted were probably still clogging
> up your table until you ran VACUUM FULL.
It seems more likely to me that the VACUUM removed the rows and just
left empty pages in the table. Since there's no ind
On Thu, 30 May 2024 at 13:03, Darwin O'Connor wrote:
> Is there a PostgreSQL setting that can control how it judges plans?
There's nothing like that, unfortunately.
> Here is a recent example of a query that finds the last time at a stop
> filtered for a certain route it has to look up another
On Mon, 1 Jul 2024 at 21:45, James Pang wrote:
>Buffers: shared hit=110246 <<< here planning need access a lot of
> buffers
> Planning Time: 81.850 ms
> Execution Time: 0.034 ms
>
>could you help why planning need a lot of shared buffers access ?
Perhaps you have lots of bloat
On Mon, 1 Jul 2024 at 22:20, Pavel Stehule wrote:
> The planners get min/max range from indexes. So some user's indexes can be
> bloated too with similar effect
I considered that, but it doesn't apply to this query as there are no
range quals.
David
On Fri, 5 Jul 2024 at 12:50, James Pang wrote:
>we have a daily vacuumdb and analyze job, generally speaking it's done in
> seconds, sometimes it suddenly running more than tens of minutes with same
> bind variable values and huge temp space got used and at that time, explain
> show "Hash
On Sat, 6 Jul 2024 at 02:43, James Pang wrote:
>for nest loop path, since the first one estimated only "8" rows ,
> and they use partitionkeyid as joinkey and all are hash partitions , is it
> better to estimate cost to 8 (loop times) * 1600 = 12800 (each one loop map
> to only 1
7;t be used in
conjunction with the options you've mentioned.
pgbench will perform a vacuum before an actual test run, so perhaps
that's what you're seeing. You may also have noticed it also didn't
perform the create tables and data population too without -i.
--
David R
tribute 200 then it must deform 1-199 first.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
ou might get more specific recommendations if you mention how much
RAM the server has and how big the data is now and will be in the
future.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
to be written out
before the new buffer can be loaded in. In a worst-case scenario, a
backend performing a query would have to do this. pg_stat_bgwriter is
your friend.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
the most of that effort.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
er useful buffers to appear 0 times.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
u get better performance from smaller shared
buffers.
I think the best thing you can go and do is to go and test this. Write
some code that mocks up a realistic production workload and see where
you get the best performance.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQ
/flat/CAMkU%3D1zPVbez_HWao781L8PzFk%2Bd1J8VaJuhyjUHaRifk6OcUA%40mail.gmail.com#7c6d3178c18103d8508f3ec5982b1b8e
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Wed, 28 Nov 2018 at 03:16, Sanyo Moura wrote:
> 11.0
> Planning Time: 7.238 ms
> Planning Time: 2.638 ms
>
> 11.5
> Planning Time: 15138.533 ms
> Execution Time: 2.310 ms
Does it still take that long after running ANALYZE on the partitioned table?
--
David Rowley
n to the number of
buffers read and how long they took to read. If you find that these
don't explain the variation then something else is at fault, perhaps
CPU contention, or perhaps swapping due to high memory usage.
It also seems pretty strange that you should need to use DIST
be good to know what random_page_cost is set to, and
also if effective_cache_size isn't set too high. Increasing
random_page_cost would help reduce the chances of this nested loop
plan, but it's a pretty global change and could also have a negative
effect on other queries.
--
David Row
On Sat, 29 Dec 2018 at 20:15, Justin Pryzby wrote:
> On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote:
> > Unfortunately, I don't think that'll help this situation. Extended
> > statistics are currently only handled for base quals
dex, we could have rewritten the query as such automatically but
it's not / we don't. I believe I've mentioned about improving this
somewhere in the distant past of the -hackers mailing list, although I
can't find it right now. I recall naming the idea "scalar value lookup
joins", but development didn't get much beyond thinking of that name)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Fri, 4 Jan 2019 at 01:57, Abadie Lana wrote:
> 4) name is unique, constraint and index created. Right index is picked up and
> query time is rather constant there 40sec.
That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
--
David Rowley
> From: David Rowley
> Sent: 03 January 2019 14:01
> That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of
> that?
>
> explain (analyze,buffers) select
> 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.st
On Fri, 4 Jan 2019 at 02:20, Abadie Lana wrote:
> > From: David Rowley
> > Sent: 03 January 2019 14:01
> Right, so you need to check your indexes on sample_ctrl_year and
> sample_buil_year. You need an index on (channel_id, smpl_time) on those.
> These indexes exist alread
HERE c.channel_id = (select
channel_id from channel where name = '...'). That's pretty different
to what you have above.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
e divided by the number of loops, which in this case is 3, one
per process working on that part of the plan. There are two workers,
but also the main process helps out too.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
builds take?
It would certainly be good to look at psql's \d tmp_outpatient_rev
output to ensure that the index is not marked as INVALID.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
to-vacuum workers are busy more often than not,
then they're likely running too slowly and should be set to run more
quickly.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
after
vacuum started.
I'd recommend reading the manual or Tomas Vondra's blog about vacuum
costs. It's not overly complex, once you understand what each of the
vacuum settings does.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
g like
75% of the machine's memory, and/or tweak random page cost down, if
it's set to the standard 4 setting. modern SSDs are pretty fast at
random reads. HDDs, not so much.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
y find that all of the
workers are busy most of the time. If so, that indicates that the
cost limits need to be raised.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
clause, if you know NULLs are not possible?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
e costs
would have to be off, which might cause you some pain.
The transformation mentioned earlier could only work if the arguments
of the IS NOT DISTINCT FROM were Vars or Consts. It couldn't work with
Params since the values are unknown to the planner.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Const. If it had been another Var then it wouldn't be safe to use.
What other unsafe cases are there? Is there a way we can always
identify unsafe cases during planning? ... are the sorts of questions
someone implementing this would be faced with.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
n a case like this, it is best to
> just go with the partitioned table anyway.
It sounds like you might want something like partition-wise join that
exists in PG11.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
ARPASSEDSTATION (SMTOC, SVIN, SSTATIONCD, DWORKDATE);
Should help speed up the subquery and provide pre-sorted input to the
outer aggregate. If you like, you could add SLINENO to the end of the
index to allow an index-only scan which may result in further
performance improvements.
Without the index, you're forced to sort, but at least it's just one
sort instead of two.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
ant it fixed, just VACUUM the table. You should likely be doing that
anyway directly after your bulk delete.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
hough, that's likely only going to make a very small difference, if
any, than getting rid of the planning completely.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
olved after a
vacuum. Maybe run VACUUM VERBOSE on the table and double check
there's not some large amount of tuples that are "nonremovable".
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
4:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-02
> 05:00:00-05'::timestamp with time zone))
>
> Is there some reason why the partition constraints aren't excluding any of the
> index scans ?
Yeah, we don't do anything to remove base quals that are redundant due
to the partition constraint.
There was a patch [1] to try and fix this but it's not seen any recent activity.
[1] https://commitfest.postgresql.org/19/1264/
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
QL Team: can You do this?
You'll need to either reduce the number of partitions down to
something realistic or wait for 12.0.
The work done to speed up the planner with partitioned tables for v12
won't be going into v11.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
geqo_seed did change the plan.
(And you could be certain the plan did not change for some other
reason like an auto-analyze).
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
nerated by Hibernate, then that sounds like a problem
with Hibernate. PostgreSQL does not currently attempt to do any
rewrites which convert OR clauses to use UNION or UNION ALL. No amount
of tweaking the planner settings is going to change that fact.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
at index definition will be prohibited since:
>
> https://www.postgresql.org/docs/9.6/release-9-6.html
> |Disallow creation of indexes on system columns, except for OID columns
> (David Rowley)
> |Such indexes were never considered supported, and would very possibly
> misbehave si
and reproducible benchmarks
should be used as evidence to support discussion. Doing worst-case and
average-case benchmarks initially will save you time, as someone will
almost certainly ask if you don't do it.
(I've not been following the thread for the patch)
--
David Rowley
On Mon, 9 Mar 2020 at 05:05, Mariel Cherkassky
wrote:
> PG12 :
> Planning Time: 8.157 ms
> Execution Time: 2.920 ms
> (22 rows)
>
>
> PG96 :
> Planning time: 0.815 ms
> Execution time: 0.158 ms
> (12 rows)
8 ms seems pretty slow to planning that query. Does the planning time
drop if you execu
On Tue, 10 Mar 2020 at 02:08, Mariel Cherkassky
wrote:
> PG12 - 3 PARTITIONS
>
> QUERY
> PLAN
> ---
On Thu, 16 Apr 2020 at 07:56, singh...@gmail.com wrote:
> We have an odd issue where specifying the same where clause twice causes PG
> to pick a much more efficent plan. We would like to know why.
> The EXPLAIN ANALYZE for both queries can be found here:-
> Query A: https://explain.depesz.com/s
On Mon, 4 May 2020 at 15:52, Arya F wrote:
>
> On Sun, May 3, 2020 at 11:46 PM Michael Lewis wrote:
> >
> > What kinds of storage (ssd or old 5400 rpm)? What else is this machine
> > running?
>
> Not an SSD, but an old 1TB 7200 RPM HDD
>
> > What configs have been customized such as work_mem or
On Mon, 4 May 2020 at 02:35, James Thompson wrote:
> buffers do look different - but still, reading 42k doesn't seem like it would
> cause a delay of 4m?
You could do: SET track_io_timing TO on;
then: EXPLAIN (ANALYZE, BUFFERS) your query and see if the time is
spent doing IO.
David
On Fri, 8 May 2020 at 10:00, David G. Johnston
wrote:
>
> On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote:
>>
>> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS
>> pa_process_activity_id FROM process_activity pa WHERE pa.app_id =
>> '126502930200650' AND pa.c
On Fri, 8 May 2020 at 09:18, github kran wrote:
> 1) We haven't changed anything related to autovacuum except a work_mem
> parameter which was increased to 4 GB which I believe is not related to
> autovacuum
It might want to look into increasing vacuum_cost_limit to something
well above 200 or
On Fri, 8 May 2020 at 13:51, github kran wrote:
> I can't either DROP or ALTER any other tables ( REMOVE Inheritance for
> any of old tables where the WRITES are not getting written to). Any of the
> ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I WAITED FOR
> SEVERAL MI
On Sat, 6 Jun 2020 at 14:12, Cedric Leong wrote:
> Somewhat unrelated but note to anyone who wants to swap out partition keys.
> Don't create a clone of the table with the new partition key and insert data.
> It messes up the query planner massively and makes everything much slower.
That compla
On Sat, 6 Jun 2020 at 14:49, Cedric Leong wrote:
> It's less of a complaint rather than just a warning not to do what I did.
My point was really that nobody really knew what you did or what you
did it on. So it didn't seem like a worthwhile warning as it
completely lacked detail.
> These tests a
On Sun, 7 Jun 2020 at 23:41, MichaelDBA wrote:
> The article referenced below assumes a worst case scenario for bulk-loading
> with hash partitioned tables. It assumes that the values being inserted are
> in strict ascending or descending order with no gaps (like a sequence number
> incrementi
On Thu, 11 Jun 2020 at 07:41, Mikkel Lauritsen wrote:
> I have a query that runs much slower in Postgres on Windows than on
> Linux
> Using explain analyze on the database running on Windows I get
>
> -> Index Scan using event_pkey on event t1 (cost=0.56..0.95 rows=1
> width=295) (actual time=0
On Sat, 13 Jun 2020 at 06:26, regrog wrote:
>
> I'm facing performance issues migrating from postgres 10 to 12 (also from 11
> to 12) even with a new DB.
> Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12.
This appears to be down to bad statistics that cause pg12 to choose a
n
On Sat, 13 Jun 2020 at 15:11, Tom Lane wrote:
> I expect you're getting a fairly decent estimate for the "contype <>
> ALL" condition, but the planner has no idea what to make of the CASE
> construct, so it just falls back to a hard-wired default estimate.
This feels quite similar to [1].
I wond
On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote:
>
> David Rowley writes:
> > I wondered if it would be more simple to add some smarts to look a bit
> > deeper into case statements for selectivity estimation purposes. An
> > OpExpr like:
> > CASE c.contype WHEN
On Sat, 13 Jun 2020 at 19:52, David Rowley wrote:
>
> On Sat, 13 Jun 2020 at 16:07, Tom Lane wrote:
> >
> > David Rowley writes:
> > > I wondered if it would be more simple to add some smarts to look a bit
> > > deeper into case statements for selectivity
On Mon, 15 Jun 2020 at 10:46, Nagaraj Raj wrote:
> CREATE TABLE test1
> (
...
> CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
>
> );
> CREATE TABLE test2
> (
...
> CONSTRAINT pk_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
> );
>
>
> User query:
>
On Fri, 21 Aug 2020 at 11:01, Michael Lewis wrote:
>
> On Thu, Aug 20, 2020 at 4:40 PM Jim Jarvie wrote:
>>
>> On 20-Aug.-2020 17:42, Michael Lewis wrote:
>>
>> Can you share an explain analyze for the query that does the select for
>> update? I wouldn't assume that partition pruning is possible
On Sat, 5 Sep 2020 at 10:20, Nagaraj Raj wrote:
> I added the index as you suggested and the planner going through the bitmap
> index scan,heap and the new planner is,
> HaOx | explain.depesz.com
In addition to that index, you could consider moving away from
standard SQL and use DISTINCT ON, whi
On Tue, 8 Sep 2020 at 06:05, Raj wrote:
>
> > This would not exactly look like a bug, because the message says "to
> > be locked", so at least it's not allowing two workers to lock the same
> > tuple. But it seems that the skip-locked mode should not make an error
> > out of this, but treat it as
On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen wrote:
>
> The crux of our issue is that the query planner chooses a nested loop join
> for this query. Essentially making this query (and other queries) take a very
> long time to complete. In contrast, by toggling `enable_nestloop` and
> `enable
On Tue, 27 Oct 2020 at 06:54, Ehrenreich, Sigrid wrote:
> -> Hash Join (cost=226.27..423.82 rows=115 width=0) (actual
> time=3.150..7.511 rows=3344 loops=1) <=== With the FK, the
> estimation should be 3344, but it is 115 rows
I'd have expected this to find the foreign key and hav
On Wed, 4 Nov 2020 at 02:20, Ehrenreich, Sigrid wrote:
>
> -- Statement
> explain SELECT
> count(*)
> FROM
> dim INNER JOIN fact ON (dim.part_key=fact.part_key)
> WHERE dim.part_key >= 110 and dim.part_key <= 160;
>
> Plan shows me, that all partitions are scanned:
> Aggregate (cost=461.00..461.0
On Tue, 2 Mar 2021 at 21:53, Liu, Xinyu wrote:
> *Expected Behavior
>
> Since these two queries are semantically equivalent, we were hoping that
> PostgreSQL would evaluate them in roughly the same amount of time.
> It looks to me that different order of group by clauses triggers different
> pla
1 - 100 of 129 matches
Mail list logo
