Re: clear cache in postgresql

[Vijaykumar Jain]

On Wed, 16 Jun 2021 at 12:09, Julien Rouhaud  wrote:
>
> On Wed, Jun 16, 2021 at 12:02:52PM +0530, Atul Kumar wrote:
> >
> > Sometimes I run a Postgres query it takes 30 seconds. Then, I
> > immediately run the same query and it takes 2 seconds. It appears that
> > Postgres has some sort of caching. Can I somehow see what that cache
> > is holding?
>
> You can use pgbuffercache for that:
> https://www.postgresql.org/docs/current/pgbuffercache.html
>
pgbuffercache is useful, but you also need to check the explain plan to
help understand if it just cache issue or something else.
i mean for cases where change in buffers hit vs buffers read, then maybe
cache is helping.
when you have time, you can find this very helpful.
pg internals 

test=# create table t(id int, name text, primary key(id));
CREATE TABLE
test=# insert into t select x, md5(x::text) from generate_series(1,
100) x;
INSERT 0 100
test=# explain (analyze,buffers) select * from t where id < 1;
QUERY PLAN
--
 Bitmap Heap Scan on t  (cost=6394.67..19138.75 rows=352806 width=36)
(actual time=0.433..1.750 rows= loops=1)
   Recheck Cond: (id < 1)
   Heap Blocks: exact=84
   Buffers: shared hit=1 read=113 dirtied=84
   ->  Bitmap Index Scan on t_pkey  (cost=0.00..6306.47 rows=352806
width=0) (actual time=0.408..0.408 rows= loops=1)
 Index Cond: (id < 1)
 Buffers: shared hit=1 read=29
 Planning:
   Buffers: shared hit=1 read=8
 Planning Time: 0.226 ms
 Execution Time: 2.233 ms
(11 rows)

test=# explain (analyze,buffers) select * from t where id < 1;
QUERY PLAN
--
 Bitmap Heap Scan on t  (cost=6394.67..19138.75 rows=352806 width=36)
(actual time=0.419..1.447 rows= loops=1)
   Recheck Cond: (id < 1)
   Heap Blocks: exact=84
   Buffers: shared hit=114
   ->  Bitmap Index Scan on t_pkey  (cost=0.00..6306.47 rows=352806
width=0) (actual time=0.399..0.400 rows= loops=1)
 Index Cond: (id < 1)
 Buffers: shared hit=30
 Planning Time: 0.145 ms
 Execution Time: 1.921 ms
(9 rows)


and then check buffercache


SELECT n.nspname, c.relname, count(*) AS buffers
 FROM pg_buffercache b JOIN pg_class c
 ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
  WHERE datname = current_database()))
 JOIN pg_namespace n ON n.oid = c.relnamespace
 GROUP BY n.nspname, c.relname
 ORDER BY 3 DESC
 LIMIT 5;
  nspname   |  relname  | buffers
+---+-
 public | t |3004
 public | t_pkey| 816
 pg_catalog | pg_proc   |  20
 pg_catalog | pg_depend_reference_index |  13
 pg_catalog | pg_attribute  |  13


> > Can I force all caches to be cleared for tuning purposes?
> > So I need to clear the cache without restarting/rebooting the postgres
> > server to check the correct execution plan of my query.
>
> No, cleaning postgres cache can only be done with a service restart.  That
> being said, tuning shouldn't be done assuming that there's no cache.  On
the
> opposite maybe what you should do is use pg_prewarm
> (https://www.postgresql.org/docs/current/pgprewarm.html) to make sure
that your
> cache isn't empty after a restart.
>


yes, flushing cache requires a restart. but you can also flush cache, by
repeatedly querying huge tables which are much larger than your buffers and
not relevant to your query,
that way the cache contents would be replaced by new content.

also if you can play around by logging onto your machine (do not do in
production, it will flush all cache which may be in use elsewhere)  as root.

stop postgresql
sync
echo 3 > /proc/sys/vm/drop_caches
start postgresql

you can try by keeping the shared_buffers/effective_cache_size value low,
to ensure not a lot of pages are pulled up into the buffers.


Thanks,
Vijay
Mumbai, India

Re: clear cache in postgresql

[Julien Rouhaud]

On Wed, Jun 16, 2021 at 12:02:52PM +0530, Atul Kumar wrote:
> 
> Sometimes I run a Postgres query it takes 30 seconds. Then, I
> immediately run the same query and it takes 2 seconds. It appears that
> Postgres has some sort of caching. Can I somehow see what that cache
> is holding?

You can use pgbuffercache for that:
https://www.postgresql.org/docs/current/pgbuffercache.html

> Can I force all caches to be cleared for tuning purposes?
> So I need to clear the cache without restarting/rebooting the postgres
> server to check the correct execution plan of my query.

No, cleaning postgres cache can only be done with a service restart.  That
being said, tuning shouldn't be done assuming that there's no cache.  On the
opposite maybe what you should do is use pg_prewarm
(https://www.postgresql.org/docs/current/pgprewarm.html) to make sure that your
cache isn't empty after a restart.

Note that you also have the operating system cache.  It can be cleared without
restarting the OS, but I have no idea if this is possible with RDS.

clear cache in postgresql

[Atul Kumar]

Hi,

I have an postgres 10 instance on RDS.

Sometimes I run a Postgres query it takes 30 seconds. Then, I
immediately run the same query and it takes 2 seconds. It appears that
Postgres has some sort of caching. Can I somehow see what that cache
is holding? Can I force all caches to be cleared for tuning purposes?

So I need to clear the cache without restarting/rebooting the postgres
server to check the correct execution plan of my query.

Please help me with your suggestions.




Regards.

Re: query issue

[Atul Kumar]

Hi,

Please find below the details you asked for:

Relation size
1986 MB

table count - 1407721

We have removed few indexes.


Query -


QUERY PLAN
Limit  (cost=0.43..5529.03 rows=10 width=37) (actual
time=0.974..12911.087 rows=10 loops=1)
  Output: items._id
  Buffers: shared hit=4838 read=3701
  ->  Subquery Scan on items  (cost=0.43..1622646.30 rows=2935
width=37) (actual time=0.972..12911.078 rows=10 loops=1)
Output: items._id
Buffers: shared hit=4838 read=3701
->  Index Scan using sort on
"op_KFDaBAZDSXc4YYts9"."UserFeedItems"  (cost=0.43..1622616.95
rows=2935 width=50) (actual time=0.970..12911.070 rows=10 loops=1)
  Output: "UserFeedItems"._id, "UserFeedItems".score,
"UserFeedItems"."updatedAt"
  Filter: (("UserFeedItems".is_deleted = ANY
('{t,f}'::boolean[])) AND ("UserFeedItems"."userId" =
'5d230d67bd99c5001b1ae757'::text) AND ("UserFeedItems"."itemType" <>
ALL ('{WELCOME_POST,UPLOAD_CONTACTS,BROADCAST_POST}'::text[])))
  Rows Removed by Filter: 15478
  Buffers: shared hit=4838 read=3701
Planning time: 100.949 ms
Execution time: 12930.302 ms








QUERY PLAN
Limit  (cost=0.43..5529.03 rows=10 width=37) (actual
time=0.974..12911.087 rows=10 loops=1)
  Output: items._id
  Buffers: shared hit=4838 read=3701
  ->  Subquery Scan on items  (cost=0.43..1622646.30 rows=2935
width=37) (actual time=0.972..12911.078 rows=10 loops=1)
Output: items._id
Buffers: shared hit=4838 read=3701
->  Index Scan using sort on
"op_KFDaBAZDSXc4YYts9"."UserFeedItems"  (cost=0.43..1622616.95
rows=2935 width=50) (actual time=0.970..12911.070 rows=10 loops=1)
  Output: "UserFeedItems"._id, "UserFeedItems".score,
"UserFeedItems"."updatedAt"
  Filter: (("UserFeedItems".is_deleted = ANY
('{t,f}'::boolean[])) AND ("UserFeedItems"."userId" =
'5d230d67bd99c5001b1ae757'::text) AND ("UserFeedItems"."itemType" <>
ALL ('{WELCOME_POST,UPLOAD_CONTACTS,BROADCAST_POST}'::text[])))
  Rows Removed by Filter: 15478
  Buffers: shared hit=4838 read=3701
Planning time: 100.949 ms
Execution time: 12930.302 ms





Please share your suggestions.





Regards,
Atul









On 6/15/21, Jehan-Guillaume de Rorthais  wrote:
> On Tue, 15 Jun 2021 19:16:41 +0530
> Atul Kumar  wrote:
>
>> hi,
>>
>> I have an RDS instance with 2GB of RAM, 1 CPU, instance class - t2.small.
>>
>> If you need any more info please let me know.
>>
>> and as you shared I need to tweak
>> random_page_cost/seq_page_cost/effective_cache_size So please suggest
>> which parameter value I need to increase or decrease as I am known
>> well with these parameters.
>
> First, did you test with "SELECT _id" instead of "SELECT *" ?
>
> About rand_page_costs/effective_cache_size, the fine manual already give
> some
> explanations and tips:
> https://www.postgresql.org/docs/current/runtime-config-query.html
>
> With such a low setup, I'm not sure what you can expect though. What is the
> concurrency? How many lines in total? The table size?
>

Re: some questions regarding replication issues and timeline/history files

[Kyotaro Horiguchi]

At Tue, 15 Jun 2021 07:05:07 -0700 (MST), "email2ssk...@gmail.com" 
 wrote in 
> Even I have this problem when I had to recover the database failed
> switchover.
> This is error is new primary server. 
> 
> < 2021-06-15 16:05:02.480 CEST > ERROR:  requested starting point
> AF/7D00 on timeline 1 is not in this server's history
> < 2021-06-15 16:05:02.480 CEST > DETAIL:  This server's history forked from
> timeline 1 at AF/7C0F8D58.

Your old primary looks like having continued running beyond 7D00
after the old standby promoted at 7C0F8D58.  In short, the new standby
experienced a diverged history from the new primary.

You can use pg_rewind to adust the new standby sever in that case.


FYI, you can reproduce the error by the folowing steps.

1. create a primary  (A)
2. create a standby  (B) connecting to A.
3. promote B.

4. connecting to A and run the following commands.

   =# select pg_switch_wal(); checkpoint;

5. stop A, then add primary_conninfo connecting to B to the conf file
   of A,then create the standby.signal file in the data directory of
   A.

6. You will get the similar error.


To recover from the sitaution, run pg_rewind like the follows, for example.

$ pg_rewind --target_pgdata= --target-server='connstr to B'
pg_rewind: servers diverged at WAL location 0/360 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/260 on timeline 1


regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: pg_basebackup fails with "COPY stream ended"

[Julien Rouhaud]

On Tue, Jun 15, 2021 at 09:53:45PM -0700, Dipanjan Das wrote:
> 
> I am running "pg_basebackup -h  -U postgres -D  -X stream". It
> fails with either of the following two error messages:
> [...]
> WARNING:  terminating connection because of crash of another server process
> DETAIL:  The postmaster has commanded this server process to roll back the
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted shared memory.

Do you have frequent emergency restarts like that?  You should start by
investigating on why this is happening.  The logs should tell you what process
crashed and what it was executing, could you report those messages?

We will also probably need a backtrace, see
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Getting_a_trace_from_a_randomly_crashing_backend
on how do to that.

pg_basebackup fails with "COPY stream ended"

[Dipanjan Das]

Hi,

I am running "pg_basebackup -h  -U postgres -D  -X stream". It
fails with either of the following two error messages:

ERROR: Backup failed copying files.
DETAILS: data transfer failure on directory
'/mnt/data/barman/base/20210615T212304/data'
pg_basebackup error:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
pg_basebackup: error: could not read COPY data: SSL SYSCALL error: EOF
detected
pg_basebackup: removing contents of data directory
"/mnt/data/barman/base/20210615T212304/data"

OR,

ERROR: Backup failed copying files.
DETAILS: data transfer failure on directory
'/mnt/data/barman/base/20210615T212849/data'
pg_basebackup error:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: error: COPY stream ended before last file was finished
pg_basebackup: removing contents of data directory
"/mnt/data/barman/base/20210615T212849/data"

I am running Postgres 12.7 on Ubuntu 20.04. Can anyone please help me
figure out what's going wrong?

Re: hot_standby_feedback implementation

[Christophe Pettus]

> On Jun 15, 2021, at 17:30, Peter Geoghegan  wrote:
> It pretty much works by making the WAL sender process on the primary
> look like it holds a snapshot that's as old as the oldest snapshot on
> the replica.
> 
> A replica can block VACUUM on the primary *directly* by holding a
> table-level lock, though in practice only when somebody on the replica
> acquires an AccessExclusiveLock -- which is presumably rare in
> practice.

Thank you!  Attempting to track down a sudden burst of every session waiting on 
lock_manager LW locks, and part of that is finding the source of locks.

Re: hot_standby_feedback implementation

[Peter Geoghegan]

On Tue, Jun 15, 2021 at 5:24 PM Christophe Pettus  wrote:
> When a replica sends a hot_standby_feedback message to the primary, does that 
> create an entry in the primary's lock table, or is it flagged to autovacuum 
> some other way?

It pretty much works by making the WAL sender process on the primary
look like it holds a snapshot that's as old as the oldest snapshot on
the replica.

A replica can block VACUUM on the primary *directly* by holding a
table-level lock, though in practice only when somebody on the replica
acquires an AccessExclusiveLock -- which is presumably rare in
practice.

-- 
Peter Geoghegan

hot_standby_feedback implementation

[Christophe Pettus]

When a replica sends a hot_standby_feedback message to the primary, does that 
create an entry in the primary's lock table, or is it flagged to autovacuum 
some other way?

Re: CONCAT function adding extra characters

[AI Rumman]

I saw that problem when I was running the query from DBeaver.
Got my answer.

Thanks & Regards.

On Tue, Jun 15, 2021 at 12:18 PM Pavel Stehule 
wrote:

>
>
> út 15. 6. 2021 v 21:07 odesílatel Tom Lane  napsal:
>
>> AI Rumman  writes:
>> > I am using Postgresql 10 and seeing a strange behavior in CONCAT
>> function
>> > when I am concatenating double precision and int with a separator.
>>
>> > select concat('41.1'::double precision,':', 20);
>> >> Result:
>> >> 41.1014:20
>>
>> What have you got extra_float_digits set to?
>>
>>
> postgres=# set extra_float_digits to 3;
> SET
> postgres=# select concat('41.1'::double precision,':', 20);
> ┌┐
> │ concat │
> ╞╡
> │ 41.1014:20 │
> └┘
> (1 row)
>
> Pavel
>
>
>
>> regards, tom lane
>>
>>
>>

Re: some questions regarding replication issues and timeline/history files

[Mateusz Henicz]

Do you have "recovery_target_timeline=latest" configured in your
recovery.conf or postgresql.conf? Depending on the version you are using,
up to 11 recovery.conf and postgresql.conf 12+.

Cheers,
Mateusz

wt., 15 cze 2021, 22:05 użytkownik email2ssk...@gmail.com <
email2ssk...@gmail.com> napisał:

> Even I have this problem when I had to recover the database failed
> switchover.
> This is error is new primary server.
>
> < 2021-06-15 16:05:02.480 CEST > ERROR:  requested starting point
> AF/7D00 on timeline 1 is not in this server's history
> < 2021-06-15 16:05:02.480 CEST > DETAIL:  This server's history forked from
> timeline 1 at AF/7C0F8D58.
>
>
>
> --
> Sent from:
> https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

Re: CONCAT function adding extra characters

[Ron]

On 6/15/21 1:55 PM, AI Rumman wrote:
I am using Postgresql 10 and seeing a strange behavior in CONCAT function 
when I am concatenating double precision and int with a separator.


select concat('41.1'::double precision,':', 20);
Result:
41.1014:20


Value 41.1 which double precision converts to 41.100014.

Is that expected?


Because of the well-known difficulty in precisely converting floating point 
to decimal, in cases like this, I always cast to NUMERIC of the desired 
precision.  It's the COBOL programmer in me...


--
Angular momentum makes the world go 'round.

Re: some questions regarding replication issues and timeline/history files

[email2ssk...@gmail.com]

Even I have this problem when I had to recover the database failed
switchover.
This is error is new primary server. 

< 2021-06-15 16:05:02.480 CEST > ERROR:  requested starting point
AF/7D00 on timeline 1 is not in this server's history
< 2021-06-15 16:05:02.480 CEST > DETAIL:  This server's history forked from
timeline 1 at AF/7C0F8D58.



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: CONCAT function adding extra characters

[Pavel Stehule]

út 15. 6. 2021 v 21:07 odesílatel Tom Lane  napsal:

> AI Rumman  writes:
> > I am using Postgresql 10 and seeing a strange behavior in CONCAT function
> > when I am concatenating double precision and int with a separator.
>
> > select concat('41.1'::double precision,':', 20);
> >> Result:
> >> 41.1014:20
>
> What have you got extra_float_digits set to?
>
>
postgres=# set extra_float_digits to 3;
SET
postgres=# select concat('41.1'::double precision,':', 20);
┌┐
│ concat │
╞╡
│ 41.1014:20 │
└┘
(1 row)

Pavel



> regards, tom lane
>
>
>

Re: CONCAT function adding extra characters

[Kenneth Marshall]

> út 15. 6. 2021 v 20:56 odesílatel AI Rumman  napsal:

> I am using Postgresql 10 and seeing a strange behavior in CONCAT function
> when I am concatenating double precision and int with a separator.
>
> select concat('41.1'::double precision,':', 20);
>> Result:
>> 41.1014:20
>
>
> Value 41.1 which double precision converts to 41.100014.
>
> Is that expected?
>

Hi

0.1 cannot be represented exactly in binary so that does not look out of
line. There are also some config options for extra digits and what not
that may affect the result of a cast.

Regards,
Ken

Re: CONCAT function adding extra characters

[Tom Lane]

AI Rumman  writes:
> I am using Postgresql 10 and seeing a strange behavior in CONCAT function
> when I am concatenating double precision and int with a separator.

> select concat('41.1'::double precision,':', 20);
>> Result:
>> 41.1014:20

What have you got extra_float_digits set to?

regards, tom lane

Re: CONCAT function adding extra characters

[Adrian Klaver]

On 6/15/21 11:55 AM, AI Rumman wrote:
I am using Postgresql 10 and seeing a strange behavior in CONCAT 
function when I am concatenating double precision and int with a separator.


select concat('41.1'::double precision,':', 20);
Result:
41.1014:20


Value 41.1 which double precision converts to 41.100014.

Is that expected?


Aah, too quick on the trigger.

Also:

Is there a home brewed version of CONCAT() in the search_path?



Thanks.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: CONCAT function adding extra characters

[Adrian Klaver]

On 6/15/21 11:55 AM, AI Rumman wrote:
I am using Postgresql 10 and seeing a strange behavior in CONCAT 
function when I am concatenating double precision and int with a separator.


select concat('41.1'::double precision,':', 20);
Result:
41.1014:20


Value 41.1 which double precision converts to 41.100014.

Is that expected?


No.

What OS and version of same?

How was Postgres installed?



Thanks.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: CONCAT function adding extra characters

[Pavel Stehule]

Hi

út 15. 6. 2021 v 20:56 odesílatel AI Rumman  napsal:

> I am using Postgresql 10 and seeing a strange behavior in CONCAT function
> when I am concatenating double precision and int with a separator.
>
> select concat('41.1'::double precision,':', 20);
>> Result:
>> 41.1014:20
>
>
> Value 41.1 which double precision converts to 41.100014.
>
> Is that expected?
>

this is strange

postgres=# select concat('41.1'::double precision,':', 20);
┌─┐
│ concat  │
╞═╡
│ 41.1:20 │
└─┘
(1 row)

postgres=# select version();
┌───┐
│  version
 │
╞═══╡
│ PostgreSQL 10.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1
20210428 (Red Hat 11.1.1-1), 64-bit │
└───┘
(1 row)

Regards

Pavel


> Thanks.
>

CONCAT function adding extra characters

[AI Rumman]

I am using Postgresql 10 and seeing a strange behavior in CONCAT function
when I am concatenating double precision and int with a separator.

select concat('41.1'::double precision,':', 20);
> Result:
> 41.1014:20


Value 41.1 which double precision converts to 41.100014.

Is that expected?

Thanks.

Re: [ext] Re: Losing data because of problematic configuration?

[Holtgrewe, Manuel]

>> < 2021-06-15 12:33:04.537 CEST > DEBUG:  resetting unlogged relations: 
>> cleanup 1 init 0
>
> Are you perhaps keeping your data in an UNLOGGED table?  If so, resetting
> it to empty after a crash is exactly what's supposed to happen.  The
> entire point of UNLOGGED is that the performance benefits come at the
> cost of losing the data on crash.


D'oh!


Yes, that is the case. I once used this but I was convinced that I took this 
back in some version. It is not in my main deployment, though. Now I have to 
find out (a) why I have diverging deployment and (b) how that bug came about to 
be.


Thanks a lot!


From: Tom Lane 
Sent: Tuesday, June 15, 2021 3:39:31 PM
To: Holtgrewe, Manuel
Cc: pgsql-general@lists.postgresql.org
Subject: [ext] Re: Losing data because of problematic configuration?

"Holtgrewe, Manuel"  writes:
> So it looks as if the database jumps back "half an hour" to ensure consistent 
> data. Everything in between is lost.

Postgres does not lose committed data --- if it did, we'd consider that a
fairly serious bug.  (Well, there are caveats of course.  But most of them
have to do with operating-system crashes or power loss, neither of which
are at stake here.)

I am wondering about this though:

> < 2021-06-15 12:33:04.537 CEST > DEBUG:  resetting unlogged relations: 
> cleanup 1 init 0

Are you perhaps keeping your data in an UNLOGGED table?  If so, resetting
it to empty after a crash is exactly what's supposed to happen.  The
entire point of UNLOGGED is that the performance benefits come at the
cost of losing the data on crash.

regards, tom lane

Re: query issue

[Jehan-Guillaume de Rorthais]

On Tue, 15 Jun 2021 19:16:41 +0530
Atul Kumar  wrote:

> hi,
> 
> I have an RDS instance with 2GB of RAM, 1 CPU, instance class - t2.small.
> 
> If you need any more info please let me know.
> 
> and as you shared I need to tweak
> random_page_cost/seq_page_cost/effective_cache_size So please suggest
> which parameter value I need to increase or decrease as I am known
> well with these parameters.

First, did you test with "SELECT _id" instead of "SELECT *" ?

About rand_page_costs/effective_cache_size, the fine manual already give some
explanations and tips:
https://www.postgresql.org/docs/current/runtime-config-query.html

With such a low setup, I'm not sure what you can expect though. What is the
concurrency? How many lines in total? The table size?

Re: query issue

[Atul Kumar]

hi,

I have an RDS instance with 2GB of RAM, 1 CPU, instance class - t2.small.

If you need any more info please let me know.

and as you shared I need to tweak
random_page_cost/seq_page_cost/effective_cache_size So please suggest
which parameter value I need to increase or decrease as I am known
well with these parameters.





Regards.








On 6/15/21, Jehan-Guillaume de Rorthais  wrote:
> On Tue, 15 Jun 2021 16:12:11 +0530
> Atul Kumar  wrote:
>
>> Hi,
>>
>> I have postgres 10 running on RDS instance.
>>
>> I have query below:
> [...]
>>
>> So my doubt is initially when I run this query it takes around 42
>> seconds to complete but later after few minutes it completes in 2-3
>> seconds.
>>
>> I tried to create indexes on table for columns score & "updatedAt"
>> DESC seperately but found no proper satisfied solution.
>>
>> So please help me telling what I am exactly missing here ?
>
> The worst part of your plan is the Bitmap Heap Scan, where the plan is
> actually
> fetching the rows from the table. The bitmap index scan and sort are fast.
> There's not much to do about them.
>
> This query need to fetch 3882 rows from your table. So either the fetching
> part
> of the plan is really, really slow (IO/CPU bound), or the simple filter, on
> only
> ~4k, is really slow (CPU bound).
>
> You might want to avoid "SELECT *" and only specify the fields you really
> need.
> Try first with only "SELECT _id", just to compare. You have an average row
> size
> of 1.3k that the executor need to fetch and carry all the way to the result
> set.
> This can cost a lot of useless IO and CPU.
>
> You might want to tweak random_page_cost/seq_page_cost/effective_cache_size
> to
> find out if an index scan would do a better job, but I'm unsure because I
> lack
> of informations about your data and system.
>
> Regards,
>

Re: Memory alloc exception

[Tom Lane]

 writes:
> I get this error when running a SQL statement in my Java application.
> ERROR: Invalid memory alloc request size 1683636507

This is a pretty common symptom of corrupt data (specifically, that the
length word of a variable-length field is garbage).  More than that
can't be said with the info you've provided.  But I'd be thinking about
"how do I clean up the corruption and prevent a recurrence", not
"I need to increase memory settings".

regards, tom lane

Re: Losing data because of problematic configuration?

[Tom Lane]

"Holtgrewe, Manuel"  writes:
> So it looks as if the database jumps back "half an hour" to ensure consistent 
> data. Everything in between is lost.

Postgres does not lose committed data --- if it did, we'd consider that a
fairly serious bug.  (Well, there are caveats of course.  But most of them
have to do with operating-system crashes or power loss, neither of which
are at stake here.)

I am wondering about this though:

> < 2021-06-15 12:33:04.537 CEST > DEBUG:  resetting unlogged relations: 
> cleanup 1 init 0

Are you perhaps keeping your data in an UNLOGGED table?  If so, resetting
it to empty after a crash is exactly what's supposed to happen.  The
entire point of UNLOGGED is that the performance benefits come at the
cost of losing the data on crash.

regards, tom lane

Re: immutable function querying table for partitioning

[Vijaykumar Jain]

On Tue, 15 Jun 2021 at 18:21, David G. Johnston 
wrote:

> You probably avoid the complications by doing the above, but the amount
of bloat you are causing seems excessive.
>
> I’d suggest an approach where you use the table data to build DDL in a
form that does adhere to the limitations described in the documentation
instead of building you system upon a lie that your immutable function is
actually a stable one.
>
> David J.

Yes I do understand that. That was just  a simple example.
I am just playing with approaches, this is not a work requirement.
This would be a simulation for sharded setup across servers.
In reality I would be moving only the relevant data and run a truncate to
get rid of bloat once data is moved. but that was not the point.

I wanted sharding to be an iterative setup. as I will make bad decisions.
I want to be able to avoid hotspots as well as, too much scatter gather.
I tried the fdw approach, the problem with it is , it opens a cursor, then
runs through all the shards sequentially. when we have too many shards, it
suffers linear degradation.

I also tried dblink to run an async scatter gather where aggregation/ limit
could not be pushed down.

and it has a lot counting on fetch_size across shards. I mean if it too
low, it takes forever to get data that does not get reduced at remote
server.
it is too high the transfer is fast, but  random query result transferring
a lot of data results in oom. I know i have to tune these things, but i
cannot anticipate data growth.

I was thinking of a way where I could have all related data in one
partition.
i mean if i am having a setup with highly normalized tables, i could use a
custom function to ensure all related data remains in one shard.

while studying approaches to how others shard, i saw this in oracle. when
all related data is in one shard which uses partition by reference,
since we do not have that in postgres, i thought i could use a custom
function.
sharding-schema-design.html

but these are experiments. I am not losing anything if I fail :)
I want to figure out  ways to rebalance without downtime.


-- 
Thanks,
Vijay
Mumbai, India

Re: immutable function querying table for partitioning

[David G. Johnston]

On Tuesday, June 15, 2021, Vijaykumar Jain 
wrote:

>
>
> --- now since the lookup table is update, a noop update would get new
> shards for ids and rebalance them accordingly.
>
> test=# update t set id = id ;
> UPDATE 25
>

You probably avoid the complications by doing the above, but the amount of
bloat you are causing seems excessive.

I’d suggest an approach where you use the table data to build DDL in a form
that does adhere to the limitations described in the documentation instead
of building you system upon a lie that your immutable function is actually
a stable one.

David J.

Re: [ext] Re: Losing data because of problematic configuration?

[Holtgrewe, Manuel]

Hi,


thanks for your answer.


Let me give some background. I have a postgres instance that serves as the data 
storage for a web-based data analytics application. For some queries, I'm 
seeing postgres going OOM because the query grows too large and subsequently 
the linux kernel kills the postgres system. I'm then observing similar log 
messages and data loss. I'm now trying to reproduce this behaviour in a more 
deterministic way to learn about the root cause and resolve the issue.


The bulk import is not running inside of a transaction for performance reasons. 
My understanding is that in case of crashes I might end up with partial data 
(that I take care of in the application). However, I would not expect rollback 
behaviour a few minutes after the bulk import went through correctly.


FWIW, the analytics application also allows users to annotate the data and 
these annotations are written to the database in transactions.


So to answer your questions:


> What kind of transaction did you use?


No transaction for bulk import. Also, bulk import completed minutes before the 
kill. After the bulk import, a number of transactions are performed touching 
different tables.


> Did you commit the transaction?


The bulk import was not done in a transaction. The other transactions were 
committed through the database access framework I'm using in my (Python/django) 
application.


> Why?


To reproduce the problematic beahaviour that I'm seeing in my application.


Does this help? Where could I look for understanding this better?


Thanks,

Manuel



From: Ron 
Sent: Tuesday, June 15, 2021 13:07
To: pgsql-general@lists.postgresql.org
Subject: [ext] Re: Losing data because of problematic configuration?

On 6/15/21 5:42 AM, Holtgrewe, Manuel wrote:

Hi,


I have a database that is meant to have high-performance for bulk insert 
operations. I've attached my postgres.conf file.


However, I'm seeing the following behaviour. At around 12:04, I have started 
the database. Then, I did a bulk insert and that completed.

What kind of transaction did you use?
Did you commit the transaction?


I then went on to kill postgres processes at 12:33 with SIGSEGV signal.

Why?

Did you CHECKPOINT beforehand?

(I'm hypothesizing that data didn't get flushed to disk, and so Pg "cleaned 
itself up" after the crash.)


I'm getting the following log excerpt:


< 2021-06-15 12:33:03.656 CEST > LOG:  database system was interrupted; last 
known up at 2021-06-15 12:04:13 CEST
< 2021-06-15 12:33:03.656 CEST > DEBUG:  removing all temporary WAL segments
< 2021-06-15 12:33:04.525 CEST > DEBUG:  checkpoint record is at 60/7C377C78
[snip]
< 2021-06-15 12:33:04.537 CEST > DEBUG:  resetting unlogged relations: cleanup 
1 init 0
< 2021-06-15 12:33:04.553 CEST > DEBUG:  unlinked file "base/16384/107877"
[... snip ... ]
< 2021-06-15 12:33:27.556 CEST > DEBUG:  copying base/16384/80948_init to 
base/16384/80948
< 2021-06-15 12:33:36.705 CEST > DEBUG:  performing replication slot checkpoint
< 2021-06-15 12:33:38.394 CEST > DEBUG:  attempting to remove WAL segments 
older than log file 0060007C
< 2021-06-15 12:33:38.394 CEST > DEBUG:  removing write-ahead log file 
"00010060007C"
< 2021-06-15 12:33:38.403 CEST > DEBUG:  MultiXactId wrap limit is 2147483648, 
limited by database with OID 1
< 2021-06-15 12:33:38.419 CEST > DEBUG:  oldest MultiXactId member is at offset 
1
< 2021-06-15 12:33:38.419 CEST > DEBUG:  MultiXact member stop limit is now 
4294914944 based on MultiXact 1
< 2021-06-15 12:33:38.428 CEST > DEBUG:  shmem_exit(0): 1 before_shmem_exit 
callbacks to make
< 2021-06-15 12:33:38.428 CEST > DEBUG:  shmem_exit(0): 5 on_shmem_exit 
callbacks to make


So it looks as if the database jumps back "half an hour" to ensure consistent 
data. Everything in between is lost.


What would be configuration settings to look into to make this more stable? Is 
there a way to "force flush state to disk" from the application/through 
postgres API/SQL?


Thank you,

Manuel


--
Angular momentum makes the world go 'round.

Re: query issue

[Jehan-Guillaume de Rorthais]

On Tue, 15 Jun 2021 16:12:11 +0530
Atul Kumar  wrote:

> Hi,
> 
> I have postgres 10 running on RDS instance.
> 
> I have query below:
[...]
> 
> So my doubt is initially when I run this query it takes around 42
> seconds to complete but later after few minutes it completes in 2-3
> seconds.
> 
> I tried to create indexes on table for columns score & "updatedAt"
> DESC seperately but found no proper satisfied solution.
> 
> So please help me telling what I am exactly missing here ?

The worst part of your plan is the Bitmap Heap Scan, where the plan is actually
fetching the rows from the table. The bitmap index scan and sort are fast.
There's not much to do about them.

This query need to fetch 3882 rows from your table. So either the fetching part
of the plan is really, really slow (IO/CPU bound), or the simple filter, on only
~4k, is really slow (CPU bound).

You might want to avoid "SELECT *" and only specify the fields you really need.
Try first with only "SELECT _id", just to compare. You have an average row size
of 1.3k that the executor need to fetch and carry all the way to the result set.
This can cost a lot of useless IO and CPU.

You might want to tweak random_page_cost/seq_page_cost/effective_cache_size to
find out if an index scan would do a better job, but I'm unsure because I lack
of informations about your data and system.

Regards,

Re: Memory alloc exception

[Ron]

On 6/15/21 6:09 AM, paul.m...@lfv.se wrote:


Hi list,

I get this error when running a SQL statement in my Java application.

ERROR: Invalid memory alloc request size 1683636507

Location: File: 
d:\pginstaller.auto\postgres.windows-x64\src\backend\utils\mmgr\mcxt.c, 
Routine: MemoryContextAlloc, Line: 779


Server SQLState: XX000



Is Postgresql running on Windows?

What version?


I think it has something with to do with PostgreSQL

This is how I had my postgrsql.conf:

shared_buffers = 128MB

#huge_pages = try

#temp_buffers = 8MB

#max_prepared_transactions = 0

#work_mem = 6MB

work_mem = 64MB

#maintenance_work_mem = 64MB

maintenance_work_mem =  64MB

Then I changed to this postgrsql.conf:

shared_buffers = 4096MB <<<,

#huge_pages = try

#temp_buffers = 8MB

#max_prepared_transactions = 0

#work_mem = 6MB

work_mem = 256MB   <<<

#maintenance_work_mem = 64MB

maintenance_work_mem =  256MB <<

And got this error instead:

Org.postgresql.util.PSQLException: AnI/O error occurred while sending to 
the backend.




Failing hardware?


--
Angular momentum makes the world go 'round.

Memory alloc exception

[paul.malm]

Hi list,
I get this error when running a SQL statement in my Java application.
ERROR: Invalid memory alloc request size 1683636507
Location: File: 
d:\pginstaller.auto\postgres.windows-x64\src\backend\utils\mmgr\mcxt.c, 
Routine: MemoryContextAlloc, Line: 779
Server SQLState: XX000

I think it has something with to do with PostgreSQL
This is how I had my postgrsql.conf:
shared_buffers = 128MB
#huge_pages = try
#temp_buffers = 8MB
#max_prepared_transactions = 0
#work_mem = 6MB
work_mem = 64MB
#maintenance_work_mem = 64MB
maintenance_work_mem =  64MB

Then I changed to this postgrsql.conf:
shared_buffers = 4096MB
#huge_pages = try
#temp_buffers = 8MB
#max_prepared_transactions = 0
#work_mem = 6MB
work_mem = 256MB
#maintenance_work_mem = 64MB
maintenance_work_mem =  256MB

And got this error instead:
Org.postgresql.util.PSQLException: AnI/O error occurred while sending to the 
backend.

Any ideas?

Kind regards,
Paul

Re: Losing data because of problematic configuration?

[Ron]

On 6/15/21 5:42 AM, Holtgrewe, Manuel wrote:


Hi,


I have a database that is meant to have high-performance for bulk insert 
operations. I've attached my postgres.conf file.



However, I'm seeing the following behaviour. At around 12:04, I have 
started the database. Then, I did a bulk insert and that completed.




What kind of transaction did you use?
Did you commit the transaction?


I then went on to kill postgres processes at 12:33 with SIGSEGV signal.



Why?

Did you CHECKPOINT beforehand?

(I'm hypothesizing that data didn't get flushed to disk, and so Pg "cleaned 
itself up" after the crash.)



I'm getting the following log excerpt:


*< 2021-06-15 12:33:03.656 CEST > LOG:  database system was interrupted; 
last known up at 2021-06-15 12:04:13 CEST*

< 2021-06-15 12:33:03.656 CEST > DEBUG:  removing all temporary WAL segments
< 2021-06-15 12:33:04.525 CEST > DEBUG:  checkpoint record is at 60/7C377C78
[snip]
< 2021-06-15 12:33:04.537 CEST > DEBUG:  resetting unlogged relations: 
cleanup 1 init 0

< 2021-06-15 12:33:04.553 CEST > DEBUG:  unlinked file "base/16384/107877"
[... snip ... ]
< 2021-06-15 12:33:27.556 CEST > DEBUG:  copying base/16384/80948_init to 
base/16384/80948
< 2021-06-15 12:33:36.705 CEST > DEBUG:  performing replication slot 
checkpoint
< 2021-06-15 12:33:38.394 CEST > DEBUG:  attempting to remove WAL segments 
older than log file 0060007C
< 2021-06-15 12:33:38.394 CEST > DEBUG:  removing write-ahead log file 
"00010060007C"
< 2021-06-15 12:33:38.403 CEST > DEBUG:  MultiXactId wrap limit is 
2147483648, limited by database with OID 1
< 2021-06-15 12:33:38.419 CEST > DEBUG:  oldest MultiXactId member is at 
offset 1
< 2021-06-15 12:33:38.419 CEST > DEBUG:  MultiXact member stop limit is 
now 4294914944 based on MultiXact 1
< 2021-06-15 12:33:38.428 CEST > DEBUG:  shmem_exit(0): 1 
before_shmem_exit callbacks to make
< 2021-06-15 12:33:38.428 CEST > DEBUG:  shmem_exit(0): 5 on_shmem_exit 
callbacks to make



So it looks as if the database jumps back "half an hour" to ensure 
consistent data. Everything in between is lost.



What would be configuration settings to look into to make this more 
stable? Is there a way to "force flush state to disk" from the 
application/through postgres API/SQL?



Thank you,

Manuel




--
Angular momentum makes the world go 'round.

Losing data because of problematic configuration?

[Holtgrewe, Manuel]

Hi,


I have a database that is meant to have high-performance for bulk insert 
operations. I've attached my postgres.conf file.


However, I'm seeing the following behaviour. At around 12:04, I have started 
the database. Then, I did a bulk insert and that completed. I then went on to 
kill postgres processes at 12:33 with SIGSEGV signal. I'm getting the following 
log excerpt:


< 2021-06-15 12:33:03.656 CEST > LOG:  database system was interrupted; last 
known up at 2021-06-15 12:04:13 CEST
< 2021-06-15 12:33:03.656 CEST > DEBUG:  removing all temporary WAL segments
< 2021-06-15 12:33:04.525 CEST > DEBUG:  checkpoint record is at 60/7C377C78
[snip]
< 2021-06-15 12:33:04.537 CEST > DEBUG:  resetting unlogged relations: cleanup 
1 init 0
< 2021-06-15 12:33:04.553 CEST > DEBUG:  unlinked file "base/16384/107877"
[... snip ... ]
< 2021-06-15 12:33:27.556 CEST > DEBUG:  copying base/16384/80948_init to 
base/16384/80948
< 2021-06-15 12:33:36.705 CEST > DEBUG:  performing replication slot checkpoint
< 2021-06-15 12:33:38.394 CEST > DEBUG:  attempting to remove WAL segments 
older than log file 0060007C
< 2021-06-15 12:33:38.394 CEST > DEBUG:  removing write-ahead log file 
"00010060007C"
< 2021-06-15 12:33:38.403 CEST > DEBUG:  MultiXactId wrap limit is 2147483648, 
limited by database with OID 1
< 2021-06-15 12:33:38.419 CEST > DEBUG:  oldest MultiXactId member is at offset 
1
< 2021-06-15 12:33:38.419 CEST > DEBUG:  MultiXact member stop limit is now 
4294914944 based on MultiXact 1
< 2021-06-15 12:33:38.428 CEST > DEBUG:  shmem_exit(0): 1 before_shmem_exit 
callbacks to make
< 2021-06-15 12:33:38.428 CEST > DEBUG:  shmem_exit(0): 5 on_shmem_exit 
callbacks to make


So it looks as if the database jumps back "half an hour" to ensure consistent 
data. Everything in between is lost.


What would be configuration settings to look into to make this more stable? Is 
there a way to "force flush state to disk" from the application/through 
postgres API/SQL?


Thank you,

Manuel



postgres.conf
Description: postgres.conf
< 2021-06-15 12:33:03.104 CEST > DEBUG:  reaping dead processes
< 2021-06-15 12:33:03.105 CEST > LOG:  system logger process (PID 26) was terminated by signal 11: Segmentation fault
< 2021-06-15 12:33:03.524 CEST > DEBUG:  reaping dead processes
< 2021-06-15 12:33:03.542 CEST > DEBUG:  reaping dead processes
< 2021-06-15 12:33:03.560 CEST > DEBUG:  reaping dead processes
< 2021-06-15 12:33:03.595 CEST > DEBUG:  reaping dead processes
< 2021-06-15 12:33:03.595 CEST > LOG:  all server processes terminated; reinitializing
< 2021-06-15 12:33:03.595 CEST > DEBUG:  shmem_exit(1): 0 before_shmem_exit callbacks to make
< 2021-06-15 12:33:03.595 CEST > DEBUG:  shmem_exit(1): 6 on_shmem_exit callbacks to make
< 2021-06-15 12:33:03.595 CEST > DEBUG:  cleaning up orphaned dynamic shared memory with ID 321415882
< 2021-06-15 12:33:03.595 CEST > DEBUG:  cleaning up orphaned dynamic shared memory with ID 761384284
< 2021-06-15 12:33:03.595 CEST > DEBUG:  cleaning up dynamic shared memory control segment with ID 1236980165
< 2021-06-15 12:33:03.615 CEST > DEBUG:  invoking IpcMemoryCreate(size=209027072)
< 2021-06-15 12:33:03.615 CEST > DEBUG:  mmap(209715200) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
< 2021-06-15 12:33:03.654 CEST > DEBUG:  SlruScanDirectory invoking callback on pg_notify/
< 2021-06-15 12:33:03.654 CEST > DEBUG:  removing file "pg_notify/"
< 2021-06-15 12:33:03.654 CEST > DEBUG:  dynamic shared memory system will support 674 segments
< 2021-06-15 12:33:03.654 CEST > DEBUG:  created dynamic shared memory control segment 504513596 (16192 bytes)
< 2021-06-15 12:33:03.656 CEST > LOG:  database system was interrupted; last known up at 2021-06-15 12:04:13 CEST
< 2021-06-15 12:33:03.656 CEST > DEBUG:  removing all temporary WAL segments
< 2021-06-15 12:33:04.525 CEST > DEBUG:  checkpoint record is at 60/7C377C78
< 2021-06-15 12:33:04.526 CEST > DEBUG:  redo record is at 60/7C377C78; shutdown true
< 2021-06-15 12:33:04.526 CEST > DEBUG:  next transaction ID: 805913; next OID: 1004988
< 2021-06-15 12:33:04.526 CEST > DEBUG:  next MultiXactId: 140; next MultiXactOffset: 283
< 2021-06-15 12:33:04.526 CEST > DEBUG:  oldest unfrozen transaction ID: 479, in database 1
< 2021-06-15 12:33:04.526 CEST > DEBUG:  oldest MultiXactId: 1, in database 1
< 2021-06-15 12:33:04.526 CEST > DEBUG:  commit timestamp Xid oldest/newest: 0/0
< 2021-06-15 12:33:04.526 CEST > DEBUG:  transaction ID wrap limit is 2147484126, limited by database with OID 1
< 2021-06-15 12:33:04.526 CEST > DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
< 2021-06-15 12:33:04.526 CEST > DEBUG:  starting up replication slots
< 2021-06-15 12:33:04.526 CEST > DEBUG:  starting up replication origin progress state
< 2021-06-15 12:33:04.526 CEST > LOG:  database system was not properly shut down; automatic recovery in progress
< 2021-06-15 12:33:04.537 CEST > DEBUG:

query issue

[Atul Kumar]

Hi,

I have postgres 10 running on RDS instance.

I have query below:

select * from "op_KFDaBAZDSXc4YYts9"."UserFeedItems"
where (("itemType" not in ('WELCOME_POST', 'UPLOAD_CONTACTS',
'BROADCAST_POST')) and ("userId" = '5d230d67bd99c5001b1ae757' and
"is_deleted" in (true, false)))
order by "score" asc, "updatedAt" desc limit 10;


Explain plan is like given below:

QUERY PLAN
Limit  (cost=11058.03..11058.05 rows=10 width=1304) (actual
time=6105.283..6105.293 rows=10 loops=1)
  ->  Sort  (cost=11058.03..11065.36 rows=2935 width=1304) (actual
time=6105.281..6105.283 rows=10 loops=1)
Sort Key: score, "updatedAt" DESC
Sort Method: top-N heapsort  Memory: 36kB
->  Bitmap Heap Scan on "UserFeedItems"
(cost=131.33..10994.60 rows=2935 width=1304) (actual
time=26.245..6093.680 rows=3882 loops=1)
  Recheck Cond: ("userId" = '5d230d67bd99c5001b1ae757'::text)
  Filter: ((is_deleted = ANY ('{t,f}'::boolean[])) AND
("itemType" <> ALL
('{WELCOME_POST,UPLOAD_CONTACTS,BROADCAST_POST}'::text[])))
  Rows Removed by Filter: 1
  Heap Blocks: exact=3804
  ->  Bitmap Index Scan on "userId"  (cost=0.00..130.60
rows=2956 width=0) (actual time=24.835..24.836 rows=3885 loops=1)
Index Cond: ("userId" = '5d230d67bd99c5001b1ae757'::text)
Planning time: 20.928 ms
Execution time: 6108.610 ms



My table structure is somewhat like this

CREATE TABLE "op_KFDaBAZDSXc4YYts9"."UserFeedItems"
(
_id text COLLATE pg_catalog."default" NOT NULL DEFAULT uuid_generate_v4(),
"userId" text COLLATE pg_catalog."default" NOT NULL,
"itemType" text COLLATE pg_catalog."default" NOT NULL,
payload jsonb NOT NULL,
score numeric NOT NULL,
"generalFeedItemId" text COLLATE pg_catalog."default",
"createdAt" timestamp without time zone NOT NULL DEFAULT
(now())::timestamp without time zone,
"createdBy" text COLLATE pg_catalog."default",
"updatedAt" timestamp without time zone NOT NULL DEFAULT
(now())::timestamp without time zone,
"updatedBy" text COLLATE pg_catalog."default",
is_deleted boolean DEFAULT false,
"isRead" boolean NOT NULL DEFAULT false,
CONSTRAINT "UserFeedItems_pkey" PRIMARY KEY (_id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;


CREATE INDEX "UserFeedItems_id"
ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
(_id COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;
-- Index: UserFeedItems_itemType_userId_isdeleted_score_updatedAt

-- DROP INDEX 
"op_KFDaBAZDSXc4YYts9"."UserFeedItems_itemType_userId_isdeleted_score_updatedAt";

CREATE INDEX "UserFeedItems_itemType_userId_isdeleted_score_updatedAt"
ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
("itemType" COLLATE pg_catalog."default" ASC NULLS LAST, "userId"
COLLATE pg_catalog."default" ASC NULLS LAST, is_deleted ASC NULLS
LAST, score ASC NULLS LAST, "updatedAt" DESC NULLS FIRST)
TABLESPACE pg_default;
-- Index: score

-- DROP INDEX "op_KFDaBAZDSXc4YYts9".score;

CREATE INDEX score
ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
(score ASC NULLS LAST)
TABLESPACE pg_default;
-- Index: updatedat

-- DROP INDEX "op_KFDaBAZDSXc4YYts9".updatedat;

CREATE INDEX updatedat
ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
("updatedAt" DESC NULLS FIRST)
TABLESPACE pg_default;
-- Index: userId

-- DROP INDEX "op_KFDaBAZDSXc4YYts9"."userId";

CREATE INDEX "userId"
ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
("userId" COLLATE pg_catalog."default" text_pattern_ops ASC NULLS LAST)
TABLESPACE pg_default;





So my doubt is initially when I run this query it takes around 42
seconds to complete but later after few minutes it completes in 2-3
seconds.

I tried to create indexes on table for columns score & "updatedAt"
DESC seperately but found no proper satisfied solution.

So please help me telling what I am exactly missing here ?








Regards,
Atul

immutable function querying table for partitioning

[Vijaykumar Jain]

 hi,

I was playing around with a setup of having a lookup table for partitioning.
Basically, I wanted to be able to rebalance partitions based on my lookup
table.

-- create a lookup and assign shard nos to ids
test=# create table pt(id int, sh int);
CREATE TABLE
test=# insert into pt select x, 1 from generate_series(1, 10) x;
INSERT 0 10
test=# insert into pt select x, 2 from generate_series(10, 20) x;
INSERT 0 11
test=# insert into pt select x, 3  from generate_series(20, 30) x;
INSERT 0 11

test=# table pt;
 id | sh
+
  1 |  1
  2 |  1
  3 |  1
  4 |  1
  5 |  1
  6 |  1
  7 |  1
  8 |  1
  9 |  1
 10 |  1
 10 |  2
 11 |  2
 12 |  2
 13 |  2
 14 |  2
 15 |  2
 16 |  2
 17 |  2
 18 |  2
 19 |  2
 20 |  2
 20 |  3
 21 |  3
 22 |  3
 23 |  3
 24 |  3
 25 |  3
 26 |  3
 27 |  3
 28 |  3
 29 |  3
 30 |  3
(32 rows)

-- create a function that would lookup the id and return sh number

test=# create or replace function get_shard(t_id int) returns int as $$
test$# begin
test$# declare shno int;
test$# select distinct sh into shno from pt where id = t_id;
test$# return shno;
test$# end; $$ language plpgsql;
CREATE FUNCTION
test=# select get_shard(10);
 get_shard
---
 1
(1 row)

test=# select get_shard(30);
 get_shard
---
 3
(1 row)

-- ok it seems the function has to be immutable, so i am just making it one
-- despite querying a lookup table that would have contents changed later

test=# create table t (id int) partition by list ( get_shard(id) );
ERROR:  functions in partition key expression must be marked IMMUTABLE
test=# create or replace function get_shard(t_id int) returns int as $$
declare shno int;
begin
select distinct sh into shno from pt where id = t_id;
return shno;
end; $$ language plpgsql IMMUTABLE;
CREATE FUNCTION

-- partition table using the lookup function
test=# create table t (id int) partition by list ( get_shard(id) );
CREATE TABLE

-- if function returns 1 then partition t1 and so on
test=# create table t1 partition of t for values in ( 1 );
CREATE TABLE
test=# create table t2 partition of t for values in ( 2 );
CREATE TABLE
test=# create table t3 partition of t for values in ( 3 );
CREATE TABLE

-- validate partitioning is working by inserting data
test=# insert into t select x from generate_series(1, 25) x;
INSERT 0 25
test=# \dt t1
List of relations
 Schema | Name | Type  |  Owner
+--+---+--
 public | t1   | table | postgres
(1 row)

test=# table t1
test-# ;
 id

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t1;
 id

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t2;
 id

 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
(10 rows)

test=# table t3;
 id

 21
 22
 23
 24
 25
(5 rows)

-- now we want to move all data from sh=3 to sh=2, so we update lookup table
test=# table pt;
 id | sh
+
  1 |  1
  2 |  1
  3 |  1
  4 |  1
  5 |  1
  6 |  1
  7 |  1
  8 |  1
  9 |  1
 10 |  1
 10 |  2
 11 |  2
 12 |  2
 13 |  2
 14 |  2
 15 |  2
 16 |  2
 17 |  2
 18 |  2
 19 |  2
 20 |  2
 20 |  3
 21 |  3
 22 |  3
 23 |  3
 24 |  3
 25 |  3
 26 |  3
 27 |  3
 28 |  3
 29 |  3
 30 |  3
(32 rows)

test=# update pt set sh = 2 where sh = 3;
UPDATE 11

--- now since the lookup table is update, a noop update would get new
shards for ids and rebalance them accordingly.

test=# update t set id = id ;
UPDATE 25

-- validate tupes moved to respective new shards.
test=# table t1;
 id

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

test=# table t2;
 id

 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
(15 rows)

test=# table t3;
 id

(0 rows)



now,

"IMMUTABLE indicates that the function cannot modify the database and
always returns the same result when given the same argument values; that
is, it does not do database lookups or otherwise use information not
directly present in its argument list. If this option is given, any call of
the function with all-constant arguments can be immediately replaced with
the function value."

"IMMUTABLE must be a pure function, whose results depend only on its
inputs. This is a very strict requirement; they cannot call other
non-immutable functions, they cannot access tables, they cannot access the
value of configuration properties, etc"

I am making a table query in an immutable function, which is used for
partitioning.
although it does not result in error and it works, what is it that might
cause trouble.
ignore the performance issues, i understand having an access exclusive
table lock on pt would block partition forever.


Also, I plan to update shard lookup only when there are not DML on
partition tables.
What am I doing wrong.

ignore, as always, if it does not make sense :)



-- 
Thanks,
Vijay
Mumbai, India