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 Fri, 25 Apr 2025 at 03:06, Laurenz Albe wrote:
>
> On Fri, 2025-04-25 at 01:41 +1200, David Rowley wrote:
> > The 79.3 seconds is the total time spent doing reads for all parallel
> > workers. 52.6 seconds is the wall clock time elapsed to execute the
> > query.
>
&g
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 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 Thu, 3 Apr 2025 at 18:07, Tom Lane wrote:
> A simple-minded approach could be to just be pessimistic, and
> increase our estimate of how many rows would need to be scanned as a
> consequence of noticing that the columns have significant correlation.
> The shape of that penalty function would be
On Thu, 3 Apr 2025 at 16:24, Manikandan Swaminathan
wrote:
> Since you mentioned the planner not knowing about the correlation between the
> columns, I’m curious, why doesn’t making a multivariate statistic make a
> difference?
>
>
> CREATE STATISTICS col_a_col_b_stats (dependencies) ON col_a, c
On Sat, 29 Mar 2025 at 10:30, Renan Alves Fonseca
wrote:
> Currently, in the SQL function path the plan is always generic. The
> planner ignores the function arguments. The plan_cache_mode setting
> has no effect in this path.
>
> I agree that the docs should be more explicit about this. There is
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 Wed, 19 Feb 2025 at 09:56, dfgpostgres wrote:
> So I want the avg of the max of the set where id=1 (5.0), where id=2 (6.0),
> where id=3 (8.0) ~= 6.33...
>
> I tried this...
>
> select
> avg(x.maxsz)
>from
> dvm.dvm_events d,
>
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 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, 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 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 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 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 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 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 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 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, 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 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 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 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 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 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, 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 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 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 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 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 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 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 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, 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 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 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 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 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 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, 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
(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, 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
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 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 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 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 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 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 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 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 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: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 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 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 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 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 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 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 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 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 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, 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, 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 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 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 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 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, 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 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
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 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 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 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, 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 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 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 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 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 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, 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 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 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 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, 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 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 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 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 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 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 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 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, 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 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 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 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 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 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 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
>
1 - 100 of 304 matches
Mail list logo
