On Mon, 29 Jan 2024 at 07:37, Ron Johnson wrote:
> 08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081
> 14.10 159.354 155.111 155.111 162.797 158.157 86.72%
>
Your speedup per cent calculation undersells PG14 by quite a bit. I'd call
that an increase of ~639% rather than 86.72%.
I thi
On Tue, 6 Feb 2024 at 01:23, Sean v wrote:
> SELECT "orders".*
> FROM "orders"
> WHERE (user_id IN ?, ?, ?)
> ORDER BY "orders"."created_at" LIMIT 50
>
> I have two indexes - `(user_id)` and `(user_id, created_at)`. Only the first
> index is ever used with this query.
> I imagined that it would
On Mon, 19 Feb 2024 at 16:32, Darryl Green wrote:
> 2) It would be nice to be able to specify the id as pk on the table being
> partitioned (as it was in the non-partitioned definition of the table) once
> to document and enforce that the partitions simply inherit the id pk. This
> would seem o
On Mon, 19 Feb 2024 at 22:07, Darryl Green wrote:
>
> On Mon, 19 Feb 2024 at 14:23, David Rowley wrote:
> >
> > On Mon, 19 Feb 2024 at 16:32, Darryl Green wrote:
> > > 2) It would be nice to be able to specify the id as pk on the table being
> > > partition
On Tue, 27 Feb 2024 at 23:23, Rafsun Masud Prince
wrote:
> I am looking for a combination of the 'off' and 'regress' state, which is:
> use parallel if improves performance + suppress context line (if
> parallel is used)
>
> Our project, Apache AGE, has a regression test for cypher MATCH queri
On Fri, 15 Mar 2024 at 07:13, David G. Johnston
wrote:
> On Thu, Mar 14, 2024, 11:08 Thiemo Kellner
> wrote:
>>
>> Thanks for the enlightenment. A pity. I suppose, there is no working
>> around this?
>
> Write a script to do the query in a loop on all databases - that catalog is
> global.
Yeah
On Fri, 15 Mar 2024 at 08:01, hassan rafi wrote:
> We have migrated to postgres version 16.1, but still due to very high update
> activity on our DB, we are seeing elevated response times, though now the
> planning time is less.
>Buffers: shared hit=33359 read=6590 dirtied=9379
> Executio
On Fri, 12 Apr 2024 at 22:33, Thierry Henrio wrote:
> Here is (B) execution plan:
To be able to determine if a plan is good or bad, we'd really need to
see the EXPLAIN (ANALYZE) output rather than just the EXPLAIN ouput.
To save a possible roundtrip;
SET track_io_timing = ON;
EXPLAIN (ANALYZE,
On Fri, 12 Apr 2024 at 23:27, Thierry Henrio wrote:
> JIT:
>Functions: 36
>Options: Inlining true, Optimization true, Expressions true, Deforming true
>Timing: Generation 1.949 ms, Inlining 28.891 ms, Optimization 207.481 ms,
> Emission 134.907 ms, Total 373.228 ms
> Execution Time:
On Mon, 15 Apr 2024 at 02:25, jack wrote:
> Then I tried to update the data in blocks of 100,000 records and it crashed 4
> times on 4 different blocks.
> So I updated the first crashed block down to the a block of 10 records, until
> it crashed.
> Then I updated each of the 10 records individua
On Tue, 16 Apr 2024 at 23:00, Sasmit Utkarsh wrote:
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'),
> coalesce(MAX(id), 1)) from mqa_flfo_cstr;
> ERROR: permission denied for sequence mqa_flfo_cstr_id_seq
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', '
On Fri, 19 Apr 2024 at 05:48, Michael Corey
wrote:
> ALTER TABLE ONLY par_log_file
> ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id);
> ALTER TABLE par_log_definition
> ADD CONSTRAINT pld_fk FOREIGN KEY (par_file_id) REFERENCES
> par_log_file(par_file_id);
> I receive the following erro
On Mon, 22 Apr 2024 at 12:16, Ron Johnson wrote:
>
> On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote:
>>
>> Ron Johnson writes:
>> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does
>> > the same thing (similarly doubling disk space), and apparently runs just as
>> > fast
On Tue, 23 Apr 2024 at 07:01, Олександр Янін
wrote:
> Try setting enable_memoize to off.
> Our practice has shown that enabling this parameter by default often resulted
> in less than optimal query plans in the cache.
It would be good to see a thread opened with details on this. I
understand in
On Sat, 11 May 2024 at 13:11, Dimitrios Apostolou wrote:
> Indeed that's an awful estimate, the table has more than 1M of unique
> values in that column. Looking into pg_stat_user_tables, I can't see the
> partitions having been vacuum'd or analyzed at all. I think they should
> have been auto-ana
On Sat, 11 May 2024 at 13:33, Tom Lane wrote:
> I do kind of wonder why it's producing both a hashagg and a Unique
> step --- seems like it should do one or the other.
It still needs to make the duplicate groups from parallel workers unique.
David
On Tue, 14 May 2024 at 00:28, Dimitrios Apostolou wrote:
>
> On Sat, 11 May 2024, David Rowley wrote:
>
> > On Sat, 11 May 2024 at 13:33, Tom Lane wrote:
> >> I do kind of wonder why it's producing both a hashagg and a Unique
> >> step --- seems like it
On Tue, 14 May 2024 at 00:41, Dimitrios Apostolou wrote:
>
> On Sat, 11 May 2024, David Rowley wrote:
> > It will. It's just that Sorting requires fetching everything from its
> > subnode.
>
> Isn't it plain wrong to have a sort step in the plan than? The differ
On Tue, 14 May 2024 at 00:46, Dimitrios Apostolou wrote:
>
> On Mon, 13 May 2024, Dimitrios Apostolou wrote:
>
> > On Sat, 11 May 2024, David Rowley wrote:
> >> If you look at [1], it says "Tuples changed in partitions and
> >> inheritance children do n
On Tue, 14 May 2024 at 01:52, Dimitrios Apostolou wrote:
>
> On Tue, 14 May 2024, David Rowley wrote:
> > The query does contain an ORDER BY, so if the index is not chosen to
> > provide pre-sorted input, then something has to put the results in the
> > correct order bef
On Tue, 14 May 2024 at 02:07, Dimitrios Apostolou wrote:
>
> On Tue, 14 May 2024, David Rowley wrote:
> > Parallel Append can also run in a way that the Append child nodes will
> > only get 1 worker each.
>
> How can I tell which case it is, from the EXPLAIN output (for ex
On Wed, 15 May 2024 at 21:08, Sašo Gantar wrote:
> this query takes more than 8 seconds,
> if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s
Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't
very useful as there's no way to tell if the planner's estimates
On Mon, 20 May 2024 at 22:32, milist ujang wrote:
>
> postgres 16.1; rocky 9.3
>
> when connect to database postgres this query is OK, but run on user database,
> got segmentation fault.
I tried your query on 16.1 and I'm unable to reproduce the crash.
Are you able to recreate this on a freshly
On Mon, 20 May 2024 at 23:09, Sašo Gantar wrote:
> what helps is
> SET enable_nestloop = off;
> query takes less then 2seconds
> but it's probably not a good idea to change this flag
Looks like it's slow due to a bad selectivity estimate on the join
between pgn and pgc. This results in:
-> Nes
On Tue, 21 May 2024 at 23:14, Laurenz Albe wrote:
> We still don't know the query.
hmm, it was posted on this thread:
https://postgr.es/m/CAGB0_6600w5C=hvhgfmwcqo9bcwcg+3s0pxxuoqv48nlqtp...@mail.gmail.com
David
On Wed, 29 May 2024 at 12:53, Tom Lane wrote:
> It would be interesting to see a non-artificial example that took
> into account when the last auto-vacuum and auto-analyze really
> happened, so we could see if there's any less-fragile way of
> dealing with this situation.
I think we need to find
On Fri, 31 May 2024 at 09:28, Wong, Kam Fook (TR Technology)
wrote:
> Is there a way to delete a specific column level stats/histogram. The
> following approach does not work.
>
> alter table abc alter column bg_org_partner set statistics 0;
> analyze abc;
You'd have to:
DELETE FROM pg_stati
(please keep communication on the list)
On Fri, 31 May 2024 at 13:43, Wong, Kam Fook (TR Technology)
wrote:
>
> Silly question why did I run into this problem below? Will the autovacuum
> analyze abc reset it back which I don't want it to.
>
> DELETE FROM pg_statistic WHERE starelid = 'abc'::re
On Fri, 28 Jun 2024, 3:20 am aghart...@gmail.com,
wrote:
>
> Now the query:
> explain (verbose, buffers, analyze)
> with last_table_ids as materialized(
>select xx from (
>select LAST_VALUE(pk_id) over (partition by integer_field_2 order by
> datetime_field_1 RANGE BETWEEN UNBOUNDED PRECE
On Fri, 5 Jul 2024 at 17:07, Lok P wrote:
> Also I understand the technical limitation of the max number of columns per
> table is ~1600. But should you advise to restrict/stop us to some low number
> long before reaching that limit , such that we will not face any anomalies
> when we grow in f
On Fri, 5 Jul 2024 at 19:53, Lok P wrote:
> As David suggested it breaks if a row exceeds the 8k limit I. E a single page
> size , will that still holds true if we have a column with JSON in it?
You wouldn't be at risk of the same tuple length problem if you
reduced the column count and stored t
On Fri, 5 Jul 2024 at 20:53, Lok P wrote:
> However out of curiosity, if the roasted/compressed component or column which
> is JSON itself goes beyond 8k post compression, will it break then?
No. The size limit of a varlena field such as TEXT, JSON or JSONB is
1GB. See "field size" in [1].
Ple
On Sat, 6 Jul 2024 at 03:24, Matt Hughes wrote:
> -- 4. uses all partitions; should exclude event_closed_y2024_m02
> explain select * from event
> where
> cleared is false OR
> (cleared is true and date_raised > '2024-01-01' AND date_raised <
> '2024-01-02');
>
On Thu, 11 Jul 2024 at 13:19, Dimitrios Apostolou wrote:
> I have a table with 1000 partitions on PostgreSQL 16.
> I notice that a fairly complicated query of the form:
>
> SELECT ... GROUP BY ... LIMIT ...
>
> causes the postgres backend process to grow insanely very fast, and the
> kernel OOM ki
On Fri, 12 Jul 2024 at 02:08, Dimitrios Apostolou wrote:
> I can't help but see this as a bug. I see many issues:
>
> * postgres is not reading from partitions in parallel, but one after the
>other. It shouldn't need all this memory simultaneously.
I don't know for Hash Aggregate, but for nod
On Fri, 19 Jul 2024 at 00:31, Laurenz Albe wrote:
> Perhaps there could be a way to report misleading, bad content and a policy
> that says
> that you can be banned if you repeatedly write grossly misleading and
> counterfactual
> content. Stuff like "to improve performance, set fast_mode = on
On Fri, 26 Jul 2024 at 19:55, Francisco Olarte wrote:
> " -> Index Scan using
> ""cl_student_semester_subject_IX3"" on cl_student_semester_subject p
> (cost=0.55..8.57 rows=1 width=60) (actual time=0.033..55.702
> rows=41764 loops=1)"
> "Index Cond: (((companycode
On Mon, 5 Aug 2024 at 23:41, Dominique Devienne wrote:
> Is that now required? Any documentation / release notes regarding
> such a change?
Yes. The release notes mention:
"Remove the Microsoft Visual Studio-specific PostgreSQL build option
(Michael Paquier)
Meson is now the only available met
On Wed, 7 Aug 2024 at 16:44, Christophe Pettus wrote:
> Child partitions should be autovacuumed and autoanalyzed just like any other
> table; they are not prohibited from autovacuum in any way by default. It's
> probably a good idea to investigate why they are not being picked up by
> autovacu
On Wed, 7 Aug 2024 at 19:20, Michael Harris wrote:
> I found that running an ANALYZE specifying only those 4 columns only took
> 5 minutes, compared to the 30 minutes for the whole table.
>
> That was a bit of a surprise as I imagined actually reading the table would
> take
> most of the time and
On Fri, 9 Aug 2024 at 02:12, Christophe Pettus wrote:
> VACUUM FULL takes an exclusive lock on the table that it is operating on.
> It's possible that a connection becomes blocked on that exclusive lock
> waiting for the VACUUM FULL to finish, the application sees the connection
> stopped and
On Mon, 19 Aug 2024 at 19:48, sud wrote:
> In a version 15.4 postgres database, Is it possible that , if we have two big
> range partition tables with foreign key relationships between them, insert
> into the child table can cause slowness if we don't have foreign key index
> present in the chi
On Tue, 20 Aug 2024 at 19:09, sud wrote:
> However, my initial understanding of "having the FK index will improve the
> insert performance in the child table" is not accurate it seems. Rather as
> you mentioned it may negatively impact the loading/insert performance because
> it has to now upda
On Tue, 27 Aug 2024 at 13:40, Tom Lane wrote:
> Yeah, it looks like that condition on "table_name" is not getting
> pushed down to the scan level anymore. I'm not sure why not,
> but will look closer tomorrow.
I was looking for the offending commit as at first I thought it might
be related to Me
On Tue, 27 Aug 2024 at 18:00, Justin Clift wrote:
> As a general thought, seeing that this might be an actual problem
> should some kind of automated testing be added that checks for
> performance regressions like this?
We normally try to catch these sorts of things with regression tests.
Of cour
On Tue, 27 Aug 2024 at 14:03, Tom Lane wrote:
> Yeah, I got that same result by bisecting. It seems like it's
> somehow related to the cast to information_schema.sql_identifier:
> we are able to get rid of that normally but seem to fail to do so
> in this query.
In case it saves you a bit of tim
On Wed, 28 Aug 2024 at 18:59, Justin Clift wrote:
> Any idea who normally does those, and if it would be reasonable to add
> test(s) for the internal information tables?
These tend to get added along with features and along with of bug
fixes. I imagine any tests for the information_schema views
On Fri, 30 Aug 2024 at 11:18, Rumpi Gravenstein wrote:
>
> Which returns:
> logical_partition_keyusage_texttxtrpo_txtindxmx_indxprvnxtnxt2prv2prv3
> "TEST_DATA"
> "F(T61)(EXPORT)" "F(T61)(EXPORT)" "F" "1" "7" *"F"* "(" "(" "[NULL]"
> "[NULL]"
> "TEST_DATA"
> "F(T61)(EXPORT)" "F(T61)(EXPORT)" "("
On Fri, 30 Aug 2024 at 23:45, Rumpi Gravenstein wrote:
> Here's the output of the script you requested.
>
These results look correct to me. Not the same as the ones you originally
reported.
David
On Fri, 30 Aug 2024 at 23:36, James Brown wrote:
> I have two tables: one named taxpayers which has a goodish number of columns
> an an integer PK id, and one named insights, which has a taxpayer_id foreign
> key to taxpayers, a year, and (again) a lot of other columns. There's an
> index on in
On Fri, 6 Sept 2024 at 23:05, Xavier Solomon
wrote:
> > create table a(a_id int primary key generated always as identity, a_data
> > text);
> > create table b(b_id int primary key generated always as identity, a_id int
> > not null references a(a_id), b_data text);
>
> Then the query
> > explain
On Wed, 11 Sept 2024 at 10:57, Laurenz Albe wrote:
> You should specify the upper bound as ('2023-12-31 23:59:59.99',
> MAXVALUE).
Or maybe do multi-level partitioning.
(it seems strange to always have MINVALUE and MAXVALUE as the range.
I'm guessing that was just an example.)
David
On Sat, 24 Apr 2021 at 03:44, Ron wrote:
>
> On 4/23/21 10:39 AM, Luca Ferrari wrote:
> > Hi all,
> > this could be trivial, but I would like an explaination: if I turn off
> > sequential scans on a table without indexes, the same access plan is
> > increased by a 100 factor. I suspect thi
On Tue, 27 Apr 2021 at 21:15, Matteo Bonardi wrote:
> Explain plan: https://explain.depesz.com/s/BXGT
>
> Usually I'm looking for "external merge Disk" to see temp files usage but, in
> this case, the only reference to that is 299,368kB in the last but one node
> of explain.
> Can anyone help me
On Thu, 29 Apr 2021 at 21:02, Ayub M wrote:
>
> In the below execution plan, the index scan on five_lima (table has 900m
> records) is where it's spending most of its time. I want to bring down the
> runtime to a few seconds, how do I optimize it? Tried forcing seq scan and
> ran vacuum/analyze
On Tue, 11 May 2021 at 11:34, Jonathan Chen wrote:
> PG13 LIMIT 1 : https://explain.depesz.com/s/GFki
> PG13 no LIMIT: https://explain.depesz.com/s/U4vR
> PG12 LIMIT 1 : https://explain.depesz.com/s/JAp4
The difference is coming from the fact that PostgreSQL 13 has
incremental sort and can use th
On Wed, 12 May 2021 at 06:33, Vijaykumar Jain
wrote:
>
> ok i think i just may be there is very less data , hence no index scan, no
> pruning.
>
> when i try to force seq_scan off,
>
> postgres=# set enable_seqscan TO off;
> SET
> postgres=# explain analyze select * from tprt where tprt.col1 in (
On Sun, 16 May 2021, 12:15 am Luca Ferrari, wrote:
> So far so good, but if I disable seqscan I would expect the planner to
> choose the primary key index, because that "should be" the preferred
> way to access the table.
> On the other hand, the planner enables JIT machinery and executes
> again
On Sun, 16 May 2021 at 13:00, David G. Johnston
wrote:
>
> On Saturday, May 15, 2021, Venkata B Nagothi wrote:
>> Below is the query generating the error :
>>
>> STATEMENT: SELECT c.relname AS table_name, c.relhaspkey AS has_primary_key
>> FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n W
On Mon, 24 May 2021 at 20:53, Andreas Joseph Krogh wrote:
>
> Hi, I have this, for historical reasons:
>
> CREATE AGGREGATE array_aggarray(anyarray) (
> SFUNC = array_cat, STYPE = anyarray);
>
>
> ...which now breaks in pg-14b1:
>
> ERROR: function array_cat(anyarray, anyarray) does not exist
On Fri, 4 Jun 2021 at 22:59, Pól Ua Laoínecháin wrote:
> Now, we've gone from 1,160,000 to 39,830 rows (progress? :-) ) and a
> cost of ~ 1M (compared with 168k for the first query).
The estimates are not that meaningful due to a lack of table
statistics on the "test" table. If you run ANALYZE o
On Sat, 5 Jun 2021 at 00:55, Laurenz Albe wrote:
>
> On Sat, 2021-06-05 at 00:12 +1200, David Rowley wrote:
> > As for the call to generate_series, you're not likely to ever get any
> > great estimation from that. The number of rows returned by a call to
> > that
On Wed, 16 Jun 2021 at 18:29, Atul Kumar wrote:
> QUERY PLAN
> Limit (cost=0.43..5529.03 rows=10 width=37) (actual
> time=0.974..12911.087 rows=10 loops=1)
> Output: items._id
> Buffers: shared hit=4838 read=3701
> -> Subquery Scan on items (cost=0.43..1622646.30 rows=2935
> width=37) (ac
On Mon, 21 Jun 2021 at 21:59, Yu Watanabe wrote:
> I have tried setting idle_in_transaction_session_timeout , however, this
> does not delete idle sessions.
>
> Would there be any expiry settings to delete client idle sessions
> automatically ?
There is idle_session_timeout in PostgreSQL 14,
On Tue, 22 Jun 2021 at 19:06, Niels Jespersen wrote:
> I think I could achieve some speedup by parallelizing further, distributing
> the cpu-work among additional workers. But, how do I achive that best?
You'll want to ensure max_parallel_workers_per_gather is set high
enough and you have max_pa
On Thu, 24 Jun 2021 at 10:55, Alvaro Herrera wrote:
>
> On 2021-Jun-23, Rumpi Gravenstein wrote:
>
> > As a best practice is it better to create the primary key starting or
> > ending with the partition column?
>
> It is not relevant from the partitioning point of view. Other factors
> can be use
On Thu, 24 Jun 2021 at 11:56, Tom Lane wrote:
>
> David Rowley writes:
> > On Thu, 24 Jun 2021 at 10:55, Alvaro Herrera
> > wrote:
> >> It is not relevant from the partitioning point of view. Other factors
> >> can be used to decide the column order.
>
On Thu, 1 Jul 2021 at 22:06, wrote:
> I have several tables with generated columns. If I restore the plain dumped
> data (insert statements from pg_dump) I'll get the error message "Column xyz
> is a generated column.". The exception is understandably, no question (and is
> well documented). In
On Tue, 6 Jul 2021 at 21:35, Ron wrote:
> The legacy RDBMS which I used to manage has a tool for analyzing (not in the
> Postgresql meaning of the word) an index, and displaying a histogram of how
> many layers deep various parts of an index are. Using that histogram, you
> can tell whether or
On Mon, 12 Jul 2021 at 11:52, David G. Johnston
wrote:
> A post over in Reddit had an expression form I've never seen before:
>
> select (array[1,2,3,4]::integer[])[generate_series(1, 3)];
> Looking at subscripting in the SQL syntax this example doesn't seem to be
> documented.
>
> https://www.p
On Mon, 12 Jul 2021 at 12:58, David G. Johnston
wrote:
>
> On Sun, Jul 11, 2021 at 5:43 PM David Rowley wrote:
>>
>> Isn't this implied by "Each subscript is itself an expression"?
>> There's nothing special here with the SRF. That just produces 3 row
On Sun, 15 Aug 2021 at 22:15, Michael Goldberg wrote:
>
>
> On Sun, Aug 15, 2021 at 12:49 PM otar shavadze wrote:
>>
>> How measure table total pages (block) count? would be this correct way? :
>>
>> SELECT pg_table_size('my_table'::regclass) /
>> current_setting('block_size')::BIGINT;
>
> Did
On Thu, 23 Sept 2021 at 08:21, Michael Lewis wrote:
> select station, array_agg(distinct(channel)) as channels
> FROM(
> SELECT station,channel FROM data GROUP BY station,channel
> ) AS sub
> group by station;
Since the subquery is grouping by station, channel, then there's no
need for the DISTIN
On Thu, 23 Sept 2021 at 08:27, David G. Johnston
wrote:
>
> On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis wrote:
>> If there is correlation between station & channel, then you might look at
>> creating a multivariate statistics object and analyzing the table so the
>> planner can make better ch
On Thu, 23 Sept 2021 at 13:28, David G. Johnston
wrote:
> Agreed, though if the query author needs to do that here we’ve violated the
> spirit of the declarative SQL language. At first blush nothing about the
> original query seems like it should be preventing parallelism. Each worker
> build
On Thu, 23 Sept 2021 at 13:21, Israel Brewster wrote:
> Ah, yes indeed. That version runs in about 30 seconds rather than 5 minutes!
> See the explain analyze output here: https://explain.depesz.com/s/L5Bf It
> looks more complicated, but being able to run parallel definitely makes a
> differen
On Thu, 25 Nov 2021 at 08:59, Godfrin, Philippe E
wrote:
> Hi Tom. Good point about the index paging out of the buffer. I did that and
> no change. I do have the shared buffers at 40GB, so there’s a good bit there,
> but I also did all those things on the page you referred, except for using
> c
to master as I rebased it just several months ago.
I've just not yet come up with any bright ideas on how to solve the
foreign key trigger timing problem.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 1 December 2017 at 15:30, Simon Riggs wrote:
> On 1 December 2017 at 12:20, David Rowley
> wrote:
>> The large hurdle which a good workaround was never really found for
>> was the fact that foreign key triggers only update the referenced rows
>> at the end of the st
eign keys do
not update the referenced rows right away means that there is a non-zero
window of time that the constraint is violated, therefore, if a query which
is run, or is running during that time, we could return the incorrect
results if we were to remove an INNER JOIN during the planning of tha
0
2 | 0
(2 rows)
when we should get:
# select *,update_t1(id, id + 2) from t1 order by id;
id | update_t1
+---
1 | 1
2 | 2
(2 rows)
Yes, this is an ANTI-JOIN and not an INNER JOIN as per $subject, but
the same applies. This is just a more simple exampl
5tD=vniepAv0pU5m=q=foqzcodmheei7oq...@mail.gmail.com#CAApHDvpCBEfuc5tD=vniepAv0pU5m=q=foqzcodmheei7oq...@mail.gmail.com
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
option than
doing the single UPDATE join method you mentioned.
It does sound like something you could take offline and benchmark if
performance is that critical. It's not really possible for us to tell
which is faster without seeing the schema, UPDATE statements and query
plans chosen.
--
Davi
ooks like some sort of Oracle syntax to get the nextval
from a sequence. In PostgreSQL you'd do nextval(''); Assuming you
have a sequence named ""
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
L that you're running it on is also a good
thing to share.
Details of the indexes which you've defined on the tables are also
useful information. It would especially be good to know if you've
added an index on product (establishment_id), for example.
--
David Rowley
ed every column in the table and the WAL volume was more than the
network's bandwidth could handle over the holiday period. (Note: I
know nothing about air traffic control, but it does seem like
something you'd want to make stable systems for, games on the
internet, probably less so...).
On Tue, 15 Oct 2024 at 06:59, Wong, Kam Fook (TR Technology)
wrote:
> I am trying to copy a table (Postgres) that is close to 1 billion rows into a
> Partition table (Postgres) within the same DB. What is the fastest way to
> copy the data? This table has 37 columns where some of which are te
On Sat, 12 Oct 2024 at 02:28, Durgamahesh Manne
wrote:
> Second column of composite index not in use effectively with index scan when
> using second column at where clause
>
> I have composite index on (placedon,id) of test
> When quering select * from test where id = '4234';
> Value of id chan
On Thu, 17 Oct 2024 at 01:26, Richards, Nina wrote:
> Even though we know there was no calendar at that time, it would make our
> work much easier if we could use data before 4713 BC in the same way.
> Especially for statistical analyses and scientific dating methods (14C,
> dendrochronology),
On Tue, 29 Oct 2024 at 22:01, Reftel, Bengt Erik Magnus
wrote:
> Thank you! I´ll try to make a change to the documentation to clarify that.
Perhaps a new row after the "relation size" row with "partitioned
table" and upper limit of "32TB * number of partitions" with a comment
"individual partitio
On Mon, 4 Nov 2024 at 22:52, ma lz wrote:
>
> some sql like ' select a from t1 intersect select a from t1 '
>
> if t1 has large number rows but has few distinct rows
>
> select distinct a from t1 intersect select distinct a from t1;— this is
> faster than origin sql
>
> can postgres do this o
On Wed, 6 Nov 2024 at 12:09, Tom Lane wrote:
> Of course, I might be overestimating the performance benefit we'd get.
> But I'm tempted to give it a try.
I'm glad. I'm curious to see if you're right about the projection
overhead of the flags. If you're right, it seems like a not too
difficult opt
On Tue, 5 Nov 2024 at 04:18, Tom Lane wrote:
> A different idea that occurred to me while looking at this is:
> why have we got all this machinery to add and check a flag
> column, rather than arranging things so that the two input
> relations are "outer" and "inner" children of the SetOp?
I've n
On Mon, 10 Feb 2025 at 17:34, Mukesh Tanuku wrote:
> 2025-02-10 04:22:00.823 GMT [2468337] ERROR: trailing junk after numeric
> literal at or near "5m" at character 28
> 2025-02-10 04:22:00.823 GMT [2468337] STATEMENT: SET idle_session_timeout =
> 5min
> 2025-02-10 04:22:03.487 GMT [2468342] E
On Tue, 14 Jan 2025 at 03:45, Eşref Halıcıoğlu
wrote:
> I do not fully understand the logic of this issue. I would be very grateful
> if you can share information on the subject.
>
> The query plan is as follows.
>
> Update on "test_table1" tt1 (cost=0.13..159112.84 rows=0 width=0)
> Update on
On Sat, 22 Mar 2025 at 05:27, Evgeny Morozov
wrote:
> select read_partition(1); -- This takes shared locks on entity_1 AND
> entity_2
>
> -- select count(*) from entity where part_id = 1; -- but this would only
> take a shared lock only on entity_1
>
> If another session tries something that takes
On Sat, 29 Mar 2025 at 06:00, Evgeny Morozov
wrote:
>
> On 23/03/2025 2:35 pm, David Rowley wrote:
> >> alter table entity_2 add column new_column text;
> > Is this just an example command? You can't add a column to a
> > partition directly.
>
> Yes, it was
On Fri, 25 Apr 2025 at 01:32, Marc Millas wrote:
> Nested Loop Left Join (cost=941400.77..966327.57 rows=3 width=653) (actual
> time=52655.694..62533.811 rows=346 loops=1)
> Buffers: shared hit=10068265 read=396705 dirtied=1858 written=218, temp
> read=429687 written=115187
> I/O Timings: r
On Sun, 27 Apr 2025 at 14:31, Marcelo Fernandes wrote:
> I have a scenario where virtually all user tables in the database will need to
> have a lower fill factor.
> Does this make sense? Have I missed something about being able to change this
> on a database level?
I suspect that it's possible
On Thu, 1 May 2025 at 00:29, PALAYRET Jacques wrote:
> => In the subquery, the semantic analysis of the query considers the column "
> dat " instead of the expression " to_char(dat,'mm') ", which is actually
> a grouped column.
> Is this normal? How can I simply resolve the problem?
Calcula
201 - 300 of 309 matches
Mail list logo
