On 11/15/24 11:46, Bharani SV-forum wrote:
Team
Need exact SQL query to find List of Detach Partitioned Tables (Yet to
be Dropped)
The following is the query which i used, i am using and i found an bug
which is listing an newly created table (last week)
As David G. Johnston said how
On Fri, Nov 15, 2024 at 12:46 PM Bharani SV-forum
wrote:
> Need exact SQL query to find List of Detach Partitioned Tables (Yet to be
> Dropped)
>
>
The premise that a detached table is distinguishable from any other table
that is not a partition is an interesting one that I wouldn&#
Team
Need exact SQL query to find List of Detach Partitioned Tables (Yet to be
Dropped)
The following is the query which i used, i am using and i found an bug which is
listing an newly created table (last week)
SELECT relnamespace::regnamespace::text AS schema_name, relname AS table_name
FROM
> On 29 Jul 2023, at 10:59, Peter J. Holzer wrote:
>
> On 2023-07-26 15:46:16 +0800, gzh wrote:
>> SET enable_seqscan TO off;
> [...]
>>-> Parallel Bitmap Heap Scan on tbl_sha
>> (cost=92112.45..2663789.14 rows=800650 width=18) (actual
>> time=260.540..21442.169 rows=804
On 2023-07-26 15:46:16 +0800, gzh wrote:
> SET enable_seqscan TO off;
[...]
> -> Parallel Bitmap Heap Scan on tbl_sha
> (cost=92112.45..2663789.14 rows=800650 width=18) (actual
> time=260.540..21442.169 rows=804500 loops=3)
> Recheck Cond: (ms_cd =
Thank you very much for taking the time to reply to my question.
> The problem is none of the explains you sent match with the description
> above. The last one when you forced the optimizer to go with index scan (SET
> enable_seqscan TO off;) the chosen index seems to be one composed by ms_
Hi "gzh",
Based on the info you provided I'm assuming you are trying to use the
TBL_SHA primary key to do an index-only scan as in you mentioned above you
have:
> TBL_SHA
> ms_cd character(6) NOT NULL -- PRIMARY KEY
> et_cd character(8)
> etrys character(8)
Assuming a composed index here by
Thank you for your suggestion.
The database is hosted on AWS RDS, with the instance having vCPU=2 and RAM=4GB.
Could the low performance be due to the low configuration of AWS RDS?
We are considering trying a higher configuration instance.
At 2023-07-27 11:36:20, "David Rowley" wrote:
>
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
Thank you very much for taking the time to reply to my question.
>You might want to check your description of the table definitions.
>Going by the above EXPLAIN ANALYZE output, it very much does not look
>like ms_cd is the primary key of TBL_SHA. If it is then it's very
>weird that you have 32000
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 Tue, 2023-07-25 at 11:11 +0800, gzh wrote:
> > I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ...
> > Or do you have a version that is too old for SETTINGS?
> Sorry. Please refer to the following execution plan.
>
> [...]
> Settings: effective_cache_size = '1886088kB', jit = 'off', search_pat
Thank you for your reply.
>I think the whole query can just:
>select COUNT(ET_CD)
>from TBL_SHA
>WHERE TBL_SHA.MS_CD = 'MLD009' AND TBL_SHA.ETRYS = '0001';
This is related to the business logic.
>if many duplicates rows returned, then there is no point of evaluate
>something like {1 in (1,1
>I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ...
>Or do you have a version that is too old for SETTINGS?
Sorry. Please refer to the following execution plan.
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
select COUNT(ET_CD)
from TBL_SHA
WHERE TBL_SHA.MS_CD = 'MLD009'
and TBL_SHA.ETRYS in
On Mon, 2023-07-24 at 17:54 +0800, gzh wrote:
> EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON)
I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ...
Or do you have a version that is too old for SETTINGS?
One other idea: check if the index is INVALID (this will
be visible if you run "\d ta
On Mon, Jul 24, 2023 at 5:54 PM gzh wrote:
>
> >Did you change any parameters that have an impact on query planning?
>
> >You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
>
> I added some parameters and re-executed the Execution Plan.
>
> Except for the index not taking effe
>Did you change any parameters that have an impact on query planning?
>You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
I added some parameters and re-executed the Execution Plan.
Except for the index not taking effect, I still don't know the reason why the
index is not w
On Fri, 2023-07-21 at 09:43 +0800, 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)
>
> TBL_INF
>
> ms_cd character(6) NOT NULL -- PRIMARY KEY
> ry_cd characte
>select (38700325 - 11833442) /38700325.0;
>is 0.69 approx.
>So I think it says around 69% of rows satisfy the query condition.Thank you
>for your reply. I have learned a lot from it.
At 2023-07-20 23:20:16, "jian he" wrote:
>On Thu, Jul 20, 2023 at 7:36 PM gzh wrote:
>>
>>
>> Thank
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)
TBL_INF
ms_cd character(6) NOT NULL -- PRIMARY KEY
ry_cd character(8) NOT NULL -- PRIMARY KEY
I made some modi
On Thu, Jul 20, 2023 at 7:36 PM 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 = '00
On Thu, 2023-07-20 at 22:14 +0800, gzh wrote:
> The information I provided is incorrect, please see my previous reply.
My question remains: I would like to see the table definitions.
Also, did you ANALYZE the tables?
Yours,
Laurenz Albe
t;"TBL_SHA"
>and "TBL_INF"?
For information security reasons, I can't provide the table definition, these
columns are defined as "character".
At 2023-07-20 19:58:59, "Laurenz Albe" wrote:
>On Thu, 2023-07-20 at 15:09 +0800, gzh wrote:
On Thu, 2023-07-20 at 15:09 +0800, gzh wrote:
> I'm running into some performance issues with my SQL query.
> The following SQL query is taking a long time to execute.
>
> explain analyze
> select COUNT(ET_CD)
> from TBL_SHA
> WHERE TBL_SHA.MS_CD = '009'
rows=1 loops=1)
Planning Time: 0.228 ms
Execution Time: 0.070 ms
At 2023-07-20 16:07:15, "Erik Wienhold" wrote:
>> On 20/07/2023 09:09 CEST gzh wrote:
>>
>> I'm running into some performance issues with my SQL query.
>> The followi
On 2023-07-20 15:09:22 +0800, gzh wrote:
> Hi everyone,
>
>
> I'm running into some performance issues with my SQL query.
>
> The following SQL query is taking a long time to execute.
>
>
> explain analyze
> select COUNT(ET_CD)
> from TBL_SHA
> WHERE
> On 20/07/2023 09:09 CEST gzh wrote:
>
> I'm running into some performance issues with my SQL query.
> The following SQL query is taking a long time to execute.
>
> explain analyze
> select COUNT(ET_CD)
> from TBL_SHA
> WHERE TBL_SHA.MS_CD = '009'
>
Hi everyone,
I'm running into some performance issues with my SQL query.
The following SQL query is taking a long time to execute.
explain analyze
select COUNT(ET_CD)
from TBL_SHA
WHERE TBL_SHA.MS_CD = '009'
and TBL_SHA.ETRYS in
(select TBL_INF.RY_CD
from TBL
On 11/8/22 17:03, Laurenz Albe wrote:
> On Tue, 2022-11-08 at 04:14 +, Jan Bilek wrote:
>
>> I know it is not exactly what you suggested (and agreeing a lot with our
>> app user shouldn't be running as superuser), but as all other inputs
>> from our application come sanitized through bind and t
On Tue, 2022-11-08 at 04:14 +, Jan Bilek wrote:
> I know it is not exactly what you suggested (and agreeing a lot with our
> app user shouldn't be running as superuser), but as all other inputs
> from our application come sanitized through bind and this is the only
> way where user can send
On 11/8/22 11:50, Christophe Pettus wrote:
>
>> On Nov 7, 2022, at 17:43, Jan Bilek wrote:
>>
>> Well, superuser (our App) is already logged in and as it is designed
>> very much as an "appliance" it simply does that job - manages its
>> database.
> Well... don't do that. :) The problem is analog
On Tue, 2022-11-08 at 01:24 +, Jan Bilek wrote:
> I am leading PCI:SSF audit on our payment switch platform product and having
> a issue where our QSA
> just came with multiple ways how to escalate privileges and get a shell to
> the remote host through
> our built-in Reporting system which r
On Mon, Nov 7, 2022 at 8:25 PM Jan Bilek wrote:
> ...
> select * from pg_read_file('/etc/passwd' , 0 , 100); -> it's possible to
> display content of '/etc/passwd/' file
> select version(); -> Result of DBMS version request.
Input filtering may help in the interim, until you get the roles an
On Mon, Nov 7, 2022 at 6:25 PM Jan Bilek wrote:
> The main problem comes from obvious - our application's PostgreSQL user
> needs to have an Superuser role as it manages most of its (dedicated)
> database (creates tables, drops those, manages views, triggers ... ).
>
No, the things you want to ma
> On Nov 7, 2022, at 17:43, Jan Bilek wrote:
>
> Well, superuser (our App) is already logged in and as it is designed
> very much as an "appliance" it simply does that job - manages its
> database.
Well... don't do that. :) The problem is analogous to having root log into a
Linux box and
On 11/8/22 11:29, Christophe Pettus wrote:
>
>> On Nov 7, 2022, at 17:24, Jan Bilek wrote:
>> Would there be any way to go around this?
> The typical configuration is to not permit the PostgreSQL superuser to log in
> remotely. The database can be managed by a different, non-superuser role,
> i
> On Nov 7, 2022, at 17:24, Jan Bilek wrote:
> Would there be any way to go around this?
The typical configuration is to not permit the PostgreSQL superuser to log in
remotely. The database can be managed by a different, non-superuser role,
including schema migrations.
> CREATE OR REPLACE
Hi team,
I am leading PCI:SSF audit on our payment switch platform product and having a
issue where our QSA just came with multiple ways how to escalate privileges and
get a shell to the remote host through our built-in Reporting system which runs
on PostgreSQL (12-14).
1. select * from pg_
On Wednesday, January 22, 2020, srikkanth
wrote:
> Hi Jan,
>
> Thanks for your input, it is working exactly what i want, but still i m
> facing issue because in my table there are different data types they are 10
> to 15 columns are Boolean, one primary key, 140 columns are character.
>
> when i
in this
case.Thanks,Srikanth BFrom: Jan Kohnert
<nospam001-li...@jan-kohnert.de>Sent: Wed, 22 Jan 2020 17:11:56To:
pgsql-general@lists.postgresql.orgSubject: Re: SQL Query Syntax helpHi,Am
Mittwoch, 22. Januar 2020, 12:00:45 CET schrieb srikkanth:[sniped
HTML]something likeselect id, 'Co
On Wed, 22 Jan 2020 at 11:00, srikkanth
wrote:
> Can you please help me in writing the syntax for the below mentioned table.
>
Suggest looking at the crosstab function.
https://www.postgresql.org/docs/current/tablefunc.html
crosstab(text source_sql, text category_sql)
Produces a "pivot
srikkanth wrote:
> Can you please help me in writing the syntax for the below mentioned
This looks like an UNPIVOT operation.
Here's a generic method that does this without having to specify the
columns individually, with the help of json functions:
SELECT ID, key, value FROM
(SELECT
Hi,
Am Mittwoch, 22. Januar 2020, 12:00:45 CET schrieb srikkanth:
[sniped HTML]
something like
select
id, 'Col A' as "Col Name", "Col A" as "Col Value"
from input
union all
select
id, 'Col B' as "Col Name", "Col B" as "Col Value"
from input
...
order by
1, 2;
should work.
--
Kin
Hi Team,Can you please help me in writing the syntax for the below mentioned
table.Input : ID
Col A Col B Col C
Col D Col E Col F
> On Fri, Nov 22, 2019 at 01:20:59PM +, Zwettler Markus (OIZ) wrote:
> > I came up with the following query which should return any apply lag in
> > seconds.
> >
> > select coalesce(replay_delay, 0) replication_delay_in_sec from (
> >select datname,
> > (
> >
On Fri, Nov 22, 2019 at 01:20:59PM +, Zwettler Markus (OIZ) wrote:
> I came up with the following query which should return any apply lag in
> seconds.
>
> select coalesce(replay_delay, 0) replication_delay_in_sec
> from (
>select datname,
> (
> select ca
We would like to check the Postgres SYNC streaming replication status with
Nagios using the same query on all servers (master + standby) and versions
(9.6, 10, 12) for simplicity.
I came up with the following query which should return any apply lag in seconds.
select coalesce(replay_delay, 0)
> The above is not the same format as OP's query:
>
> Update tableA set col1 = null and col2 in (1,2);
I did include set in the sql. I typed it wrong here.
On 4/18/19 11:36 AM, Ravi Krishna wrote:
Not able to produce this with PG 11.1
If col1 is any type other than boolean, the update statement fails in syntax.
If col1 is boolean, then it updated it correctly. In other words
update col1 = NULL
and col2 in (1,2)
is treated same as
upda
"Ravi Krishna"
> To: "Vikas Sharma"
> Cc: pgsql-general@lists.postgresql.org
> Subject: Re: SQL query
>
> Not able to produce this with PG 11.1
>
> If col1 is any type other than boolean, the update statement fails in syntax.
> If col1 is bool
Not able to produce this with PG 11.1
If col1 is any type other than boolean, the update statement fails in syntax.
If col1 is boolean, then it updated it correctly. In other words
update col1 = NULL
and col2 in (1,2)
is treated same as
update col1 = NULL
where col2 in (1,2)
Also
On 4/18/19 11:43 AM, Vikas Sharma wrote:
Hi,
I have come across a query that a developer wrote to update a few rows in
table, the query did update the two desired rows but also updated the
rest of the table with the column value as 'false'.
Update tableA set col1 = null and col2 in (1,2);
T
On 4/18/19 9:43 AM, Vikas Sharma wrote:
Hi,
I have come across a query that a developer wrote to update a few rows
in table, the query did update the two desired rows but also updated the
rest of the table with the column value as 'false'.
Update tableA set col1 = null and col2 in (1,2);
Th
On Thursday, April 18, 2019, Vikas Sharma wrote:
>
> The above was run without where clause.
>
There was no where clause thus every row has to be updated by
definition...null and bool evaluates to either false or null since if bool
is false the null doesn’t matter and if bool is true the result
Hi,
I have come across a query that a developer wrote to update a few rows in
table, the query did update the two desired rows but also updated the
rest of the table with the column value as 'false'.
Update tableA set col1 = null and col2 in (1,2);
The query updated col1 to null for the rows whe
ne 30. 9. 2018 v 18:49 odesílatel Arup Rakshit napsal:
> I just added it as you said, but I am getting same plan.
>
>
> Sort (cost=62842.16..62846.91 rows=1897 width=35) (actual
> time=1845.831..1845.950 rows=1229 loops=1)
> Sort Key: projects.id
> Sort Method: quicksort Memory: 145kB
> -
I just added it as you said, but I am getting same plan.
Sort (cost=62842.16..62846.91 rows=1897 width=35) (actual
time=1845.831..1845.950 rows=1229 loops=1)
Sort Key: projects.id
Sort Method: quicksort Memory: 145kB
-> HashAggregate (cost=62710.42..62738.88 rows=1897 width=35) (actual
Hi
ne 30. 9. 2018 v 18:23 odesílatel Arup Rakshit napsal:
> I have the below query which is taking 1873 ms. How can I improve this?
>
> explain analyze select
> sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 -
> coalesce(workitems.discount, 0)/ 100)) as total_budget_cents,
> s
I have the below query which is taking 1873 ms. How can I improve this?
explain analyze select
sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 -
coalesce(workitems.discount, 0)/ 100)) as total_budget_cents,
sum(coalesce(price_cents, 0)::bigint * coalesce(quantity
Hello Tom, good evening.
Thank you for your prompt answer and help.
It was enough to ANALYZE the temp tables to achieve a magnificent
result: 37 seconds.
I'm portuguese.
It's a shame you live so far from me. So I can't put a box of bottles of
Porto wine at your door.
I also thank David an
DiasCosta writes:
> This is the query plan for only 19684 rows.
I think you're getting a bad query plan, mostly as a result of two
factors:
* Poor row estimates. It looks like the bottom-most misestimations
are on temp tables, which makes me wonder whether you've ANALYZEd
those tables. Your ap
Hello David and Fabrízio,
The names of the tables and indexes differ from the original script.
Only the names.
This is the query plan for only 19684 rows.
I have another query running for around 3 rows, but it takes an
eternity to finish.
If it finishes in acceptable time I'll make it a
On Wed, Jun 20, 2018 at 3:34 PM, Fabrízio de Royes Mello <
fabri...@timbira.com.br> wrote:
> And use some external service like pastebin.com to send long SQL
> statements.
>
Or just attach a text file - those are allowed on these lists.
David J.
2018-06-20 18:35 GMT-03:00 DiasCosta :
>
> Hi all,
> can someone help me?
>
> I don't know if this is the correct list for this matter. If I'm wrong,
please bear with me and point me in right direction.
>
Here is a good start...
> I have a large query which, largely after more than 24 hours run
Hi all,
can someone help me?
I don't know if this is the correct list for this matter. If I'm wrong,
please bear with me and point me in right direction.
I have a large query which, largely after more than 24 hours running,
doesn't come to an end;
However I can see, using system tools, that
66 matches
Mail list logo
