On Tue, Dec 12, 2017 at 8:15 AM, Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:
> A
> nd the bigger question, Where are the missing 180G ?
>
> In the toaster probably...
https://www.postgresql.org/docs/current/static/storage-toast.html
Basically large data values are store in another
On Mon, Dec 18, 2017 at 5:00 PM, Dave Johansen
wrote:
>
> other = a.other
> But I get this error:
> ERROR: invalid reference to FROM-clause entry for table "a"
>
> LINE 1: ...ue), COUNT(DISTINCT value) FROM other WHERE other=a.other GR...
>
> HINT: There is an entry for table "a", but it cannot
On Tue, Jan 23, 2018 at 7:39 AM, Pavan Teja
wrote:
> " checkpoints are frequently occurring (1second apart). Consider
> increasing checkpoint_segements parameter".
>
The custom on these lists is to bottom or inline post.
This tends to appear when someone decide to write a load script of the
f
On Wed, Jan 24, 2018 at 7:48 AM, Pavan Teja
wrote:
>
>
> Yes, but why doing vacuum freeze of a table is causing a rapid
> archiving??
> Any idea??
>
>
IIUC Freezing involves physically altering those pages that are not frozen
to make them frozen. Those changes are logged just like any (mo
On Sunday, January 28, 2018, Nur Agus wrote:
>
> Is this a bug on postgres-9.4.4 ?
>
> We are considering upgrade to postgres-10 but since this is a very
> critical system, it takes a lot of test and approval :)
>
>
Upgrade to 9.4.15. Asking if 9.4.4 might have a bug is a question most
people h
On Fri, Feb 2, 2018 at 8:49 AM, Nandakumar M wrote:
> But, for the PK column we are not in control of the index that is created.
>
You probably can (I assume the nulls aspect of the index doesn't prevent
PK usage), but you must add the PK to the table after creating the index
and not let the sy
On Saturday, April 28, 2018, Kaushal Shriyan
wrote:
> Hi,
>
> I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance
> type with 500 GB volume of volume type io1 with 25000 IOPS and I am
> seeing performance issues. The sql query response takes around *127713.413
> ms *time*.* Is
On Mon, May 21, 2018 at 6:39 AM, pavan95
wrote:
> Hi Abbas,
>
> Thanks for your valuable suggestions. To my surprise I got the same output
> as what I have executed before.
>
> But unfortunately I'm unable to understand the logic of the code, in
> specific what is base 60 number? The used data ty
On Mon, May 21, 2018 at 7:43 AM, pavan95
wrote:
> Hi David,
>
> Thank you so much for your valuable inputs. Is there anything that I need
> to look from Indexes perspective or Join order ??
>
> Kindly let me know if it can be tuned further.
>
What I've got to give here is what you've received.
On Tue, Mar 1, 2022 at 8:37 AM Andrew Zakharov wrote:
> create table region_hierarchy(
>
> gid uuid not null default uuid_generate_v1mc(),
>
> parent_gid uuid null,
>
> region_code int2,
>
>
>
> I’ve carefully looked thru docs/faqs/google/communities and found out that
> I must include “g
On Tue, Mar 1, 2022 at 9:37 AM Andrew Zakharov wrote:
> David, - yes, creation composite foreign/primary key is not a problem. But
> the main question is what method should I use for partitioning by composite
> key gid, region_code?
>
The convention here is to inline or bottom-post responses.
Y
On Wed, Apr 6, 2022 at 5:27 PM Saurabh Sehgal wrote:
>
> I have the following query:
>
> *explain* (*analyze*, costs, timing) *SELECT* rr.* *FROM* rpc rpc
>
>*INNER* *JOIN* rr rr
>
>*ON* rr.uuid = rpc.rr_id
>
>*INNER* *
On Sun, Apr 17, 2022 at 8:53 AM Kumar, Mukesh
wrote:
> We request you to please provide some assistance on below issue and it is
> impacting the migration project.
>
I suggest you try and re-write the loop-based function into a set-oriented
view.
Specifically, I think doing: "array_agg(DISTINCT
On Tuesday, May 31, 2022, Praneel Devisetty
wrote:
>
> Initially it was processing 1000 tables per minute. Performance is
>> gradually dropping and now after 24 hr it was processing 90 tables per
>> minute.
>>
>
That seems like a fairly problematic metric given the general vast
disparities in siz
On Sun, Jun 12, 2022 at 2:47 PM Josh wrote:
>
> This was only possible because I was dealing with arrays though, and an
> operation such as `in (select unnest...)` can be easily converted to `=
> any(...)`. However for the general case,
In the general case you don't have subqueries inside join
On Tue, Jul 26, 2022 at 5:45 AM James Pang (chaolpan)
wrote:
> Without step 3 , copy data take long time. Use wal_level=minimal can
> help make COPY load data without logging ?
>
>
I believe you are referring to:
https://www.postgresql.org/docs/current/populate.html#POPULATE-COPY-FROM
Since
On Wed, Feb 8, 2023 at 11:01 AM Mark Hills wrote:
>
> CREATE DOMAIN hash AS text
> CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$');
>
> devstats=> ALTER TABLE invite ADD COLUMN test hash;
> ALTER TABLE
> Time: 30923.380 ms (00:30.923)
>
Necessarily, I presume because if you decided that the check on
On Friday, April 28, 2023, Παρασκευη Πασσαρη
wrote:
> Dear all,
>
> We are facing a performance issue with the following query. Executing this
> query takes about 20 seconds.
> (the database version is 14.1)
>
Given the possibility of this working better in the supported 14.7 I
suggest starting
On Mon, Jun 19, 2023 at 1:05 PM Les wrote:
> AFAIK PostgreSQL does not allow a row to occupy multiple blocks.
>
Your plan is going to heavily involve out-of-band storage. Please read up
on it here:
https://www.postgresql.org/docs/current/storage-toast.html
I'm not looking for a definitive ans
On Tuesday, August 8, 2023, An, Hongguo (CORP) wrote:
> Hi:
>
> I have a function, if I call it from DBeaver, it returns within a minute.
>
> *call* commonhp.run_unified_profile_load_script_work_assignment_details(
> 'BACDHP', 'G3XPM6YE2JHMSQA2');
>
> but if I called it from spring jdbc template,
On Wed, Nov 8, 2023 at 6:26 PM Lincoln Swaine-Moore
wrote:
> SELECT
>
s at time zone 'utc' AS period_start,
> LEAD(s) OVER (
> ORDER BY
> s
> ) at time zone 'utc' AS period_end
>
Maybe does
On Thursday, December 28, 2023, mohini mane
wrote:
> Thank you for your response !!
> I am experimenting with SQL query performance for SELECT queries on large
> tables and I observed that changing/increasing the degree of parallel hint
> doesn't give the expected performance improvement.
>
> I h
On Tue, Jan 2, 2024 at 8:12 AM mohini mane wrote:
>
> I have executed the SELECT query with 2,4 & 6 parallel degree however
>>> every time only 4 workers launched & there was a slight increase in
>>> Execution time as well,
>>>
>>
>> Adding an ignored comment to your SQL would not be expected to
On Wednesday, June 12, 2024, nikhil kumar wrote:
>
> Can anyone please help on SMTP configuration for send gmail. If any
> document please let me know.
>
This seems like an exceedingly unusual place to be asking for such help…
David J.
On Wed, Jul 10, 2024 at 11:05 AM nikhil kumar
wrote:
>
> We received a request from client. They required all functions, stored
> procedures and triggers backup. can anyone please let me know. How to take
> backup only above objects.
>
This hardly qualifies as a performance question.
You might
On Thursday, July 11, 2024, FREYBURGER Simon (SNCF VOYAGEURS / DIRECTION
GENERALE TGV / DM RMP YIELD MANAGEMENT) wrote:
> Also, It might not be related, but I have suspiciously similar slow reads
> when I am inserting in database, could it be related ?
> I’m using a 3 steps process to insert my l
On Thu, Sep 13, 2018 at 12:49 PM, Fd Habash wrote:
> In API function may invoke 10 queries. Ideally, I would like to know what
> queries are invoked by it and how long each took.
>
>
>
> I’m using pg_stat_statement. I can see the API function statement, but how
> do I deterministically identify a
On Mon, Apr 8, 2019 at 3:31 PM Gavin Flower
wrote:
> I think having a specific migration group would also be likely to
> improve the visibility of pg, and the idea of migrating to pg. As it
> help pg to appear in more search results.
>
>
I presently have qualms retaining novice, sql, performance
On Wed, Jul 17, 2019 at 4:04 AM mayank rupareliya
wrote:
> create table fields(user_id varchar(64), field varchar(64));
> CREATE INDEX index_field ON public.fields USING btree (field);
>
> Any suggestions for improvement?
>
Reduce the number of rows by constructing a relationally normalized data
On Thu, Feb 13, 2020 at 1:42 PM Karl Düüna wrote:
> It really isn't noticeable until about 5k UPDATEs on a single row.
>
Don't know why, and never dealt with a scenario where this would even come
up, but that this doesn't perform well inside a transaction isn't
surprising to me. Kinda surprised
Please pick a single list to post to. Performance seems like the
unnecessary one here.
On Thu, Feb 20, 2020 at 10:34 PM Daulat Ram
wrote:
> Can we have multiple tablespaces with in a database in postgres?
>
I fell as if I'm missing something in your question given the presence of
the "CREATE T
How is this a performance related question?
On Tue, Mar 24, 2020 at 11:10 AM Luis Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
> However, I am getting the same result over mulitiple rows. This is a
> sample of the SQL I am using:
>
> select (select string_agg(random()::text,';')
>
On Friday, April 17, 2020, Rick Vincent wrote:
> Hi,
>
> I was wondering if anyone can explain the below problem. Should a bug be
> logged for this?
>
> Kind regards,
> Rick
>
> _
> *From:* Rick Vincent
> *Sent:* Tuesday, April 7, 2020 11:08 AM
> *To:*
On Thursday, April 23, 2020, Thomas Kellerer wrote:
> > CREATE INDEX idx_tabla_entidad
> > ON public.entidad USING btree
> > (cod_tabla ASC NULLS LAST);
> >
> > CREATE INDEX idx_entidad_tabla_4
> > ON public.entidad USING btree
> > (cod_entidad_tabla ASC NULLS LAST)
> > INCLUD
On Thu, Apr 23, 2020 at 8:29 AM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thursday, April 23, 2020, Thomas Kellerer wrote:
> >> Plus: scanning idx_tabla_entidad is more efficient because that index is
> >> smaller.
>
> > Really? The a
On Thu, Apr 23, 2020 at 1:33 PM Tom Lane wrote:
> I find the apparently
> unnecessary cast in the partial-index predicate to be suspicious ---
> maybe that's blocking matching to the WHERE clause?
>
I noticed that too...I suspect its related to the ANALYZE result:
Index Scan using idx_tabla_ent
On Mon, May 4, 2020 at 9:12 AM Jean-Christophe Boggio <
postgre...@thefreecat.org> wrote:
> Is there a way to disable JIT (I use the apt.postgresql.org repository)
> in both 11.6 and 12.2 ? I would have liked to disable it on this
> particular query but maybe I could live with disabling JIT everyw
On Thu, May 7, 2020 at 7:40 AM Adrian Klaver
wrote:
> On 5/7/20 4:19 AM, Amarendra Konda wrote:
> > Hi,
> >
> > PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled
> > by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
> >
> > We have noticed huge difference interms of execu
On Thu, May 7, 2020 at 1:05 PM samhitha g
wrote:
> Our application serves multiple tenants. Each tenant has the schema with a
> few hundreds of tables and few functions.
> We have 2000 clients so we have to create 2000 schemas in a single
> database.
>
That is one option but I wouldn't say you m
On Thu, May 7, 2020 at 10:49 AM Amarendra Konda
wrote:
> Can you please explain, why it is getting more columns in output, even
> though we have asked for only one column ?
>
>
>
> * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url,
> pa.process_activity_user_id, pa.app_id,
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.created > '1970-01-01 00:00:00' AND EXISTS (
> SELECT 1 FROM proce
On Thu, May 21, 2020 at 7:28 AM postgann2020 s
wrote:
> which is having an avg width of 149bytes.
>
The average is meaningless if your maximum value exceeds a limit.
2. What type of index is the best suited for this type of data?.
>
And what type of data exactly are we talking about. "TEXT" i
On Thu, May 21, 2020 at 7:45 AM postgann2020 s
wrote:
> >And what type of data exactly are we talking about. ==> Column is
> stroing GIS data.
>
GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this
is maybe better posted to the PostGIS community directly...
David J.
On Thursday, May 21, 2020, postgann2020 s wrote:
>
> SELECT seq_no+1 INTO pair_seq_no FROM SCHEMA.TABLE WHERE (Column1 like
> '%,sheath--'||cable_seq_id ||',%' or Column1 like 'sheath--'||cable_seq_id
> ||',%' or Column1 like '%,sheath--'||cable_seq_id or
> Column1='sheath--'||cable_seq_id) orde
On Tue, Jun 2, 2020 at 10:17 AM Oleksandr Shulgin <
oleksandr.shul...@zalando.de> wrote:
> That *might* turn out to be the case with a small number of distinct
> values in the partitioning column(s), but then why rely on hash
> assignment instead of using PARTITION BY LIST in the first place?
>
>
On Friday, June 19, 2020, Laurenz Albe wrote:
>
> > I am absolutely aware that those are hashed sub plans below a seqscan
> and that Postgres therefore has to scan all tuples of the table. But the
> problem is that upper nodes (which are excluded from
> > this example for simplicity) think they w
On Sunday, October 11, 2020, Gopisetty, Ramesh
wrote:
>
> to sch USING ( key =
> f_sel_1(key)
> );
>
As Tom said it doesn’t matter what you classify the function as (stable,
etc) if your function call accepts a column reference as an input and
compares its output to another colu
On Wed, Oct 21, 2020 at 5:32 PM Nagaraj Raj wrote:
> Hi, I have long running query which running for long time and its planner
> always performing sequnce scan the table2.
>
FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm
> where hed.eventid=itm.eventid group by 1,2,3,4,5,6
On Wed, Oct 21, 2020 at 10:22 PM aditya desai wrote:
> As per application team, it is business requirement to show last 60 days
>> worth data.
>>
>
I didn't look deeply but it sounds like you are looking backwards into 60
days worth of detail every single time you perform the query and computing
On Wednesday, November 4, 2020, Koteswara Rao Daliparthi
wrote:
> Hi Guys, Sorry for the bold below. I just feel it helps others identify
> my question easily.
>
You stopped following the reporting form too soon. You also need to
provide the “questions about queries” section. Or “slow queries
On Mon, Mar 8, 2021 at 4:30 PM Nagaraj Raj wrote:
> I have a quick question, does role custom parameters settings will be
> granted to users well?
>
Parameters are not inherited - the role credentials that are logging in are
the ones that are used to check for defaults. This "no" is not explici
On Tue, Jul 27, 2021 at 7:57 PM l...@laurent-hasson.com <
l...@laurent-hasson.com> wrote:
> hash_mem_multiplier is an upper-bound right: it doesn't reserve memory
> ahead of time correct?
>
Yes, that is what the phrasing "maximum amount" in the docs is trying to
convey.
https://www.postgresql.or
On Wednesday, August 11, 2021, aditya desai wrote:
> Hi All,
> What is the difference between pg_triggers and
> information_schema.triggers? I want to list all triggers in the database.
>
Read the docs for information_schema.triggers.
> What is the best way to list all objects in PostgreSQL?(s
On Mon, Aug 23, 2021 at 2:46 AM Nagaraj Raj wrote:
>
> Currently this is not something can do. this functionality is there in
> oracle.
>
> Is this future considering to add? (it would really help for create any
> test schemas without disturbing current schema. )
>
>
I find this to be not all th
On Tue, Oct 19, 2021 at 2:48 PM Mithran Kulasekaran <
mithranakulaseka...@gmail.com> wrote:
> i think the only problem is when we try to use both union and where/join
> the issue starts to happen
>
I'm unconvinced this is actually an issue based upon what is presented
here. All I'm seeing is two
On Tuesday, October 19, 2021, Michael Lewis wrote:
> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran <
> mithranakulaseka...@gmail.com> wrote:
>
>> create view template_view (id, name, description, is_staged) as
>> select t.id,t.name, t.description, false as is_staged
>> from template t
>>
On Wed, Oct 20, 2021 at 6:58 AM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Tuesday, October 19, 2021, Michael Lewis wrote:
> >> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran <
> >> mithranakulaseka...@gmail.com> wrote:
> >>
On Wed, Oct 27, 2021 at 7:31 PM Tim Slechta wrote:
>
> == Point 2. The equivalent query on the VL10N_OBJECT_NAME view executes a
> Seq Scan on the underlying pl10n_object_name. Why?
> tc=# EXPLAIN ANALYZE select pval_0 from VL10N_OBJECT_NAME where pval_0 =
> '';
>
Just to confirm and simplif
On Monday, December 20, 2021, Lars Bergeson wrote:
>
> What is it about null values in the table that slows down the full table
> scan?
>
> If I populate blank/zero for all of the unused values in columns that are
> NULLable, the query is fast again. So just defining the columns as NULLable
> isn
On Monday, December 20, 2021, Justin Pryzby wrote:
> On Mon, Dec 20, 2021 at 08:11:42PM -0800, Lars Bergeson wrote:
> > ok, here are results after I did:
> > set max_parallel_workers_per_gather = 0;
> >
> > HashAggregate (cost=1676432.13..1676432.16 rows=3 width=15) (actual
> time=19908.343..199
On Monday, December 20, 2021, Tom Lane wrote:
> Justin Pryzby writes:
> > On Mon, Dec 20, 2021 at 08:11:42PM -0800, Lars Bergeson wrote:
> >> Still taking 10X more I/O to read the smaller table. Very odd.
>
> > If I'm not wrong, it's even worse than that ?
> > It takes 20 or 30sec to run the que
On Tue, Dec 21, 2021 at 4:07 PM Tom Lane wrote:
> Lars Bergeson writes:
> > I'm running PostgreSQL under AWS Aurora, and I didn't set it up or
> install
> > it, so I'm not sure about the OS version.
>
> Oh! Aurora is not Postgres. My admittedly-not-well-informed
> understanding is that they st
On Tue, Jan 18, 2022 at 10:13 AM Mladen Gogala
wrote:
>
> mgogala=# create unique index test1_uq on test1(col1,coalesce(col2,'***
> EMPTY ***'));
>
> -> Bitmap Index Scan on test1_uq (cost=0.00..1.70 rows=6 width=0)
...
>Index Cond: (test1.col1 = 1)
>
> How com
On Wed, Jan 19, 2022 at 7:59 AM Ludwig Isaac Lim wrote:
>
> I noticed that different is actually in Nested Loop join. One is taking 2
> minutes, other is taking 12 seconds. I find this puzzling as I assume the
> nested loop should be done in memory.
>
Everything is done in memory, but the data h
On Thu, Jan 20, 2022 at 4:22 PM Sbob wrote:
> I am looking for information on how PostgreSQL leverages or interfaces
> with CPU's on Linux. Does PostgreSQL let Linux do the work? Does it
> bypass the OS? Any information or docs you can send my way would be much
> appreciated.
>
>
PostgreSQL is a
On Thursday, October 24, 2024, Tom Lane wrote:
> Shiv Iyer writes:
> > The query plans and results you shared illustrate the unexpected
> > performance differences between using and bypassing the
> simplify_function()
> > logic in PostgreSQL’s optimizer. Here’s an in-depth analysis and thoughts
66 matches
Mail list logo
