On Wed, 7 Sept 2022 at 07:40, Levi Aul wrote:
> In other words, our workload is inherently one that acquires "way too many
> locks." Our largest performance bottleneck, according to pg_wait_sampling, is
> the LockManager itself. Despite most of our queries spending only
> milliseconds actually
On Wed, 7 Sept 2022 at 13:33, Levi Aul wrote:
> To be clear, this isn't a bug report. There is no bug—everything is working
> exactly as it should. The partitions are not being pruned because the
> workload consists of OLAP aggregations that fetch a small number of rows
> spread across all part
On Tue, 11 Oct 2022 at 22:52, Josef Šimánek wrote:
> This can make some queries fail since there will be no way to gather
> data without seqscan.
Disabling enable_seqscan only adds a const penalty to Seq Scans. It
does not outright disallow them altogether.
Having said that, having Paths with th
On Tue, 11 Oct 2022 at 16:13, gzh wrote:
> new=# explain analyze select 2 from analyze_word_reports where (cseid = 94)
> limit 1;
> Limit (cost=0.00..0.43 rows=1 width=4) (actual time=2156.964..2156.966
> rows=1 loops=1)
>-> Seq Scan on analyze_word_reports (cost=0.00..528550.75 rows=122
On Wed, 12 Oct 2022 at 13:06, Klint Gore wrote:
> Limit (cost=0.56..28349.28 rows=1 width=4) (actual time=0.039..0.040 rows=1
> loops=1)
> -> Unique (cost=0.56..28349.28 rows=1 width=4) (actual time=0.039..0.039
> rows=1 loops=1)
> -> Index Only Scan using idx on tbl (cost=0.56..2
On Thu, 10 Nov 2022 at 04:11, wrote:
> If someone would create delayed locking for generic plans, after parameters
> are known and partition pruning occurs, I believe generic plan will be on
> pars with custom.
> So, I`m sticking with plan cache parameter for feature development, that was
> clear.
On Thu, 24 Nov 2022 at 11:34, Ted Toth wrote:
>
> On Wed, Nov 23, 2022 at 4:01 PM Tom Lane wrote:
>> Then you're stuck managing it manually. But ATTACH PARTITION is
>> not relevant to INHERITS-style partitioning.
>
> That's the part that wasn't clear to me, thanks.
Would this have been more cle
On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi wrote:
> Coming back to this thread after a while.. we have to remove OID on a 6 TB (5
> TB of indexes) table and ALTER TABLE is gonna block the table and is gonna
> take hours...
You may want to look into exploiting table inheritance for this.
So
On Sun, 4 Dec 2022 at 00:14, yang zhao wrote:
> So,can we try to split memory to pieces and qsort every one,and merge than
> all in memory,I have tried this in my local code, and got about 12%
> improvement when memory is enough.
We're not very good at CPU cache awareness. The hard part here w
On Thu, 8 Dec 2022 at 19:59, higherone wrote:
> I know there's a pg_stats_user_indexes that gives statistics about the index
> scans/fetches, but I don't find any information about how many
> writes(insert/update/delete) happen on this individual index.
> Is there some tool that could achieve th
On Thu, 22 Dec 2022 at 13:31, David Gilman wrote:
>
> When a fdw table participates in query planning and finds itself as
> part of a join it can output a parameterized path. If chosen, Postgres
> will dutifully call the fdw over and over via IterateForeignScan to
> fetch matching tuples. Many fdw
On Fri, 13 Jan 2023 at 07:33, Dimitrios Apostolou wrote:
>
> I have a very simple NATURAL JOIN that does not fit in the work_mem. Why
> does the query planner prefer a hash join that needs 361s, while with a
> sort operation and a merge join it takes only 13s?
It's a simple matter of that the Ha
On Tue, 31 Jan 2023 at 01:14, Marc Millas wrote:
> But if I alter table to change a column that is a varchar 20 into a varchar 21
> postgres refuse saying that it cannot due to the return rule... using said
> column
>
> why ?? as the view is not a materialized object, the impact of the length of
On Thu, 9 Feb 2023 at 13:05, Martin L. Buchanan
wrote:
> For the common and simple cases of find this string anywhere in another
> string:
>
> str LIKE '%foo%'
>
> str ILIKE '%foo%'
>
> position('foo' in str) > 0
>
> Is Boyer-Moore string searching now used by any of these three?
We use a sort
On Thu, 9 Feb 2023 at 14:49, Tom Lane wrote:
>
> David Rowley writes:
> > Tom's argument seems to think it's impossible, so if you find that
> > it's definitely not impossible, then you can assume he's wrong about
> > that.
>
> My point was that
On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer wrote:
> OTOH it could also be argued that the optimizer should be able to
> perform the same simplifications as I did above and produce the same
> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
> as for WHERE (("id" > ?)) AND (("id" <=
On Mon, 20 Feb 2023 at 10:18, Tom Lane wrote:
> I don't see it at all. Comparing your two test queries on released
> branches, I see maybe 2x penalty for the ORDER BY NULL, not 30x.
> (In HEAD there's only about 13% penalty.) I wonder what PG version
> you are testing.
I suspect ed1a88dda would
On Mon, 20 Feb 2023 at 13:17, Tom Lane wrote:
> I suspect most of the remaining performance discrepancy is just triggered
> by having to pass the extra always-NULL column forward through the various
> plan steps. We could teach createplan.c to generate a WindowAgg plan node
> that omits the usele
On Tue, 21 Feb 2023 at 22:30, Arthur Ramsey wrote:
>
> I'm trying to figure out why a sequential scan is out performing. I've tried
> psql 13.7, psql14.6 and REINDEX. The REINDEX didn't help. This is on an RDS
> instance that's a db.m5.large (2 * vCPU, 8 GB memory) with 200 storage on io1
>
On Tue, 21 Feb 2023 at 08:42, Brad White wrote:
>
> I'm concerned that Autovacuum may not be running based on the results of this
> query.
>
> SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
> gives 211 rows like this...
> relname| last_vacuum | last_aut
On Wed, 22 Feb 2023 at 11:28, Brad White wrote:
> datname | stats_reset| now
> DB | 2023-02-17 14:28:27-06 | 2023-02-21 16:16:34-06
>
> I heard that the system was running slowly on Friday. They may have cycled
> the service in an attempt to resolve that.
You'll probably want to
On Fri, 3 Mar 2023 at 02:20, cen wrote:
> I understand that even though both colums are indexed, the indexes are
> completely different but the point is, how would one know in advance
> which one will be faster when designing the query?
Likely to be safe, you'd just include both. The problem is t
On Fri, 3 Mar 2023 at 22:35, cen wrote:
> Does equivalency only work for constants as in the sample you provided
> or will it also be found in b1."number" and t0."block_number" in my
> sample query?
It works for more than constants, but in this case, it's the presence
of the constant that would a
On Fri, 3 Mar 2023 at 23:17, Conner Bean wrote:
I wanted to avoid using a unique index since dropping them requires an
exclusive lock and cannot be done concurrently. My thought was to then
use a unique constraint, since I've read unofficial docs[0] that say
these can be dropped safely with no loc
On Fri, 3 Mar 2023 at 23:43, Simon Elbaz wrote:
> hydrodb=# SELECT c.oid::regclass as table_name,
>greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
> FROM pg_class c
> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
> WHERE c.relkind IN ('r', 'm') and c.relname='test';
> table_nam
On Sat, 4 Mar 2023 at 10:55, Ron wrote:
> On 3/3/23 04:54, David Rowley wrote:
> If you have a look at
> https://www.postgresql.org/docs/15/sql-dropindex.html check out the
> CONCURRENTLY option. That option allows an index to be dropped without
> blocking concurrent reads and writ
On Tue, 7 Mar 2023 at 12:40, Tom Lane wrote:
>
> Ben Clements writes:
> > As shown above, the following calculated column can bring in the city name,
> > even though the city name isn't in the GROUP BY:
> >max(city) keep (dense_rank first order by population desc)
>
> You haven't really expla
On Tue, 7 Mar 2023 at 16:11, David G. Johnston
wrote:
>
> On Mon, Mar 6, 2023 at 7:51 PM David Rowley wrote:
>> the transfn for bottom() would need to remember the city and the
>> population for the highest yet seen value of the 2nd arg.
>
>
> BOTTOM() remember
On Tue, 14 Mar 2023 at 21:01, Alban Hertroys wrote:
> > On 7 Mar 2023, at 4:11, David G. Johnston
> > wrote:
> > TOP(city, ROW(population, land_area)) ?
>
> What should be the expected behaviour on a tie though?
Undefined. Same as having an ORDER BY on a column that's not unique.
The sort impl
On Tue, 14 Mar 2023 at 16:07, Ben Clements wrote:
> Similar to your "TOP() and BOTTOM() aggregate" idea, you might find Erwin
> Brandstetter's solution using the LAST() aggregate function interesting:
> (https://dba.stackexchange.com/a/324646/100880)
Interesting. Just note that ORDER BY aggreg
On Thu, 30 Mar 2023 at 17:18, Tom Lane wrote:
>
> Julien Rouhaud writes:
> > brin indexes don't work the way you would hope for. the stored min/max
> > values per range guarantees that all values in the underlying relation
> > pages are contained in that range, but it doesn't mean that those min
On Thu, 30 Mar 2023 at 22:21, 任重 wrote:
> Here is anyway to Get dead tuples data from table when I had disable
> autovacuum?
> I need whole row data
https://www.postgresql.org/docs/current/pageinspect.html#id-1.11.7.34.5
David
On Tue, 11 Apr 2023 at 23:03, Christian Schröder
wrote:
> We have recently migrated from our old PostgreSQL 9.4 server to a PostgreSQL
> 15 server. Even though the new machine has more resources, we see a
> considerable decrease in the performance of some of our heavier queries, and
> I have no
On Wed, 12 Apr 2023 at 22:14, Alexander Saydakov wrote:
>
> I have a few questions regarding aggregate functions that would be parallel
> safe.
> 1. do the inputs of combinefunc always go through serialfunc-deserialfunc or
> they can come directly from sfunc (worker on the same machine, perhaps)
(Let's keep this on this list)
On Thu, 13 Apr 2023 at 12:08, Alexander Saydakov wrote:
>
> Yes, I am talking about a custom aggregate in C++ with internal state type.
> You did not really answer my first question. Does the state always go through
> serialfinc-deserialfunc before reaching a combi
On Thu, 13 Apr 2023 at 14:31, Alexander Saydakov wrote:
> 1. I wonder if there is a clean separation between the phases: once partial
> aggregation is done only combining is done after that (state transition
> function never receives results of combining).
Currently the transfn won't be called
#x27;d need to be careful never to use that in a view or
even a PREPAREd statement. Those abbreviations are evaluated when the
query is parsed. In those cases, you'd just get the results for
whatever day you did CREATE VIEW or PREPARE.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
ost a bit or increasing effective_cache_size. This will
lower the estimated cost of random IO for indexes scans.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
h some other random
name. If that patch gets in with a good amount of time to spare then
there's a decent chance we'd see a reindex concurrently command patch
appear soon after.
[1] https://commitfest.postgresql.org/20/1726/
--
David Rowley http://www.2ndQuadrant.com
clauses, LIKE or any other type of condition."
[1] https://www.postgresql.org/docs/10/planner-stats.html
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
executor. I didn't look
at why the cost is estimated to be slightly higher, but the planner
wouldn't consider rewriting the queries to one of the other cases
anyway, so it's likely not that critical that the costings are
slightly out from reality.
> where U.KEY_U in ({correlated
use that plan for all subsequent EXECUTEs for the session
until you DEALLOCATE the prepared query or DISCARD PLANS/ALL;
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Mon, 7 Jan 2019 at 21:40, Mitar wrote:
>
> On Mon, Jan 7, 2019 at 12:09 AM David Rowley
> wrote:
> > On Mon, 7 Jan 2019 at 18:54, Mitar wrote:
> > > If I have a PREPAREd query without parameters (static) and I EXECUTE
> > > it repeatedly in the same sessio
/augmenting-the-postgresql-planner-with-machine-learning/
Perhaps there are others that have worked on similar things, however,
I don't recall any conversations on these postgresql.org mailing lists
though. Maybe it's worth trying searching the archives?
--
David Rowley h
E
Time: 179.750 ms
# alter table rp attach partition rp1 for values from(1) to (201);
INFO: partition constraint for table "rp1" is implied by existing constraints
ALTER TABLE
Time: 4.969 ms
# alter table rp detach partition rp1;
# alter table rp1 drop constraint rp1_a_chk;
--
David
;t be fixed for 12. However, nothing is entirely certain that far
out.
It's probably best to reduce the number of partitions.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
s,
of course, not always possible, but useful to keep in mind when
deciding the order to have your columns in the table.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
roving TupleDesc so
it cached the first attnum without a NOT NULL constraint so that we
knew we could use the cached offset before that attnum even if the
tuple has NULLs later in the tuple. However, that puts a lot of
dependency on the NOT NULL constraint never being wrong.
--
David Rowley
efresh, otherwise, you might need to have a look at ungranted locks
in pg_locks and see if the number of locks spikes during the refresh.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
uildfarm member running the operating system
( https://buildfarm.postgresql.org/ ), to which there is currently
not. If you're interested enough and have the means, then you can
register and run one.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24
hink it is. Remember that variable length attributes can be
toasted and stored in the relation's toast table.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Mon, 8 Apr 2019 at 14:19, Igal Sapir wrote:
>
> On Sun, Apr 7, 2019 at 6:20 PM David Rowley
> wrote:
>>
>> On Mon, 8 Apr 2019 at 10:09, Igal Sapir wrote:
>> >
>> > I have a table for which pg_relation_size() shows only 31MB, but
>> >
rge. A normal VACUUM will turn space used
by dead tuples back into free space, so if done often enough there
won't be a need to vacuum full.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
ded or a bug?
Overlooked for PG11. You'll see them in PG12 per [1]. It's not really
possible to backpatch a fix for that since these views are created
during initdb.
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f94cec64476f2752e91b10d7928a2fcd105e9fc3
--
David Rowley
ion time.
The closest thing we have to that is run-time partition pruning in
PG11, but that can't help you either since the partition key cannot
contain values from other tables. You'd still need to normalise the
record table. With that and a partitioned table, there might be
further advanta
. If for example, you can get it to crash with LIMIT 1, can you
send us the psql output of \d public.aclappliedtopep and also some
anonymised version of the row it crashes on? (ensuring it still
crashes once anonymised). You may be able to narrow it down by "binary
searching" through various L
to assume that though.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
easurement_automatic"
> DETAIL: Specified lower bound (1) is greater than or equal to upper
> bound (1).
>
> OOPS, so lets cheat:
Or you could partition by LIST.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
.
If your go-live date is near the end of the year or beyond, then it
might be a good idea to start testing with PG12 right away. The
release date for that will likely be around the middle of October.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
. It'll be pretty much the same as
if you were querying a normal table.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
partition and ln
it back to its original location.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
nd up either NULL or be set to the value of the
column's DEFAULT clause, if it has one.
You might also want to look at INSERT ON CONFLICT DO NOTHING if you
want that INSERT to work in concurrent environments. Docs in
https://www.postgresql.org/docs/current/sql-insert.html
--
David Row
7;s in "s". "s" will be an empty relation if
"test" does not have any row matching WHERE area = 'test5'.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
urrent
sessions running this at the same time. You could protect against
that by doing some advisory locking (
https://www.postgresql.org/docs/11/explicit-locking.html#ADVISORY-LOCKS
), or taking an access exclusive lock on "test".
--
David Rowley http://www.2ndQu
up_ins is always correctly set.
> However, I've noted that this behavior applies up to february
Did you perhaps reset the stats or failover to a standby around Feb?
What does: select stats_Reset from pg_stat_database where datname =
current_database(); say?
--
David Rowley
f performing a
sub-query, LEFT JOIN to a query similar to your subquery but after
removing the std_stk_key = stock.stk_key condition and adding a GROUP
BY std_stk_key. However, that may perform worse if there are many more
std_stk_key groups than there are matching rows in stock.stk_key.
Experimentatio
efit to users in the same release.
You may already be aware, but another use case for such variable-width
identifiers was with indirect indexes as discussed in [1]
[1]
https://www.postgresql.org/message-id/20161018182843.xczrxsa2yd47pnru%40alvherre.pgsql
--
David Rowley
> in the updated record is not lower-cased.
You'll need a BEFORE trigger, not an AFTER trigger.
https://www.postgresql.org/docs/current/sql-createtrigger.html
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Wed, 31 May 2023 at 08:54, Ron wrote:
> https://www.postgresql.org/about/news/postgresql-16-beta-1-released-2643/
> says "PostgreSQL 16 can also improve the performance of concurrent bulk
> loading of data using COPY up to 300%."
>
> Since pg_dump & pg_restore use COPY (or something very simila
On Wed, 31 May 2023 at 13:13, Bruce Momjian wrote:
> There is no mention of concurrency being a requirement. Is it wrong? I
> think there was a question of whether you had to add _multiple_ blocks
> ot get a benefit, not if concurrency was needed. This email about the
> release notes didn't men
On Wed, 31 May 2023 at 14:11, Bruce Momjian wrote:
>
> On Wed, May 31, 2023 at 02:05:10PM +1200, David Rowley wrote:
> > "Relation extensions have been improved allowing faster bulk loading
> > of data using COPY. These improvements are more significant when
>
On Wed, 31 May 2023 at 16:26, Lian Jiang wrote:
> I am using psql to periodically dump the postgres tables into json files
> which are imported into snowflake. For large tables (e.g. 70M rows), it takes
> hours for psql to complete. Using spark to read the postgres table seems not
> to work as
On Sat, 3 Jun 2023 at 00:14, Jonathan S. Katz wrote:
> Typically once a release announcement is out, we'll only edit it if it's
> inaccurate. I don't think the statement in the release announcement is
> inaccurate, as it specifies that concurrent bulk loading is faster.
Understood. I had thought
On Mon, 5 Jun 2023 at 18:56, gzh wrote:
> I'm running into some performance issues with my SQL query.
> The following SQL query is taking a long time to execute.
> -> Hash Join (cost=253388.44..394112.07 rows=1 width=56) (actual
> time=1197.484..2954.084 rows=330111 loops=1)
>
On Wed, 21 Jun 2023 at 07:42, Marc Millas wrote:
> But if I do the same with clause one OR clause 2, I have to kill the request
> after an hour, seeing the filesystem showing more than 140 Mb of increased
> usage.
> So, before providing the 3 explain plans (I must anonymize everything, so
> s
On Wed, 21 Jun 2023 at 08:34, Marc Millas wrote:
>
> On Tue, Jun 20, 2023 at 10:14 PM David Rowley wrote:
>>
>> On Wed, 21 Jun 2023 at 07:42, Marc Millas wrote:
>> > But if I do the same with clause one OR clause 2, I have to kill the
>> > request after an
On Wed, 21 Jun 2023 at 10:26, Marc Millas wrote:
> link to the plan with both clauses ORed (the one not finishing)
> https://explain.depesz.com/s/jHO2
I'd go with the UNION or UNION ALL idea I mentioned earlier.
David
On Mon, 26 Jun 2023 at 03:02, Marc Millas wrote:
> When I ask this list, David Rowley suggest to rewrite the SQL, replacing the
> OR by a union.
>
> Fine, this do work, even if a bit complex as the original SQL was a set of
> intricate joins.
>
>
> So, either this behavi
On Wed, 14 Jun 2023 at 07:28, Patrick O'Toole wrote:
> Maybe we are barking up the wrong tree with the previous questions. Are there
> other configuration parameters we should consider first to improve
> performance in situations like the one illustrated?
random_page_cost and effective_cache_si
On Fri, 30 Jun 2023 at 00:42, Umut TEKİN wrote:
> @Marc, I think there is no problem.Even though it says it is filtered by
> ladate, it is not. Because of the partition.
> As you can see for each index scan it uses a different partition and those
> partition boundaries are already specified logi
On Tue, 18 Jul 2023 at 06:19, basti wrote:
>
> Thanks a lot tomas, i will try it.
>
> I have find out that there is a 'aggregation' function in the frontend.
> But this is MySQL specific and I have no idea the transform it to postgres.
>
> It looks like:
> 'REPLACE INTO aggregate (channel_id, type
On Mon, 17 Jul 2023 at 21:13, basti wrote:
> volkszaehler=# explain analyze SELECT COUNT(DISTINCT DATE_TRUNC('day',
> TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data
> WHERE channel_id = 5 AND timestamp >= 0;
Alternatively, you could express this as:
SELECT COUNT(*) FROM (SE
On Wed, 19 Jul 2023 at 07:41, Rob Sargent wrote:
> You might consider deleting portions of the table in separate (consecutive)
> batches (maybe 5% per delete). And then truncate table is not logged so that
> might be an alternative.
Can you explain why this would be a useful thing to do?
It s
On Thu, 20 Jul 2023 at 23:36, gzh wrote:
>
>
> Thank you very much for taking the time to reply to my question.
>
>
> Sorry, I provided incorrect information.
>
> The index also does not work in the following query statement.
>
>
> > select COUNT(ET_CD)
> > from TBL_SHA
> > WHERE MS_CD = '009'
> >
On Fri, 21 Jul 2023 at 13:44, gzh wrote:
>
> The definitions of the columns used in SQL are as follows.
>
> TBL_SHA
> ms_cd character(6) NOT NULL -- PRIMARY KEY
> et_cd character(8)
> etrys character(8)
> explain analyze
> select COUNT(ET_CD)
> from TBL_SHA
> WHERE TBL_SHA.MS_CD = 'MLD009'
On Wed, 26 Jul 2023 at 19:46, gzh wrote:
> QUERY PLAN (enable_seqscan=on)
> Execution Time: 167183.133 ms
> QUERY PLAN (enable_seqscan=off)
> Execution Time: 22320.153 ms
effective_cache_size and random_page_cost are the settings you should
be adjusting to coax the planner into using the index
On Fri, 11 Aug 2023 at 13:54, Ron wrote:
> Wouldn't IO contention make for additive timings instead of exponential?
No, not necessarily. Imagine one query running that's doing a
parameterised nested loop join resulting in the index on the inner
side being descended several, say, million times. L
On Mon, 14 Aug 2023 at 11:14, Marc Millas wrote:
> that's exactly my question.
> does the analyze buffers data, generated when track_io_timing is on, keep
> track of multiple reloads of the same data while executing one operation ?
Yes, the timing for reads will include the time it took to fetc
On Thu, 31 Aug 2023 at 07:55, Dimitrios Apostolou wrote:
> I'd appreciate help on whether it's a real issue, and if it's unknown I
> can forward this to the psql-bugs mailing list. I'd also appreciate any
> critique on the clarity of my description and on my schema and queries,
> since I'm new to
On Sun, 3 Sept 2023 at 23:52, veem v wrote:
> Additionally, is it true that optimizer will also get fooled on getting the
> math correct during cardinality estimates, as because there is a big
> difference between , comparing or substracting, two date values VS two number
> values. And storing
On Thu, 7 Sept 2023 at 11:14, Dirschel, Steve
wrote:
> select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_ from
> hist28.history_event_display_timestamp_20230301 historyeve0_ where
> historyeve0_.IS_DELETED=0
> history_event_sid | character varying(32) | | not
> nu
On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote:
> It seems likely that the problem here is that some of the predicates
> appear as so-called "Filter:" conditions, as opposed to true index
> quals.
hmm, if that were true we'd see "Rows Removed by Filter" in the
explain analyze.
I think all t
On Fri, 15 Sept 2023 at 01:36, Ryo Yamaji (Fujitsu)
wrote:
> Question:
> I am assuming that the version upgrade has changed the behavior of the
> planner. Is this correct?
It's possible. 9.2 was a long time ago. It would be quite a bit of
work to determine if this is the case. You could perhap
On Fri, 15 Sept 2023 at 21:13, Ryo Yamaji (Fujitsu)
wrote:
> The following example shows a table with 1 million tuples:
> * The cost of using PK was higher than the cost of using user index.
> * It was faster to use PK.
>
> Index Scan using tbl_ix1 on tbl (cost=0.43..0.67 rows=1 width=61) (actua
On Tue, 3 Oct 2023 at 21:07, wrote:
> P.S.: The only serious discussion I was able to find about it was from 2015
> here, everyone basically stating that the improvement would be useful.
> https://postgrespro.com/list/thread-id/1880012
There is some active discussion and a patch which aims to i
On Tue, 3 Oct 2023 at 22:03, wrote:
> So one last question, should I expect the patch to land in version 17 only or
> is there chance that it will also be in lower versions right away?
It wouldn't ever be put into anything earlier than 17.
David
On Tue, 24 Oct 2023 at 10:39, Torsten Förtsch wrote:
> Then I added this constraint to the small table:
>
> ALTER TABLE original_small_table
> ADD CONSTRAINT partition_boundaries
> CHECK((false, '-infinity')<=(is_sold, purchase_time)
> AND (is_sold, purchase_time)<(false, 'infinity'))
> NOT VA
On Wed, 1 Nov 2023 at 11:41, Dimitrios Apostolou wrote:
> I'm wondering why the planner doesn't see that the left table is very small
> and follow a different path.
> From an abstract computer science POV, I would
>
> 1. sort the left table (the right one is already indexed)
> 2. "merge" the two
On Wed, 29 Nov 2023 at 11:23, Owen Nelson wrote:
> "message_payload_not_null_pidx" btree (expiration) WHERE payload IS NOT
> NULL
> I periodically run a query like this:
> ```
> UPDATE message SET payload = NULL WHERE id IN (
> Update on message (cost=1773.41..44611.36 rows=5000 width=283)
On Mon, 4 Dec 2023 at 15:08, senor wrote:
> PG version 11.4
You're missing out on over 3 years of bug fixes running that version.
I see you're planning an upgrade to v15. You should update to 11.22 in
the meantime. That's the latest and final version of v11.
> If I start a vacuum on this table w
101 - 200 of 309 matches
Mail list logo
