On Thu, 16 Jan 2025 at 07:29, bruno vieira da silva
wrote:
> On pg 17 now we have better visibility on the I/O required during query
> planning.
> so, as part of an ongoing design work for table partitioning I was analyzing
> the performance implications of having more or less partitions.
> In o
On Sat, 28 Dec 2024 at 00:34, wrote:
> Sometimes one is better and sometimes the other is best.
> In Oracle, the choice is done by COST. This should be the same for
> PostgreSQL. In essence, it is the same thing for views: inlining or
> materializing...
I don't doubt you. However, I did already
On Tue, 26 Nov 2024 at 10:03, Ba Jinsheng wrote:
> If we specify ``NOT MATERIALIZED`` for the query, the execution time is
> reduced from 1min to 1s due to the usage of inline CTE. It seems expected as
> described in the PostgreSQL documentation. However, from the code:
> https://github.com/pos
On Tue, 26 Nov 2024 at 09:55, Ba Jinsheng wrote:
> TPC-DS query 95:
> Its execution time is nearly 1 min:
>CTE ws_wh
> -> Hash Join (cost=37772.14..198810.77 rows=7242361 width=12) (actual
> time=211.161..1443.926 rows=6644004 loops=1)
> If applying this patch:
> - if (is_inne
On Fri, 8 Nov 2024 at 10:54, Ed Sabol wrote:
> The good news is that, after some research and experimentation, I was able to
> fix this performance degradation by setting random_page_cost = 2.0. We've
> always used the default values for seq_page_cost and random_page_cost (1.0
> and 4.0, respec
On Fri, 25 Oct 2024 at 22:26, Ba Jinsheng wrote:
> I guess the better query plan is not considered when comparing the cost of
> paths?
You might want to change effective_cache_size is set high enough.
Something like 50-75% of RAM is likely fine.
David
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
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 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 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 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 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 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 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 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 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, 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, 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 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 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 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 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 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 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 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 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 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
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 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,
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 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 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 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 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 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 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, 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 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 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 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 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, 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 Fri, 23 Jul 2021 at 04:14, Peter Geoghegan wrote:
>
> On Thu, Jul 22, 2021 at 8:45 AM Tom Lane wrote:
> > That is ... weird. Maybe you have found a bug in the spill-to-disk logic;
> > it's quite new after all. Can you extract a self-contained test case that
> > behaves this way?
>
> I wonder
On Fri, 23 Jul 2021 at 03:56, Tom Lane wrote:
> So basically, we now have a hard restriction that hashaggs can't use
> more than INT_MAX kilobytes, or approximately 2.5GB, and this use case
> is getting eaten alive by that restriction. Seems like we need to
> do something about that.
Hmm, math c
On Thu, 22 Jul 2021 at 16:37, l...@laurent-hasson.com
wrote:
> Seems like no cigar ☹ See plan pasted below. I changed the conf as follows:
> - hash_mem_multiplier = '2'
> - work_mem = '1GB'
> Batches: 5 Memory Usage: 2400305kB Disk Usage: 126560kB
You might want to keep going highe
On Sat, 17 Jul 2021 at 16:40, Justin Pryzby wrote:
> You could run a single UPDATE rather than 30k triggers.
> Or switch to an INSERT on the table, with an index on it, and call
> max(last_parent_table_change) from whatever needs to ingest it. And prune the
> old entries and vacuum it outside the
On Mon, 12 Jul 2021 at 12:37, Nagaraj Raj wrote:
> personally, I feel this design is very bad compared to other DB servers.
I'm not sure exactly what you're referring to here as you didn't quote
it, but my guess is you mean our lack of global index support.
Generally, there's not all that much c
On Thu, 1 Jul 2021 at 08:56, Manuel Weitzman wrote:
> For each of these RestrictInfos there *could* be one cache miss on
> cached_scansel() that *could* force the planner to compute
> get_actual_variable_range() for the same variable (a.a) over and over,
> as mergejoinscansel() always computes the
On Fri, 4 Jun 2021 at 23:53, Taras Savchuk wrote:
> My real life test is to "register" 10 _same_ documents (провести документы)
> in each of 1C/PostgreSQL DBs. Both PostgreSQL DBs are identical and just
> before test imported to PostgreSQL via application server (DT import).
> On Windows Server
On Wed, 26 May 2021 at 11:38, Nagaraj Raj wrote:
>
> Apologies, I didn't understand you completely.
>
> > 1. Those that have sub_soc.soc = 'NFWJYW0' and sub_soc.curr_ind = 'Y'
>
> > It can use constraint exclusion on these to only scan applicable partitions.
>
> > 2. Those that have (acc.acct = '
On Sat, 22 May 2021 at 10:59, Nagaraj Raj wrote:
> ERROR: empty range bound specified for partition "mytable_z" DETAIL:
> Specified lower bound ('Z') is greater than or equal to upper bound ('[').
> SQL state: 42P17
It looks like '[' does not come after 'Z' in your collation.
David
On Sat, 22 May 2021 at 04:38, Nagaraj Raj wrote:
> I am trying to create partitions on the table which have around 2BIL records
> and users will always look for the "name", its not possible to create a
> partition with a list, so we are trying to create a partition-based first
> letter of the n
On Fri, 21 May 2021 at 19:02, Nagaraj Raj wrote:
> then what would be the range of Z
> FROM (Z) to (?) ;
postgres=# select chr(ascii('z')+1) ;
chr
-
{
(1 row)
> same way for 9
postgres=# select chr(ascii('9')+1) ;
chr
-
:
(1 row)
https://en.wikipedia.org/wiki/ASCII
You can also u
On Fri, 21 May 2021 at 12:32, Nagaraj Raj wrote:
> I am trying to create partitions on the table based on first letter of the
> column record value using inherit relation & check constraint.
You'll get much better performance out of native partitioning than you
will with the old inheritance met
On Tue, 18 May 2021 at 08:42, wrote:
> Running delete from table1 where id = 48938 the trigger for constraint runs
> for 20 seconds
>
> Event when doing a simple select from table2 where table1_id = 48938 takes
> about 8 seconds
Does EXPLAIN show it uses a seq scan for this 8-second SELECT?
If
On Sat, 15 May 2021 at 00:39, KES wrote:
>
> Thank you for detailed explanation. I glad to hear that I can use aliases and
> this will be recognized and optimization is applied.
>
> >We'd need some sort of ability to assign ressortgroupref to a particular
> >column within a
> whole-row var
> Cou
On Sun, 16 May 2021 at 02:34, Eugen Konkov wrote:
> I found a case when `not assigning a ressortgroupref to the whole-row var`
> cause
> wrong window function calculations.
>
> I use same query. The difference come when I wrap my query into
> function. (see full queries in attachment)
>
>
On Fri, 14 May 2021 at 02:38, Eugen Konkov wrote:
> Now I create minimal reproducible test case.
> https://dbfiddle.uk/?rdbms=postgres_13&fiddle=761a00fb599789d3db31b120851d6341
>
> Optimization is not applyed when I filter/partition by column using composite
> type name.
You probably already kn
On Tue, 4 May 2021 at 22:05, Alex wrote:
> Shouldn't this process be automatic based on some heuristics?
When plan_cache_mode is set to "auto", then the decision to use a
generic or custom plan is cost-based. See [1]. There's a fairly crude
method there for estimating the effort required to repla
On Thu, 22 Apr 2021 at 00:03, Jeremy Schneider wrote:
>
> Two years later, I still remember this. And today I just confirmed
> someone hitting this on open source PG13.
The only thing that changed about get_actual_variable_range() is that
it now uses a SnapshotNonVacuumable snapshot. Previously a
On Wed, 3 Mar 2021 at 10:04, Michael Lewis wrote:
> Are there guidelines or principles you could share about writing the group by
> clause such that it is more efficient?
If you have the option of writing them in the same order as an
existing btree index that covers the entire GROUP BY clause (i
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 there is a missing optimization rule related to pushing
> th
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
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, 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 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, 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 Tue, 10 Mar 2020 at 02:08, Mariel Cherkassky
wrote:
> PG12 - 3 PARTITIONS
>
> QUERY
> PLAN
> ---
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
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
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
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
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
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
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
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
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
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
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
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
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
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
clause, if you know NULLs are not possible?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
1 - 100 of 126 matches
Mail list logo
