Re: Optimal configuration for server

[Moises Lopez]

Could you enable the connections logs and share the results when it is
reproduced, please?

It generally shows the error code and message

So, you can double-confirm if it is because of KeepAlive configuration or
something else

-- 
Moisés López Calderón
Mobile: (+521) 477-752-22-30
Twitter: @moylop260
hangout: moylop...@vauxoo.com
http://www.vauxoo.com - Odoo Gold Partner
Twitter: @vauxoo

The query plan get all columns but I'm using only one column.

[Moises Lopez]

We have production database that has slow queries because of the query get
all columns even if I'm using only one column.
The result is slow for tables that there are too much columns
The weird part is that there is environment that I can't reproduce it even
if they are using the same postgresql.conf
I didn't find what is the variant/configuration to avoid it
I could reproduce it using the official docker image of postgresql

* Steps to reproduce it

1. Run the following script:
docker run --name psql1 -d -e POSTGRES_PASSWORD=pwd postgres
docker exec -it --user=postgres psql1 psql
# Into docker container
CREATE DATABASE db;
\connect db;
CREATE TABLE link (
ID serial PRIMARY KEY,
url VARCHAR (255) NOT NULL,
name VARCHAR (255) NOT NULL,
description VARCHAR (255),
rel VARCHAR (50)
);
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT l1.url
FROM link l1
JOIN link l2
  ON l1.url=l2.url;

2. See result of the Query Plan:
QUERY PLAN

---
Hash Join  (cost=10.90..21.85 rows=40 width=516) (actual
time=0.080..0.081 rows=1 loops=1)
Output: l1.url
Hash Cond: ((l1.url)::text = (l2.url)::text)
Buffers: shared hit=5
->  Seq Scan on public.link l1  (cost=0.00..10.40 rows=40 width=516)
(actual time=0.010..0.011 rows=1 loops=1)
*Output: l1.id , l1.url, l1.name
, l1.description, l1.rel*
Buffers: shared hit=1
->  Hash  (cost=10.40..10.40 rows=40 width=516) (actual
time=0.021..0.021 rows=1 loops=1)
Output: l2.url
Buckets: 1024  Batches: 1  Memory Usage: 9kB
Buffers: shared hit=1
->  Seq Scan on public.link l2  (cost=0.00..10.40 rows=40
width=516) (actual time=0.010..0.011 rows=1 loops=1)
Output: l2.url
Buffers: shared hit=1
Planning Time: 0.564 ms
Execution Time: 0.142 ms

3. Notice that I'm using only the column "url" for "JOIN" and "SELECT"
section,
but the "Output" section is returning all columns.

Is there a manner to avoid returning all columns in order to get a better
performance?

Thank you in advance

* PostgreSQL version:

psql postgres -c "SELECT version()"
PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Changes made to the settings in the postgresql.conf file:  see Server
Configuration for a quick way to list them all.
without changes

Operating system and version:
cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"

-- 
Moisés López
@moylop260

Re: The query plan get all columns but I'm using only one column.

[Moises Lopez]

Michael,

Your complete explanation is very helpful!
I appreciate it
Thank you so much!

Regards!


El jue., 30 abr. 2020 a las 10:52, Michael Lewis ()
escribió:

> In staging environment we have disabled autovacuum since that it is a
>> testing environment and the database are restored very often.
>> But in production environment it is enabled autovacuum=on
>>
>> The weird case is that production was slow and staging environment was
>> faster.
>>
>
> You haven't specified how you are doing backup and restore, but unless it
> is a byte-for-byte file copy method, then there would be no bloat on the
> restored staging environment so no need to vacuum. You would want to ensure
> you take a new statistics sample with analyze database after restore if you
> aren't.
>
> In your production system, if your configs for autovacuum settings have
> not been changed from the default parameters, it probably is not keeping up
> at all if the system is moderately high in terms of update/delete
> transactions. You can check pg_stat_activity for active vacuums, change the
> parameter to log autovacuums longer than X to 0 and review the logs, or
> check pg_stat_user_tables to see how many autovacuums/analyze have been
> done since you last reset those stats.
>
> If you have tables that are in the millions or hundreds or millions of
> rows, then I would recommend decreasing autovacuum_vacuum_scale_factor from
> 20% down to 1% or perhaps less and similar
> for autovacuum_analyze_scale_factor. You can do this on individual tables
> if you have mostly small tables and just a few large ones. Else, increase
> the threshold settings as well. The default value
> for autovacuum_vacuum_cost_delay changed from 20ms to 2ms in PG12 so that
> may also be prudent to do likewise if you upgraded to PG12 and kept your
> old settings, assuming your I/O system can handle it.
>
> Otherwise, if you have a period of time when the activity is low for your
> database(s), then a last resort can be a daily scheduled vacuum analyze on
> all tables. Note- do not do vacuum FULL which requires an exclusive lock on
> the table to re-write it entirely. You are just looking to mark space
> re-usable for future transactions, not recover the disk space back to the
> OS to be consumed again if autovacuum still can't keep up. pg_repack
> extension would be an option if you need to recover disk space while online.
>


-- 
Moisés López Calderón
Mobile: (+521) 477-752-22-30
Twitter: @moylop260
hangout: moylop...@vauxoo.com
http://www.vauxoo.com - Odoo Gold Partner
Twitter: @vauxoo