Re: Help to understand Actual Rows vs Plan Rows from the query planner output
On 09/23/2018 10:21 PM, Arup Rakshit wrote: > Hello I have some questions related to the query plan output about the > planned and actual rows. In the following example: > > # explain (analyze true, costs true, format yaml) select * from users > where lower(city) = 'melanyfort' and lower(state) = 'ohio'; > QUERY PLAN > > > - Plan: > + > Node Type: "Bitmap Heap Scan" > + > Parallel Aware: false > + > Relation Name: "users" > + > Alias: "users" > + > Startup Cost: 10.78 > + > Total Cost: 14.80 > + > Plan Rows: 1 > + > Plan Width: 73 > + > Actual Startup Time: 0.155 > + > Actual Total Time: 0.155 > + > Actual Rows: 0 > + > Actual Loops: 1 > + > Recheck Cond: "((lower((city)::text) = 'melanyfort'::text) AND > (lower((state)::text) = 'ohio'::text))"+ > Rows Removed by Index Recheck: 0 > + > Exact Heap Blocks: 0 > + > Lossy Heap Blocks: 0 > + > Plans: > + > - Node Type: "BitmapAnd" > + > Parent Relationship: "Outer" > + > Parallel Aware: false > + > Startup Cost: 10.78 > + > Total Cost: 10.78 > + > Plan Rows: 1 > + > Plan Width: 0 > + > Actual Startup Time: 0.153 > + > Actual Total Time: 0.153 > + > Actual Rows: 0 > + > Actual Loops: 1 > + > Plans: > + > - Node Type: "Bitmap Index Scan" > + > Parent Relationship: "Member" > + > Parallel Aware: false > + > Index Name: "users_lower_idx" > + > Startup Cost: 0.00 > + > Total Cost: 4.66 > + > Plan Rows: 50 > + > Plan Width: 0 > + > Actual Startup Time: 0.048 > + > Actual Total Time: 0.048
Re: heads up on large text fields.
On 09/22/2018 06:00 AM, Andreas Kretschmer wrote: Am 22.09.2018 um 02:28 schrieb Rob Sargent: However, I get into deep dodo when I try redirecting psql output such as select ld from gt.ld\g /tmp/regen.file works for me if i start psql with -t -A -o /path/to/file (pg 10.5, but psql from 11beta3) Regards, Andreas OK, I'm a little slow on the uptake. The few very wide lines (728035 characters) demand that all the other lines be padded and with 132236 lines you end up with a 96G file (with out the smarts provided about).
Help to understand Actual Rows vs Plan Rows from the query planner output
Hello I have some questions related to the query plan output about the planned and actual rows. In the following example: # explain (analyze true, costs true, format yaml) select * from users where lower(city) = 'melanyfort' and lower(state) = 'ohio'; QUERY PLAN - Plan: + Node Type: "Bitmap Heap Scan" + Parallel Aware: false + Relation Name: "users" + Alias: "users" + Startup Cost: 10.78 + Total Cost: 14.80 + Plan Rows: 1 + Plan Width: 73 + Actual Startup Time: 0.155 + Actual Total Time: 0.155 + Actual Rows: 0 + Actual Loops: 1 + Recheck Cond: "((lower((city)::text) = 'melanyfort'::text) AND (lower((state)::text) = 'ohio'::text))"+ Rows Removed by Index Recheck: 0 + Exact Heap Blocks: 0 + Lossy Heap Blocks: 0 + Plans: + - Node Type: "BitmapAnd" + Parent Relationship: "Outer" + Parallel Aware: false + Startup Cost: 10.78 + Total Cost: 10.78 + Plan Rows: 1 + Plan Width: 0 + Actual Startup Time: 0.153 + Actual Total Time: 0.153 + Actual Rows: 0 + Actual Loops: 1 + Plans: + - Node Type: "Bitmap Index Scan" + Parent Relationship: "Member" + Parallel Aware: false + Index Name: "users_lower_idx" + Startup Cost: 0.00 + Total Cost: 4.66 + Plan Rows: 50 + Plan Width: 0 + Actual Startup Time: 0.048 + Actual Total Time: 0.048 + Actual Rows: 1 +
Re: PostgreSQl, PHP and IIS
On 9/23/18 3:54 AM, Mark Williams wrote: Thanks for the suggestions. I don't think there is any dependencies in the lib folder, but added it anyway and it made no difference. I tried "Process Explorer" (having previously tried "Dependency Walker") PE revealed one or two more dependencies I was previously unaware of. Added these to the root PHP folder, but still got me no further. I have even added Postgres.EXE to the php root folder. In desperation I tried adding all the postgres files to the PHP Ext folder. Unsurprisingly that did not yield results either. Does anyone know of a step by step guide for installing php and postgres on windows server? So I can doublecheck to see if there is anything I have missed. I can only assume I have made some simple mistake as it surely can't be this difficult to set up. Maybe this?: https://docs.microsoft.com/en-us/iis/application-frameworks/install-and-configure-php-on-iis/install-and-configure-php Cheers, Mark __ -Original Message- From: Adrian Klaver Sent: 22 September 2018 16:45 To: Mark Williams ; pgsql-general@lists.postgresql.org Subject: Re: PostgreSQl, PHP and IIS On 9/22/18 3:06 AM, Mark Williams wrote: "Fatal error: Call to undefined function pg_connect". Obviously, that is to be expected if pgsql module is not loading. Went back over your previous post and saw: "I have copied the version of libpq.dll from the lib folder of the postgresql installation to the root folder of the PHP installation. I have added the bin folder of the postgresql installation to the Windows system search path. " I would say add the lib/ of the Postgres install to the search path. -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to investigate what postgres is spending time on
On Sun, Sep 23, 2018 at 1:15 PM Kim Rose Carlsen wrote:
> Hi
>
>
> I have some simple INSERT / UPDATE queries, that takes a long time the
> first time they are run in out test environment, but I'm not sure what
> postgres is doing and what I can do to help it. Whats common is that the
> table contains many rows in the order of about 20 millions.
>
>
> Query:
>
> INSERT INTO communication.request_parameter (request_id,
> template_version_parameter_id, parameter_value)
> VALUES (1222, 1211, 122) RETURNING request_parameter_id
>
> Row from pg_stat_statements:
> ---+++--++--++++++++++++++++++-
> userid | dbid | queryid| query
> | calls | total_time | min_time | max_time
> | mean_time | stddev_time| rows | shared_blk |
> shared_blk | shared_blk | shared_blk | local_blks | local_blks | local_blks |
> local_blks | temp_blks_ | temp_blks_ | blk_read_t | blk_write_ |
> ---+++--++--++++++++++++++++++-
> 16385 | 16389 | 2064198912 | INSERT INTO
> communication.request_parameter (request_id, tem | 98 | 646.393451
>| 0.03 | 638.712758 | 6.59585154081633 | 64.1818799227704 | 98
> | 2850 | 24 | 21 | 0 | 0 | 0
> | 0 | 0 | 0 | 0 | 0 | 0
> |
>
> Description of table:
> # \d communication.request_parameter
> Table
> "communication.request_parameter"
> Column | Type| Collation | Nullable
> |
> Default
>
> ---+---+---+--+---
> request_parameter_id | integer | | not null
> |
> nextval('communication.request_parameter_request_parameter_id_seq'::regclass)
> request_id| integer | | not null
> |
> template_version_parameter_id | integer | | not null
> |
> parameter_value | character varying | |
> |
> Indexes:
> "request_parameter_pkey" PRIMARY KEY, btree (request_parameter_id)
> "request_parameter_parameter_value_idx" btree (parameter_value)
> "request_parameter_request_id_idx" btree (request_id)
> "request_parameter_template_version_parameter_id_idx" btree
> (template_version_parameter_id)
> Foreign-key constraints:
> "request_parameter_request_id_fkey" FOREIGN KEY (request_id)
> REFERENCES communication.request(request_id)
> "request_parameter_template_version_parameter_id_fkey" FOREIGN KEY
> (template_version_parameter_id) REFERENCES
> communication.template_version_parameter(template_version_parameter_id)
>
> This only happens in testing, and on a cold bootet database. The test
> database is constructed with pg_dump and restore on fresh postgres
> installation.
>
Sounds like warming up the cache, but still in a test environment you may
want to add auto_explain to your list of preloads and perhaps set it to
dump explain analyze when it hits a certain threshold. Note that while
dumping the query plans has very little overhead, timing the query plan
nodes does impact performance in a negative way.
>
>
> Best Regards
> Kim Carlsen
>
>
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more
How to investigate what postgres is spending time on
Hi
I have some simple INSERT / UPDATE queries, that takes a long time the first
time they are run in out test environment, but I'm not sure what postgres is
doing and what I can do to help it. Whats common is that the table contains
many rows in the order of about 20 millions.
Query:
INSERT INTO communication.request_parameter (request_id,
template_version_parameter_id, parameter_value)
VALUES (1222, 1211, 122) RETURNING request_parameter_id
Row from pg_stat_statements:
---+++--++--++++++++++++++++++-
userid | dbid | queryid| query
| calls | total_time | min_time | max_time |
mean_time | stddev_time| rows | shared_blk | shared_blk
| shared_blk | shared_blk | local_blks | local_blks | local_blks | local_blks |
temp_blks_ | temp_blks_ | blk_read_t | blk_write_ |
---+++--++--++++++++++++++++++-
16385 | 16389 | 2064198912 | INSERT INTO
communication.request_parameter (request_id, tem | 98 | 646.393451
| 0.03 | 638.712758 | 6.59585154081633 | 64.1818799227704 | 98
| 2850 | 24 | 21 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | 0 | 0 |
Description of table:
# \d communication.request_parameter
Table
"communication.request_parameter"
Column | Type| Collation | Nullable |
Default
---+---+---+--+---
request_parameter_id | integer | | not null |
nextval('communication.request_parameter_request_parameter_id_seq'::regclass)
request_id| integer | | not null |
template_version_parameter_id | integer | | not null |
parameter_value | character varying | | |
Indexes:
"request_parameter_pkey" PRIMARY KEY, btree (request_parameter_id)
"request_parameter_parameter_value_idx" btree (parameter_value)
"request_parameter_request_id_idx" btree (request_id)
"request_parameter_template_version_parameter_id_idx" btree
(template_version_parameter_id)
Foreign-key constraints:
"request_parameter_request_id_fkey" FOREIGN KEY (request_id) REFERENCES
communication.request(request_id)
"request_parameter_template_version_parameter_id_fkey" FOREIGN KEY
(template_version_parameter_id) REFERENCES
communication.template_version_parameter(template_version_parameter_id)
This only happens in testing, and on a cold bootet database. The test database
is constructed with pg_dump and restore on fresh postgres installation.
Best Regards
Kim Carlsen
Re: postgresql systemd service fails to start only on boot but not manually
On Sat, Sep 22, 2018 at 04:58:18PM +0200, Christoph Moench-Tegeder wrote: > ## Doron Behar (doron.be...@gmail.com): > > > My server fails to start PostgreSQL only on boot, if I restart it > > manually afterwards it doesn't have any problem starting. Here is the > > log extracted from the journal: > > > > ``` > > 2018-09-21 20:46:40.028 CEST [306] LOG: listening on IPv4 address > > "127.0.0.1", port 5432 > > 2018-09-21 20:46:40.036 CEST [306] LOG: listening on Unix socket > > "/run/postgresql/.s.PGSQL.5432" > > 2018-09-21 20:46:40.233 CEST [337] LOG: database system was shut down at > > 2018-09-21 20:46:21 CEST > > 2018-09-21 20:48:10.441 CEST [352] WARNING: worker took too long to start; > > canceled > > 2018-09-21 20:49:10.469 CEST [352] WARNING: worker took too long to start; > > canceled > > This would indicate that your machine is overloaded during start - > perhaps there's just too much being started at the same time? > ObRant: that's what happens if people take "system startup duration" > as a benchmark and optimize for that - sure, running one clumsy shell > script after another isn't effective usage of today's systems, > but starting eight dozens programs all at once may have other > side effects. Really, with the hardware taking small ages to find > it's own arse before even loading the boot loader, those few seconds > weren't worth optimizing - and if people reboot their computers so > often that startup time takes a measurable toll on their productive > day, perhaps they should rather spend their time thinking about their > usage pattern than "optimizing" the startup process. > > So, now that I've got that off my chest... your machine propably tries to > do too much at the same time when booting: the worker processes take > longer than 90 seconds to start. Slow CPU or storage maybe? > > > 2018-09-21 20:49:10.478 CEST [306] LOG: database system is ready to accept > > connections > > 2018-09-21 20:49:10.486 CEST [306] LOG: received fast shutdown request > > And in the mean time, systemd has lost it's patience, declares the > start as failed and terminates the process group. (The default systemd > timeout is 90 seconds, at least in some releases of systemd, so > this fits quite nicely). > > You could try to work around this by increasing TimeoutStartSec > in postgresql's systemd unit (or even globally), which perhaps > only hides the problem until the next service suddenly doesn't > start anymore. > You could move postgresql to the end of the boot order by > adding "After=..." to the Unit section of the systemd service > file, the value behind "After=" being all the other services in > the same target, which should reduce parallelism and improve > PostgreSQL's startup behaviour. > A more advanced variant of that would be to create a new > systemd target, make that start "After" multiuser.target > or even graphical.target (depending on your setup), make sure > it "Requires" the current default systemd target and make > postgresql the only additional service in that target. > (This would be the cleanest solution, but you should get some > grasp of systemd and how your specific distribution uses it > before meddling with the default targets; I don't know every > distribution/version variant of systemd integration, so I > can't give that specific instructions here). > Or you figure out what the heck your machine is running > during startup any why it is that slow, and try to fix that. > > Regards, > Christoph Thanks for your very detailed answer, that helped me a lot. I've increased `TimeoutSec=` to infinity in the systemd service since it was set initially to 120 seconds which apparently wasn't enough for my poor VPS with 2G RAM and 1 CPU core. That worked great, I still feel like I have slow startups but at least PostgreSQL doesn't totally fail to start on boot. I'll try to debug the slow startups on my own, thanks again for everything! Doron.
RE: PostgreSQl, PHP and IIS
Thanks for the suggestions. I don't think there is any dependencies in the lib folder, but added it anyway and it made no difference. I tried "Process Explorer" (having previously tried "Dependency Walker") PE revealed one or two more dependencies I was previously unaware of. Added these to the root PHP folder, but still got me no further. I have even added Postgres.EXE to the php root folder. In desperation I tried adding all the postgres files to the PHP Ext folder. Unsurprisingly that did not yield results either. Does anyone know of a step by step guide for installing php and postgres on windows server? So I can doublecheck to see if there is anything I have missed. I can only assume I have made some simple mistake as it surely can't be this difficult to set up. Cheers, Mark __ -Original Message- From: Adrian Klaver Sent: 22 September 2018 16:45 To: Mark Williams ; pgsql-general@lists.postgresql.org Subject: Re: PostgreSQl, PHP and IIS On 9/22/18 3:06 AM, Mark Williams wrote: > "Fatal error: Call to undefined function pg_connect". > > Obviously, that is to be expected if pgsql module is not loading. Went back over your previous post and saw: "I have copied the version of libpq.dll from the lib folder of the postgresql installation to the root folder of the PHP installation. I have added the bin folder of the postgresql installation to the Windows system search path. " I would say add the lib/ of the Postgres install to the search path. > > __ > > -Original Message- > From: Adrian Klaver > Sent: 21 September 2018 23:02 > To: Mark Williams ; > pgsql-general@lists.postgresql.org > Subject: Re: PostgreSQl, PHP and IIS > > On 9/21/18 10:35 AM, Mark Williams wrote: >> I have PHP v7.2.7 and PostgreSQL v10 with Windows IIS. >> >> I am trying to get this to work following any advice I have been able >> to find on the web, but with no success. My configuration so far is >> as > below. >> >> PHP.ini extension list includes both php_pdo_pgsql.dll and php_pgsql.dll. >> >> Both these dlls are located in the correct extensions directory >> according to phpinfo. >> >> According to phpinfo the Configuration File Path is "C:\Windows" and >> the Loaded Configuration File is "C:\Program Files\PHP\v7.2\php.ini". >> I have the same php.ini file at both locations. >> >> I have copied the version of libpq.dll from the lib folder of the >> postgresql installation to the root folder of the PHP installation. >> >> I have added the bin folder of the postgresql installation to the >> Windows system search path. >> >> Phpinfo shows that pdo_pgsql extension is loaded but not pgsql. >> >> I have also tried copying the dependent dlls (ssleay32.dll, >> libeay32.dll, libintl-8.dll, libiconv-2.dll) from the postgresql bin >> folder to the php root folder). >> >> Phpinfo show php_pdo_pgsql is enabled, but not php_pgsql.dll and an >> undefined function call error is displayed by php when I try and run >> any pg php functions. >> >> Can anyone please shed any light on what more I need to do? > > So what is the error you are getting? > > Or to put it another way, what is not happening? > >> >> Regards, >> >> Mark >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Adrian Klaver adrian.kla...@aklaver.com
Re: postgresql systemd service fails to start only on boot but not manually
On Sat, Sep 22, 2018 at 07:14:33AM -0700, Adrian Klaver wrote:
>
> Linux distro and version?
Arch Linux
$ uname -a
Linux vps 4.18.9-arch1-1-ARCH #1 SMP PREEMPT Wed Sep 19 21:19:17 UTC
2018 x86_64 GNU/Linux
It's a VPS with one CPU core on it and 2G RAM - not very much I know,
hosted on OVH
>
> Assuming Postgres version 10+ given logical replication warning. Still
> actual version would be nice.
>
> How did you install Postgres?
With the package manager and the files used in it's build are viewable
from here:
https://git.archlinux.org/svntogit/packages.git/tree/trunk?h=packages/postgresql
>
> Where did systemd script come from?
>
> What is in the systemd script?
The systemd service was installed with the package
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=notify
TimeoutSec=120
User=postgres
Group=postgres
Environment=PGROOT=/var/lib/postgres
SyslogIdentifier=postgres
PIDFile=/var/lib/postgres/data/postmaster.pid
RuntimeDirectory=postgresql
RuntimeDirectoryMode=755
ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data
ExecStart=/usr/bin/postgres -D ${PGROOT}/data
ExecReload=/bin/kill -HUP ${MAINPID}
KillMode=mixed
KillSignal=SIGINT
# Due to PostgreSQL's use of shared memory, OOM killer is often
overzealous in
# killing Postgres, so adjust it downward
OOMScoreAdjust=-200
# Additional security-related features
PrivateTmp=true
ProtectHome=true
ProtectSystem=full
NoNewPrivileges=true
[Install]
WantedBy=multi-user.target
>
> What does the log show when you do a successful manual start?
2018-09-22 09:38:44.470 CEST [15251] LOG: listening on IPv4 address
"127.0.0.1", port 5432
2018-09-22 09:38:44.472 CEST [15251] LOG: listening on Unix socket
"/run/postgresql/.s.PGSQL.5432"
2018-09-22 09:38:44.485 CEST [15253] LOG: database system was shut
down at 2018-09-21 20:49:10 CEST
2018-09-22 09:38:44.490 CEST [15251] LOG: database system is ready to
accept connections
>
> What does the system log show when the Postgres reboot startup fails?
This was posted in my 1st message.
