Re: Query unable to utilize index without typecast to fixed length character

[ahi]

You are right we should move from character(N) to text, however the
explicit typecast is also required for the numeric column not just the
character one

On Thu, Apr 6, 2023 at 4:50 PM Tom Lane  wrote:

> ahi  writes:
> > CREATE TABLE public.marketplace_sale (
> > log_index integer NOT NULL,
> > created_at timestamp with time zone DEFAULT now() NOT NULL,
> > updated_at timestamp with time zone DEFAULT now() NOT NULL,
> > block_timestamp timestamp with time zone NOT NULL,
> > block bigint NOT NULL,
> > contract_address character(42) NOT NULL,
> > buyer_address character(42) NOT NULL,
> > seller_address character(42) NOT NULL,
> > transaction_hash character(66) NOT NULL,
> > quantity numeric NOT NULL,
> > token_id numeric NOT NULL,
>   ...
>
> Type character(N) is a hangover from the days of punched cards.
> Don't use it.  It has weird semantics concerning trailing spaces,
> which are almost never the behavior you actually want, and cause
> interoperability issues with type text.  (Text is Postgres' native
> string type, meaning that unlabeled string constants will tend to
> get resolved to that.)
>
> regards, tom lane
>

Is there any tool which will help me run and explain analyze about 150 queries?

[kunwar singh]

Hi Listers,
Anyone here use such a tool for Postgres? Any recommendations?

Say I have 150 queries in Postgres 11 and I want to upgrade to Postgres 15.
I want to run explain analyze for 150 in both versions for comparative
analysis.

I am looking for the easiest way to do it with a tool :)

-- 
Cheers,
Kunwar

Re: Is there any tool which will help me run and explain analyze about 150 queries?

[Achilleas Mantzios]

Prepend "EXPLAIN ANALYZE " on every statement :

cat foo.sql  | awk '{print " EXPLAIN (ANALYZE, BUFFERS, TIMING, SUMMARY) 
" $0}' | psql testdb -f -


Στις 7/4/23 20:57, ο/η kunwar singh έγραψε:

Hi Listers,
Anyone here use such a tool for Postgres? Any recommendations?

Say I have 150 queries in Postgres 11 and I want to upgrade to 
Postgres 15. I want to run explain analyze for 150 in both versions 
for comparative analysis.


I am looking for the easiest way to do it with a tool :)

--
Cheers,
Kunwar


--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt

Re: Is there any tool which will help me run and explain analyze about 150 queries?

[kyle Hailey]

In my opinion, Datadog is the best Postgres monitor available, but it
doesn't have a feature that has been discussed. However, you can use
auto_explain to analyze long-running queries by setting a limit. I recently
enabled it in GCP Cloud SQL, but I haven't seen any results in the logs
yet. I need to figure out which parameters to enable to get it working.




On Fri, Apr 7, 2023 at 10:57 AM kunwar singh 
wrote:

> Hi Listers,
> Anyone here use such a tool for Postgres? Any recommendations?
>
> Say I have 150 queries in Postgres 11 and I want to upgrade to Postgres
> 15. I want to run explain analyze for 150 in both versions for comparative
> analysis.
>
> I am looking for the easiest way to do it with a tool :)
>
> --
> Cheers,
> Kunwar
>

Re: Is there any tool which will help me run and explain analyze about 150 queries?

[kunwar singh]

Thanks Kyle. I am also trying to get it working :).

@Archilleas ,Thanks for your inputs. Appreciate it. I am further
interested in learning if we can automate the generation/creation of  foo.sql
by including queries with bind variables defined and bind values populated
( speaking in Oracle linguistics , pardon my Postgres ignorance, a newbie
here)

On Fri, Apr 7, 2023 at 2:41 PM kyle Hailey  wrote:

>
> In my opinion, Datadog is the best Postgres monitor available, but it
> doesn't have a feature that has been discussed. However, you can use
> auto_explain to analyze long-running queries by setting a limit. I recently
> enabled it in GCP Cloud SQL, but I haven't seen any results in the logs
> yet. I need to figure out which parameters to enable to get it working.
>
>
>
>
> On Fri, Apr 7, 2023 at 10:57 AM kunwar singh 
> wrote:
>
>> Hi Listers,
>> Anyone here use such a tool for Postgres? Any recommendations?
>>
>> Say I have 150 queries in Postgres 11 and I want to upgrade to Postgres
>> 15. I want to run explain analyze for 150 in both versions for comparative
>> analysis.
>>
>> I am looking for the easiest way to do it with a tool :)
>>
>> --
>> Cheers,
>> Kunwar
>>
>

-- 
Cheers,
Kunwar

Re: Is there any tool which will help me run and explain analyze about 150 queries?

[Jerry Sievers]

kunwar singh  writes:

> Hi Listers,
> Anyone here use such a tool for Postgres? Any recommendations?
>
> Say I have 150 queries in Postgres 11 and I want to upgrade to Postgres 15. I 
> want to run explain analyze for 150 in both versions for comparative
> analysis. 
>
> I am looking for the easiest way to do it with a tool :) 

I'd use a tool like bash for this which is very affordable :-)

Just load your queries into individual files in some directory with a
.sql suffix...

for file in $some-directory/*.sql; do
psql <$file.explain-output 2>&1
explain analyze
$(<$file)
EOF
done