Re: [PERFORM] Speeding up JSON + TSQUERY + GIN

[Jeff Janes]

On Sun, Feb 26, 2017 at 5:28 AM, Sven R. Kunze  wrote:

>
>
> Using "select pg_prewarm('docs');" and on any of the indexes doesn't help
> either.
> After a "systemctl stop postgresql.service && sync && echo 3 >
> /proc/sys/vm/drop_caches && systemctl start postgresql.service" the age=20,
> 30 or name=john queries are slow again.
>
>
> Is there a way to speed up or to warm up things permanently?
>


If by 'permanently', you mean even when you intentionally break things,
then no.  You will always be able to intentionally break things.  There is
on-going discussion of an auto-prewarm feature.  But that doesn't yet
exist; and once it does, a super user will always be able to break it.

Presumably you have a use-case in mind other than intentional sabotage of
your caches by root.  But, what is it?  If you reboot the server
frequently, maybe you can just throw 'select pg_prewarm...' into an init
script?

Cheers,

Jeff

Re: [PERFORM] Speeding up JSON + TSQUERY + GIN

[Sven R. Kunze]

Thanks Oleg for your reply.

On 26.02.2017 21:13, Oleg Bartunov wrote:
On Sun, Feb 26, 2017 at 4:28 PM, Sven R. Kunze >wrote:


create index docs_meta_idx ON docs using gin (meta jsonb_path_ops);
create index docs_name_idx ON docs using gin
(to_tsvector('english', meta->>'name'));
create index docs_address_idx ON docs using gin
(to_tsvector('english', meta->>'address'));


functional index tends to be slow, better use separate column(s) for 
tsvector


Why? Don't we have indexes to make them faster?

The idea is to accelerate all operations as specified (cf. the table 
schema below) without adding more and more columns.



what is full output from explain analyze ?


Okay, let's stick to gin + @> operator for nowbefore we tackle the 
functional index issue.

Maybe, I did something wrong while defining the gin indexes:


explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
-
 Bitmap Heap Scan on docs  (cost=86.50..9982.50 rows=1 width=4) 
(actual time=97.443..8073.983 rows=98385 loops=1)

   Recheck Cond: (meta @> '{"age": 40}'::jsonb)
   Heap Blocks: exact=79106
   ->  Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=1 
width=0) (actual time=66.878..66.878 rows=98385 loops=1)

 Index Cond: (meta @> '{"age": 40}'::jsonb)
 Planning time: 0.118 ms
 Execution time: 8093.533 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
-
 Bitmap Heap Scan on docs  (cost=86.50..9982.50 rows=1 width=4) 
(actual time=99.527..3349.001 rows=98385 loops=1)

   Recheck Cond: (meta @> '{"age": 40}'::jsonb)
   Heap Blocks: exact=79106
   ->  Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=1 
width=0) (actual time=68.503..68.503 rows=98385 loops=1)

 Index Cond: (meta @> '{"age": 40}'::jsonb)
 Planning time: 0.113 ms
 Execution time: 3360.773 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
-
 Bitmap Heap Scan on docs  (cost=86.50..9982.50 rows=1 width=4) 
(actual time=64.928..168.311 rows=98385 loops=1)

   Recheck Cond: (meta @> '{"age": 40}'::jsonb)
   Heap Blocks: exact=79106
   ->  Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=1 
width=0) (actual time=45.340..45.340 rows=98385 loops=1)

 Index Cond: (meta @> '{"age": 40}'::jsonb)
 Planning time: 0.121 ms
 Execution time: 171.098 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
-
 Bitmap Heap Scan on docs  (cost=86.50..9982.50 rows=1 width=4) 
(actual time=86.118..215.755 rows=98385 loops=1)

   Recheck Cond: (meta @> '{"age": 40}'::jsonb)
   Heap Blocks: exact=79106
   ->  Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=1 
width=0) (actual time=54.535..54.535 rows=98385 loops=1)

 Index Cond: (meta @> '{"age": 40}'::jsonb)
 Planning time: 0.127 ms
 Execution time: 219.746 ms
(7 rows)

explain analyze select id from docs where meta @> '{"age": 40}';
QUERY PLAN
-
 Bitmap Heap Scan on docs  (cost=86.50..9982.50 rows=1 width=4) 
(actual time=83.197..211.840 rows=98385 loops=1)

   Recheck Cond: (meta @> '{"age": 40}'::jsonb)
   Heap Blocks: exact=79106
   ->  Bitmap Index Scan on docs_meta_idx (cost=0.00..84.00 rows=1 
width=0) (actual time=53.036..53.036 rows=98385 loops=1)

 Index Cond: (meta @> '{"age": 40}'::jsonb)
 Planning time: 0.127 ms
 Execution time: 215.753 ms
(7 rows)


Regards,
Sven


Table Schema:

 Table "public.docs"
 Column |  Type   | Modifiers
+-+---
 id | integer | not null default nextval('docs_id_seq'::regclass)
 meta   | jsonb   |
Indexes:
"docs_pkey" PRIMARY KEY, btree (id)
"docs_address_idx" gin (to_tsvector('english'::regconfig, meta ->> 
'address'::text))

"docs_address_trgm_idx" gin ((meta ->> 'address'::text) gin_trgm_ops)
"docs_birth_idx" btree ((meta ->> 'birth'::text))
"docs_meta_idx" gin (meta jsonb_path_ops)
"docs_name_idx" gin (to_tsvector('english'::regconfig, meta ->> 
'name'::text))

Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not ?

[Dinesh Chandra 12108]

Hi,

I run the below command
[postgres@CPPMoma-DB05 bin]$ pg_dump -U postgres  moma_ts_oce; echo $

Output was like this(Last few lines )
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

$

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
--
Mobile: +91-9953975849 | Ext 1078 |dinesh.chan...@cyient.com
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.

-Original Message-
From: vinny [mailto:vi...@xs4all.nl]
Sent: 27 February, 2017 7:31 PM
To: John Gorman 
Cc: Rick Otten ; Dinesh Chandra 12108 
; pgsql-performance@postgresql.org; 
pgsql-performance-ow...@postgresql.org
Subject: Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not 
?

On 2017-02-27 14:29, John Gorman wrote:
> Even though it's not listed in any of the documentation or “pg_dump
> --help” you can check the return code of the process. A return code
> greater than 0 (zero) usually indicates a failure
>
> ./bin >pg_dump -U dummy_user  dummy_database; echo $?
>
> 1
>
> FROM: pgsql-performance-ow...@postgresql.org
> [mailto:pgsql-performance-ow...@postgresql.org] ON BEHALF OF Rick
> Otten
> SENT: Monday, February 27, 2017 3:36 AM
> TO: Dinesh Chandra 12108
> CC: pgsql-performance@postgresql.org
> SUBJECT: Re: [PERFORM] How Can I check PostgreSQL backup is
> successfully or not ?
>
> Although it doesn't really tell if the pg_dump was successful (you'll
> need to do a full restore to be sure), I generate an archive list.  If
> that fails, the backup clearly wasn't successful, and if it succeeds,
> odds are pretty good that it worked:
>
> On Mon, Feb 27, 2017 at 4:35 AM, Dinesh Chandra 12108
>  wrote:
>
> Hi,
>
> We are taking daily full backup of PostgreSQL database using PG_DUMP
> which is automatic scheduled through Cronjobs.
>
> How can I check my yesterday backup is successfully or not?
>
> Is there any query or view by which I can check it?
>
> REGARDS,
>
> DINESH CHANDRA
>
> |DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA.


It's important to note the distinction between

"the backup process did not fail"

and

"we now have a trustworthy backup"

And you can go full-paranoia and say that you can successfully create a 
perfectly working backup of the wrong database.

So what is it that you want to make sure of:
1. Did the process give an error?
2. Did the process create a usable backup?

What are the chances of #1 reporting success but still producing a bad backup?
And can #2 fail on a good database, and if so, can you detect that?





DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not ?

[vinny]

On 2017-02-27 14:29, John Gorman wrote:

Even though it's not listed in any of the documentation or “pg_dump
--help” you can check the return code of the process. A return code
greater than 0 (zero) usually indicates a failure

./bin >pg_dump -U dummy_user  dummy_database; echo $?

1

FROM: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] ON BEHALF OF Rick
Otten
SENT: Monday, February 27, 2017 3:36 AM
TO: Dinesh Chandra 12108
CC: pgsql-performance@postgresql.org
SUBJECT: Re: [PERFORM] How Can I check PostgreSQL backup is
successfully or not ?

Although it doesn't really tell if the pg_dump was successful (you'll
need to do a full restore to be sure), I generate an archive list.  If
that fails, the backup clearly wasn't successful, and if it succeeds,
odds are pretty good that it worked:

On Mon, Feb 27, 2017 at 4:35 AM, Dinesh Chandra 12108
 wrote:

Hi,

We are taking daily full backup of PostgreSQL database using PG_DUMP
which is automatic scheduled through Cronjobs.

How can I check my yesterday backup is successfully or not?

Is there any query or view by which I can check it?

REGARDS,

DINESH CHANDRA

|DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA.



It's important to note the distinction between

"the backup process did not fail"

and

"we now have a trustworthy backup"

And you can go full-paranoia and say that you can successfully create a 
perfectly working backup of the wrong database.


So what is it that you want to make sure of:
1. Did the process give an error?
2. Did the process create a usable backup?

What are the chances of #1 reporting success but still producing a bad 
backup?

And can #2 fail on a good database, and if so, can you detect that?




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not ?

[John Gorman]

Even though it's not listed in any of the documentation or “pg_dump --help” you 
can check the return code of the process. A return code greater than 0 (zero) 
usually indicates a failure

./bin >pg_dump -U dummy_user  dummy_database; echo $?
1

From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Rick Otten
Sent: Monday, February 27, 2017 3:36 AM
To: Dinesh Chandra 12108
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not 
?

Although it doesn't really tell if the pg_dump was successful (you'll need to 
do a full restore to be sure), I generate an archive list.  If that fails, the 
backup clearly wasn't successful, and if it succeeds, odds are pretty good that 
it worked:

-- bash code snippet --
archiveList=`pg_restore -l ${backupFolder}`
if [[ ! ${archiveList} =~ "Archive created at" ]]
then
echo "PostgreSQL backup - Archive List Test Failed for 
${hostName}:${dbName}"
echo "Archive listing:"
echo ${archiveList}
exit 1
fi
---



On Mon, Feb 27, 2017 at 4:35 AM, Dinesh Chandra 12108 
mailto:dinesh.chan...@cyient.com>> wrote:
Hi,

We are taking daily full backup of PostgreSQL database using PG_DUMP which is 
automatic scheduled through Cronjobs.

How can I check my yesterday backup is successfully or not?
Is there any query or view by which I can check it?

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
--
Mobile: +91-9953975849 | Ext 1078 
|dinesh.chan...@cyient.com
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.




DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.

Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not ?

[Rick Otten]

Although it doesn't really tell if the pg_dump was successful (you'll need
to do a full restore to be sure), I generate an archive list.  If that
fails, the backup clearly wasn't successful, and if it succeeds, odds are
pretty good that it worked:

-- bash code snippet --
archiveList=`pg_restore -l ${backupFolder}`
if [[ ! ${archiveList} =~ "Archive created at" ]]
then
echo "PostgreSQL backup - Archive List Test Failed for
${hostName}:${dbName}"
echo "Archive listing:"
echo ${archiveList}
exit 1
fi
---



On Mon, Feb 27, 2017 at 4:35 AM, Dinesh Chandra 12108 <
dinesh.chan...@cyient.com> wrote:

> Hi,
>
>
>
> We are taking daily full backup of PostgreSQL database using *PG_DUMP*
> which is automatic scheduled through Cronjobs.
>
>
>
> How can I check my yesterday backup is successfully or not?
>
> Is there any query or view by which I can check it?
>
>
>
> *Regards,*
>
> *Dinesh Chandra*
>
> *|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.*
>
> *--*
>
> Mobile: +91-9953975849 <+91%2099539%2075849> | Ext 1078
> |dinesh.chan...@cyient.com
>
> Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.
>
>
>
> --
>
> DISCLAIMER:
>
> This email message is for the sole use of the intended recipient(s) and
> may contain confidential and privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not the
> intended recipient, please contact the sender by reply email and destroy
> all copies of the original message. Check all attachments for viruses
> before opening them. All views or opinions presented in this e-mail are
> those of the author and may not reflect the opinion of Cyient or those of
> our affiliates.
>

[PERFORM] How Can I check PostgreSQL backup is successfully or not ?

[Dinesh Chandra 12108]

Hi,

We are taking daily full backup of PostgreSQL database using PG_DUMP which is 
automatic scheduled through Cronjobs.

How can I check my yesterday backup is successfully or not?
Is there any query or view by which I can check it?

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
--
Mobile: +91-9953975849 | Ext 1078 
|dinesh.chan...@cyient.com
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.




DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.