Обсуждение: Related to Foreign Table Accessing

Поиск
Список
Период
Сортировка

Related to Foreign Table Accessing

От
ROHIT SACHDEVA
Дата:

Hi Team,
The application team is accessing a foreign table in batches of 200 rows, but the problem they are facing is that the query is taking different times for the same set of rows.
The foreign table is only 1 shard with a partition of 21, and the partition we are using is a hash partition.

What can I do at the database side to debug this or any configuration parameter related to this wired behavior?

Have a Good day !!!

Regards
Rohit Sachdeva

Re: Related to Foreign Table Accessing

От
ROHIT SACHDEVA
Дата:
Hi Team,

When i run the same query via client it takes around 2 seconds and when it is run at application side it is taking around 40 seconds 

Examples 
select animal_id  from table  where owner_id in ()
limit 200 offset 1600  -- takes 2 seconds 
select animal_id  from table  where owner_id in ()
limit 200 offset 1800 --takes 40 seconds.

What will be this issue?

On Tue, Nov 28, 2023 at 2:08 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:

Hi Team,
The application team is accessing a foreign table in batches of 200 rows, but the problem they are facing is that the query is taking different times for the same set of rows.
The foreign table is only 1 shard with a partition of 21, and the partition we are using is a hash partition.

What can I do at the database side to debug this or any configuration parameter related to this wired behavior?

Have a Good day !!!

Regards
Rohit Sachdeva


--
Have a Good day !!!

Regards
Rohit Sachdeva

Re: Related to Foreign Table Accessing

От
ROHIT SACHDEVA
Дата:
Hi Team,

When I see the same query via pg_stat_activity it was hang on explain statement.

How to handle this and moreover I found in docs there is a parameter I.e analyze_sampling.

How to set this ?

On Thu, 30 Nov, 2023, 9:15 pm ROHIT SACHDEVA, <sachdeva.rohit648@gmail.com> wrote:
Hi Team,

When i run the same query via client it takes around 2 seconds and when it is run at application side it is taking around 40 seconds 

Examples 
select animal_id  from table  where owner_id in ()
limit 200 offset 1600  -- takes 2 seconds 
select animal_id  from table  where owner_id in ()
limit 200 offset 1800 --takes 40 seconds.

What will be this issue?

On Tue, Nov 28, 2023 at 2:08 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:

Hi Team,
The application team is accessing a foreign table in batches of 200 rows, but the problem they are facing is that the query is taking different times for the same set of rows.
The foreign table is only 1 shard with a partition of 21, and the partition we are using is a hash partition.

What can I do at the database side to debug this or any configuration parameter related to this wired behavior?

Have a Good day !!!

Regards
Rohit Sachdeva


--
Have a Good day !!!

Regards
Rohit Sachdeva

Re: Related to Foreign Table Accessing

От
Ron Johnson
Дата:
where owner_id in ()

1. What's in those parentheses?

2. Have you added EXPLAIN ANALYZE?

On Fri, Dec 8, 2023 at 9:00 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:
Hi Team,

When I see the same query via pg_stat_activity it was hang on explain statement.

How to handle this and moreover I found in docs there is a parameter I.e analyze_sampling.

How to set this ?

On Thu, 30 Nov, 2023, 9:15 pm ROHIT SACHDEVA, <sachdeva.rohit648@gmail.com> wrote:
Hi Team,

When i run the same query via client it takes around 2 seconds and when it is run at application side it is taking around 40 seconds 

Examples 
select animal_id  from table  where owner_id in ()
limit 200 offset 1600  -- takes 2 seconds 
select animal_id  from table  where owner_id in ()
limit 200 offset 1800 --takes 40 seconds.

What will be this issue?

On Tue, Nov 28, 2023 at 2:08 PM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:

Hi Team,
The application team is accessing a foreign table in batches of 200 rows, but the problem they are facing is that the query is taking different times for the same set of rows.
The foreign table is only 1 shard with a partition of 21, and the partition we are using is a hash partition.

What can I do at the database side to debug this or any configuration parameter related to this wired behavior?

Have a Good day !!!

Regards
Rohit Sachdeva


--
Have a Good day !!!

Regards
Rohit Sachdeva