Volker Boehm | 30 May 19:53 2016
Picon

similarity and operator '%'

Hello,
I'm trying to find persons in an address database where I have built 
trgm-indexes on name, street, zip and city.

When I search for all four parts of the address (name, street, zip and city)

     select name, street, zip, city
     from addresses
     where name % $1
       and street % $2
       and (zip % $3 or city % $4)

everything works fine: It takes less than a second to get some (5 - 500) 
proposed addresses out of 500,000 addresses and the query plan shows

     Bitmap Heap Scan on addresses  (cost=168.31..1993.38 rows=524 ...
       Recheck Cond: ...
       ->  Bitmap Index Scan on ...
             Index Cond: ...

The same happens when I search only by name with

     select name, street, zip, city
     from addresses
     where name % $1

But when I rewrite this query to

     select name, street, zip, city
     from addresses
(Continue reading)

Jake Magner | 27 May 21:08 2016
Picon
Gravatar

Planner chooses slow index heap scan despite accurate row estimates

I am trying to insert rows that don't already exist from a temp table into
another table. I am using a LEFT JOIN on all the columns and checking for
nulls in the base table to know which rows to insert. The problem is that
the planner is choosing a nested loop plan which is very slow over the much
faster (~40x) hash join. What's interesting is that all the row estimates
appear to be fairly accurate. I'm wondering if it has something to do with
the GIN indexes on bigint_array_1 and bigint_array_2. Perhaps it
misestimates the cost of each index scan?

Postgres 9.3.10 on 2.6.32-573.18.1.el6.x86_64 GNU/Linux
- base_table has been VACUUM ANALYZED
- base_table has GIN indexes on bigint_array_1 and bigint_array_2
- base_table has btree index on id
- base_table is 700k rows
- temp_table is 4k rows
- the bigint arrays are type bigint[] and contain 0 to 5 elements, with a
median of 1 element
- the time difference between nested loop vs hash join is not based on the
cache, I can reproduce it in either order

test_db=# BEGIN;
BEGIN
test_db=# EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO base_table (
    bigint_array_1, bigint_array_2, id
) (
    SELECT s.bigint_array_1, s.bigint_array_2, s.id
    FROM temp_rows_to_insert s
    LEFT JOIN base_table t
    ON s.bigint_array_1 = t.bigint_array_1 AND s.bigint_array_2 =
(Continue reading)

Johan Fredriksson | 27 May 14:10 2016
Picon
Picon

Performance problems with 9.2.15

Hello!

I am just about to upgrade from PostgreSQL 8.4.20 to 9.2.15, but I'v run
into some huge performance issues. Both databases are configured the
same way (shared_buffers = 2GB, temp_buffers = 32MB). I have increased
work_mem on the 9.2 from 4MB to 64MB, but to no avail.

Now, the query on 8.4:
rt4=# EXPLAIN ANALYZE VERBOSE SELECT DISTINCT main.* FROM Users main
CROSS JOIN ACL ACL_3 JOIN Principals Principals_1  ON ( Principals_1.id
= main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON
( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN
CachedGroupMembers CachedGroupMembers_4  ON
( CachedGroupMembers_4.MemberId = Principals_1.id )  WHERE
((ACL_3.ObjectType = 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System'
AND ACL_3.ObjectId   = 1)) AND (ACL_3.PrincipalId =
CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND
(ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND
(CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId
= '4') AND (CachedGroupMembers_4.Disabled = '0') AND
(Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User')
AND (Principals_1.id != '1')  ORDER BY main.Name ASC;

QUERY
PLAN                                                                                           

                    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(Continue reading)

Tory M Blue | 26 May 00:34 2016
Picon

Testing in AWS, EBS

We are starting some testing in AWS, with EC2, EBS backed setups.

What I found interesting today, was a single EBS 1TB volume, gave me
something like 108MB/s throughput, however a RAID10 (4 250GB EBS
volumes), gave me something like 31MB/s (test after test after test).

I'm wondering what you folks are using inside of Amazon (not
interested in RDS at the moment).

Thanks
Tory

--

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

Vladimir Borodin | 25 May 16:33 2016

9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

Hi all.

We have found that queries through PgBouncer 1.7.2 (with transaction pooling) to local PostgreSQL are almost two times slower in 9.5.3 than in 9.4.8 on RHEL 6 hosts (all packages are updated to last versions). Meanwhile the problem can’t be reproduced i.e. on Ubuntu 14.04 (also fully-updated).

Here is how the results look like for 9.4, 9.5 and 9.6. All are built from latest commits on yesterday in
* REL9_4_STABLE (a0cc89a28141595d888d8aba43163d58a1578bfb),
* REL9_5_STABLE (e504d915bbf352ecfc4ed335af934e799bf01053),
* master (6ee7fb8244560b7a3f224784b8ad2351107fa55d).

All of them are build on the host where testing is done (with stock gcc versions). Sysctls, pgbouncer config and everything we found are the same, postgres configs are default, PGDATA is in tmpfs. All numbers are reproducible, they are stable between runs.

Shortly:

OS PostgreSQL version TPS Avg. latency
RHEL 6 9.4 44898 1.425 ms
RHEL 6 9.5 26199 2.443 ms
RHEL 6 9.5 43027 1.487 ms
Ubuntu 14.04 9.4 67458 0.949 ms
Ubuntu 14.04 9.5 64065 0.999 ms
Ubuntu 14.04 9.6 64350 0.995 ms

You could see that the difference between major versions on Ubuntu is not significant, but on RHEL 9.5 is 70% slower than 9.4 and 9.6.

Below are more details.

RHEL 6:

postgres <at> pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg94'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 2693962
latency average: 1.425 ms
tps = 44897.461518 (including connections establishing)
tps = 44898.763258 (excluding connections establishing)
postgres <at> pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg95'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 1572014
latency average: 2.443 ms
tps = 26198.928627 (including connections establishing)
tps = 26199.803363 (excluding connections establishing)
postgres <at> pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg96'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 2581645
latency average: 1.487 ms
tps = 43025.676995 (including connections establishing)
tps = 43027.038275 (excluding connections establishing)
postgres <at> pgload05g ~ $

Ubuntu 14.04 (the same hardware):

postgres <at> pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg94'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 4047653
latency average: 0.949 ms
tps = 67458.361515 (including connections establishing)
tps = 67459.983480 (excluding connections establishing)
postgres <at> pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg95'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 3844084
latency average: 0.999 ms
tps = 64065.447458 (including connections establishing)
tps = 64066.943627 (excluding connections establishing)
postgres <at> pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg96'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 3861088
latency average: 0.995 ms
tps = 64348.573126 (including connections establishing)
tps = 64350.195750 (excluding connections establishing)
postgres <at> pgloadpublic02:~$

In both tests (RHEL and Ubuntu) the bottleneck is performance of singe CPU core which is 100% consumed by PgBouncer. If pgbench connects to postgres directly I get the following (expected) numbers:

postgres <at> pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=5432'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 10010710
latency average: 0.384 ms
tps = 166835.937859 (including connections establishing)
tps = 166849.730224 (excluding connections establishing)
postgres <at> pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=5433'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 13373890
latency average: 0.287 ms
tps = 222888.311289 (including connections establishing)
tps = 222951.470125 (excluding connections establishing)
postgres <at> pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=5434'
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 12989816
latency average: 0.296 ms
tps = 216487.458399 (including connections establishing)
tps = 216548.069976 (excluding connections establishing)
postgres <at> pgload05g ~ $

Compilation options look almost the same:
# RHEL 6
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2
# Ubuntu
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2

Attached are a simple script to deploy the testing environment (PgBouncer should be installed) and pgbouncer config. I could provide any other needed information like backtraces or perf reports or anything else.

Attachment (pgbouncer.ini): application/octet-stream, 7496 bytes

Attachment (deploy.sh): application/octet-stream, 1305 bytes


--
May the force be with you…

Justin Pryzby | 24 May 19:39 2016

index fragmentation on insert-only table with non-unique column

Summary: Non-unique btree indices are returning CTIDs for rows with same
value of indexed column not in logical order, imposing a high performance
penalty.

Running PG 9.5.3 now, we have a time-based partitions of append-only tables
with data loaded from other sources.  The tables are partitioned by time, and
timestamp column has an non-unique, not-null btree index.

The child tables are each ~75GB and expected to keep growing.  For a child
table with a week's worth of data:
relpages  | 11255802
reltuples | 5.90502e+07

The data is loaded shortly after it's available, so have high correlation in
pg_statistic:
[pryzbyj <at> viaero ~]$ psql ts -c "SELECT tablename, correlation, n_distinct FROM pg_stats s JOIN pg_class
c ON (c.relname=s.tablename) WHERE tablename LIKE 'cdrs_huawei_pgwrecord%' AND
attname='recordopeningtime' ORDER BY 1" |head
            tablename             | correlation | n_distinct 
----------------------------------+-------------+------------
 cdrs_huawei_pgwrecord            |    0.999997 |     102892
 cdrs_huawei_pgwrecord_2016_02_15 |    0.999658 |      96145
 cdrs_huawei_pgwrecord_2016_02_22 |    0.999943 |      91916
 cdrs_huawei_pgwrecord_2016_02_29 |    0.997219 |      50341
 cdrs_huawei_pgwrecord_2016_03_01 |    0.999947 |      97485

But note the non-uniqueness of the index column:
ts=# SELECT recordopeningtime, COUNT(1) FROM cdrs_huawei_pgwrecord WHERE
recordopeningtime>='2016-05-21' AND recordopeningtime<'2016-05-22' GROUP BY 1 ORDER BY 2 DESC;
  recordopeningtime  | count
---------------------+-------
 2016-05-21 12:17:29 |   176
 2016-05-21 12:17:25 |   171
 2016-05-21 13:11:33 |   170
 2016-05-21 10:20:02 |   169
 2016-05-21 11:30:02 |   167
[...]

We have an daily analytic query which processes the previous day's data.  For
new child tables, with only 1 days data loaded, this runs in ~30min, and for
child tables with an entire week's worth of data loaded, takes several hours
(even though both queries process the same amount of data).

First, I found I was able to get 30-50min query results on full week's table by
prefering a seq scan to an index scan.  The row estimates seemed fine, and the
only condition is the timestamp, so the planner's use of index scan is as
expected.

AFAICT what's happening is that the index scan was returning pages
nonsequentially.  strace-ing the backend showed alternating lseek()s and
read()s, with the offsets not consistently increasing (nor consistently
decreasing):
% sudo strace -p 25588 2>&1 |grep -m9 'lseek(773'
lseek(773, 1059766272, SEEK_SET)        = 1059766272
lseek(773, 824926208, SEEK_SET)         = 824926208
lseek(773, 990027776, SEEK_SET)         = 990027776
lseek(773, 990330880, SEEK_SET)         = 990330880
lseek(773, 1038942208, SEEK_SET)        = 1038942208
lseek(773, 1059856384, SEEK_SET)        = 1059856384
lseek(773, 977305600, SEEK_SET)         = 977305600
lseek(773, 990347264, SEEK_SET)         = 990347264
lseek(773, 871096320, SEEK_SET)         = 871096320

.. and consecutive read()s being rare:
read(802, "g"..., 8192)                 = 8192
lseek(802, 918003712, SEEK_SET)         = 918003712
read(802, "c"..., 8192)                 = 8192
lseek(802, 859136000, SEEK_SET)         = 859136000
read(802, "a"..., 8192)                 = 8192
lseek(802, 919601152, SEEK_SET)         = 919601152
read(802, "d"..., 8192)                 = 8192
lseek(802, 905101312, SEEK_SET)         = 905101312
read(802, "c"..., 8192)                 = 8192
lseek(801, 507863040, SEEK_SET)         = 507863040
read(801, "p"..., 8192)                 = 8192
lseek(802, 914235392, SEEK_SET)         = 914235392
read(802, "c"..., 8192)                 = 8192

I was able to see great improvement without planner parameters by REINDEX the
timestamp index.  My theory is that the index/planner doesn't handle well the
case of many tuples with same column value, and returns pages out of logical
order.  Reindex fixes that, rewriting the index data with pages in order
(confirmed with pageinspect), which causes index scans to fetch heap data more
or less monotonically (if not consecutively).  strace shows that consecutive
read()s are common (without intervening seeks).  I gather this allows the OS
readahead to kick in.

Postgres seems to assume that the high degree of correlation of the table
column seen in pg_stats is how it will get data from the index scan, which
assumption seems to be very poor on what turns out to be a higly fragmented
index.  Is there a way to help it to understand otherwise??

Maybe this is a well-understood problem/deficiency; but, is there any reason
why Btree scan can't sort result by ctid for index tuples with same column
value (_bt_steppage() or btgettuple())?  Or maybe the problem could be
mitigated by changing the behavior during INESRT?  In the meantime, I'll be
implementing a reindex job.

Thanks,
Justin

--

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

Christian Castelli | 18 May 13:10 2016
Picon
Gravatar

Locks when launching function across schemata

Hi,
I have defined a function into public schema which permits to execute a set of SQL statements on every schema:

CREATE OR REPLACE FUNCTION "public"."multiddl"("sql" text)
  RETURNS "pg_catalog"."bool" AS $BODY$DECLARE
    r record;
BEGIN
  FOR r IN
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name NOT LIKE 'pg_%' AND
              schema_name NOT IN('information_schema')
    LOOP
      EXECUTE 'SET search_path TO ' ||  r.schema_name || ', public';
      RAISE NOTICE 'Executing for %', r.schema_name;
      EXECUTE sql;
  END LOOP;
    RETURN 't';
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

Then I have executed this statement:
 
SELECT * FROM public.multiddl($$

CREATE TYPE enum_report_type AS ENUM ('A', 'B'); 
CREATE TABLE "report_layout" (
    "id" serial,
    "report_type" enum_report_type NOT NULL,     
    "layout_name" varchar(255) NOT NULL,
    "report_config" jsonb,
    "created_by" integer,
    "id_cliente" integer,
    "builder" varchar(255),
    "can_modify" bool,
    "can_delete" bool,
    "is_default" bool,
    "created_on" timestamp NULL,
    "modified_on" timestamp NULL,
    "modified_by" integer,
    CONSTRAINT "fk_clienti_report_layout" FOREIGN KEY ("id_cliente") REFERENCES "public"."customer" ("id"),
    CONSTRAINT "fk_utenti_report_layout_create" FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    CONSTRAINT "fk_utenti_report_layout_modify" FOREIGN KEY ("modified_by") REFERENCES "user" ("id")
)
WITH (OIDS=FALSE);
ALTER TABLE report ADD COLUMN id_layout integer;
$$);


All locks derived from this statement seem to be related to public views, that are commodity views which ties together all schemata. Example of view:

CREATE OR REPLACE VIEW "public"."v_contacts" AS
 SELECT 'public'::text AS schema,
    [FIELDS]
UNION
 SELECT 'customer2'::text AS schema,
   [FIELDS]
   FROM ((((customer c
     JOIN customer2.table1 g ON ...
     JOIN customer2.table2 s ON ...
     JOIN customer2.reparti r ON ...
     JOIN customer2.contatto co ON ...


I cannot understand why every query which uses union view like the before mentioned is stuck.
Thanks for any advice.

--
Christian Castelli
skype:  christrack
John Gorman | 13 May 21:59 2016

Database transaction with intermittent slow responses

Transactions to table, ChangeHistory, have recently become intermittently slow and is increasing becoming slower.
 
* No database configuration changes have been made recently
* We have run vacuum analyze
* We have tried backing up and reloading the table (data, indexes, etc)
 
Some transactions respond quickly (200 ms) and others take over 55 seconds (we cancel the query after 55 seconds – our timeout SLA). The problem has recently become very bad. It is the same query being issued but with different parameters.
 
If the transaction takes over 55 seconds and I run the query manually (with or without EXPLAIN ANALYZE) it returns quickly (a few hundred ms). In case I am looking at cache, I have a list of other queries (just different parameters) that have timed out and when I run them (without the limit even) the response is very timely.
 
Any help or insight would be great.
 
NOTE: our application is connecting to the database via JDBC and we are using PreparedStatements. I have provided full details so all information is available, but please let me know if any other information is needed – thx in advance.
 
p306=> EXPLAIN ANALYZE SELECT * FROM ChangeHistory WHERE Category BETWEEN 'Employee' AND 'Employeezz' AND PrimaryKeyOfChange BETWEEN '312313' AND '312313!zz' ORDER BY ChgTS DESC, ChgUser DESC, Category DESC, PrimaryKeyOfChange DESC LIMIT 11;
                                            QUERY PLAN                                            
------------------------------------------------------------------------------------------------------
Limit  (cost=33.66..33.67 rows=1 width=136) (actual time=0.297..0.297 rows=11 loops=1)
   ->  Sort  (cost=33.66..33.67 rows=1 width=136) (actual time=0.297..0.297 rows=11 loops=1)
         Sort Key: chgts, chguser, category, primarykeyofchange
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Index Scan using changehistory_idx4 on changehistory  (cost=0.00..33.65 rows=1 width=136) (actual time=0.046..
0.239 rows=85 loops=1)
               Index Cond: (((primarykeyofchange)::text >= '312313'::text) AND ((primarykeyofchange)::text <= '312313!zz'::
text))
               Filter: (((category)::text >= 'Employee'::text) AND ((category)::text <= 'Employeezz'::text))
Total runtime: 0.328 ms
(8 rows)
 
>>>
History this week of counts with good response times vs timeouts.
 
| Date       | Success # | Time Out # | Avg. Success Secs |
|------------+-----------+------------+-------------------|
| 2016-05-09 |        18 |         31 |               7.9 |
| 2016-05-10 |        17 |         25 |              10.5 |
| 2016-05-11 |        27 |         33 |              10.1 |
| 2016-05-12 |        68 |         24 |               9.9 |
 
 
>>> Sample transaction response times
 
| Timestamp         | Tran ID        | Resp MS | Resp CD
--------------------+----------------+---------+--------
2016-05-10 06:20:19 | ListChangeHist | 55,023  | TIMEOUT
2016-05-10 07:47:34 | ListChangeHist | 55,017  | TIMEOUT
2016-05-10 07:48:00 | ListChangeHist |  9,866  | OK
2016-05-10 07:48:10 | ListChangeHist |  2,327  | OK
2016-05-10 07:59:23 | ListChangeHist | 55,020  | TIMEOUT
2016-05-10 08:11:20 | ListChangeHist | 55,030  | TIMEOUT
2016-05-10 08:31:45 | ListChangeHist |  4,216  | OK
2016-05-10 08:35:09 | ListChangeHist |  7,898  | OK
2016-05-10 08:36:18 | ListChangeHist |  9,810  | OK
2016-05-10 08:36:56 | ListChangeHist | 55,027  | TIMEOUT
2016-05-10 08:37:33 | ListChangeHist | 46,433  | OK
2016-05-10 08:38:09 | ListChangeHist | 55,019  | TIMEOUT
2016-05-10 08:53:43 | ListChangeHist | 55,019  | TIMEOUT
2016-05-10 09:45:09 | ListChangeHist | 55,022  | TIMEOUT
2016-05-10 09:46:13 | ListChangeHist | 55,017  | TIMEOUT
2016-05-10 09:49:27 | ListChangeHist | 55,011  | TIMEOUT
2016-05-10 09:52:12 | ListChangeHist | 55,018  | TIMEOUT
2016-05-10 09:57:42 | ListChangeHist |  9,462  | OK
2016-05-10 10:05:21 | ListChangeHist | 55,016  | TIMEOUT
2016-05-10 10:05:29 | ListChangeHist |    136  | OK
2016-05-10 10:05:38 | ListChangeHist |  1,517  | OK
 
Artifacts
======================
 
$ >uname -a
SunOS ***** 5.10 Generic_150400-30 sun4v sparc sun4v
 
Memory    : 254G phys mem, 207G free mem.
Processors: 32 - CPU is mostly 80% free
 
>>>
p306=> select version();
                                                      version                                                     
---------------------------------------------------------------------------------------------------
PostgreSQL 9.1.14 on sparc-sun-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit
 
>>>
p306=> \dt+ changehistory
                      List of relations
Schema |     Name      | Type  | Owner | Size  | Description
--------+---------------+-------+-------+-------+-------------
public | changehistory | table | p306  | 17 GB |
 
>>>
p306=> \di+ changehistory*
                                   List of relations
Schema |         Name          | Type  | Owner |     Table     |  Size   | Description
--------+-----------------------+-------+-------+---------------+---------+-------------
public | changehistory_idx1    | index | p306  | changehistory | 9597 MB |
public | changehistory_idx3    | index | p306  | changehistory | 11 GB   |
public | changehistory_idx4    | index | p306  | changehistory | 4973 MB |
public | changehistory_pkey    | index | p306  | changehistory | 2791 MB |
public | changehistory_search2 | index | p306  | changehistory | 9888 MB |
public | changehistory_search3 | index | p306  | changehistory | 10 GB   |
public | changehistory_search4 | index | p306  | changehistory | 9240 MB |
public | changehistory_search5 | index | p306  | changehistory | 8373 MB |
(8 rows)
 
 
>>>
p306=> select count(*) from changehistory ;
   count  
------------
129,185,024
 
>>>
Show all (filtered)
======================================================
 
              name               | setting
---------------------------------+--------------------
autovacuum                      | on
autovacuum_analyze_scale_factor | 0.001
autovacuum_analyze_threshold    | 500
autovacuum_freeze_max_age       | 200000000
autovacuum_max_workers          | 5
autovacuum_naptime              | 1min
autovacuum_vacuum_cost_delay    | 0
autovacuum_vacuum_cost_limit    | -1
autovacuum_vacuum_scale_factor  | 0.001
autovacuum_vacuum_threshold     | 500
bgwriter_delay                  | 200ms
block_size                      | 8192
check_function_bodies           | on
checkpoint_completion_target    | 0.9
checkpoint_segments             | 256
checkpoint_timeout              | 1h
checkpoint_warning              | 30s
client_encoding                 | UTF8
commit_delay                    | 0
commit_siblings                 | 5
cpu_index_tuple_cost            | 0.005
cpu_operator_cost               | 0.0025
cpu_tuple_cost                  | 0.01
cursor_tuple_fraction           | 0.1
deadlock_timeout                | 1s
default_statistics_target       | 100
default_transaction_deferrable  | off
default_transaction_isolation   | read committed
default_transaction_read_only   | off
default_with_oids               | off
effective_cache_size            | 8GB
from_collapse_limit             | 8
fsync                           | on
full_page_writes                | on
ignore_system_indexes           | off
join_collapse_limit             | 8
krb_caseins_users               | off
lo_compat_privileges            | off
maintenance_work_mem            | 1GB
max_connections                 | 350
max_files_per_process           | 1000
max_function_args               | 100
max_identifier_length           | 63
max_index_keys                  | 32
max_locks_per_transaction       | 64
max_pred_locks_per_transaction  | 64
max_prepared_transactions       | 0
max_stack_depth                 | 2MB
max_wal_senders                 | 5
random_page_cost                | 4
segment_size                    | 1GB
seq_page_cost                   | 1
server_encoding                 | UTF8
server_version                  | 9.1.14
shared_buffers                  | 2GB
sql_inheritance                 | on
statement_timeout               | 0
synchronize_seqscans            | on
synchronous_commit              | on
synchronous_standby_names       |
tcp_keepalives_count            | 0
tcp_keepalives_idle             | -1
tcp_keepalives_interval         | 0
track_activities                | on
track_activity_query_size       | 1024
track_counts                    | on
track_functions                 | none
transaction_deferrable          | off
transaction_isolation           | read committed
transaction_read_only           | off
transform_null_equals           | off
update_process_title            | on
vacuum_cost_delay               | 0
vacuum_cost_limit               | 200
vacuum_cost_page_dirty          | 20
vacuum_cost_page_hit            | 1
vacuum_cost_page_miss           | 10
vacuum_defer_cleanup_age        | 0
vacuum_freeze_min_age           | 50000000
vacuum_freeze_table_age         | 150000000
 
John Gorman | Manager of Production Support, Architecture, Release Engineering | Eldorado | a Division of MPHASIS | www.eldoinc.com/ |
5353 North 16th Street, Suite 400, Phoenix, Arizona 85016-3228 | Tel 602.604.3100 | Fax: 602.604.3115
 
 
 
Владимир | 6 May 11:48 2016
Picon
Gravatar

LIKE pattern

Hello.

It seems my quite complex query runs 10 times faster on "some_column 
LIKE '%test_1' " vs "some_column LIKE 'test_1' "
So I just add "%"  to the pattern...

Both query plans use same indexes.

--

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

George Neuner | 28 Apr 04:41 2016
Picon
Picon

testing - please ignore


--

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

Artem Tomyuk | 26 Apr 16:03 2016

Poor disk (virtio) Performance Inside KVM virt-machine vs host machine

Hi All. 

I've noticed that there is a huge (more than ~3x slower) performance difference between KVM guest and host machine. 
Host machine: 
dell r720xd 
RAID10 with 12 SAS 15 k drives and RAID0  with 2*128 GB INTEL SSD drives in Dell CacheCade mode.

On the KVM guest:

 /usr/pgsql-9.4/bin/pg_test_fsync -f test.sync

5 seconds per test

O_DIRECT supported on this platform for open_datasync and open_sync.


Compare file sync methods using one 8kB write:

(in wal_sync_method preference order, except fdatasync

is Linux's default)

        open_datasync                      5190.279 ops/sec     193 usecs/op

        fdatasync                          4022.553 ops/sec     249 usecs/op

        fsync                              3069.069 ops/sec     326 usecs/op

        fsync_writethrough                              n/a

        open_sync                          4892.348 ops/sec     204 usecs/op


Compare file sync methods using two 8kB writes:

(in wal_sync_method preference order, except fdatasync

is Linux's default)

        open_datasync                      2406.577 ops/sec     416 usecs/op

        fdatasync                          4309.413 ops/sec     232 usecs/op

        fsync                              3518.844 ops/sec     284 usecs/op

        fsync_writethrough                              n/a

        open_sync                          1159.604 ops/sec     862 usecs/op


Compare open_sync with different write sizes:

(This is designed to compare the cost of writing 16kB

in different write open_sync sizes.)

         1 * 16kB open_sync write          3700.689 ops/sec     270 usecs/op

         2 *  8kB open_sync writes         2581.405 ops/sec     387 usecs/op

         4 *  4kB open_sync writes         1318.871 ops/sec     758 usecs/op

         8 *  2kB open_sync writes          698.640 ops/sec    1431 usecs/op

        16 *  1kB open_sync writes          262.506 ops/sec    3809 usecs/op


Test if fsync on non-write file descriptor is honored:

(If the times are similar, fsync() can sync data written

on a different descriptor.)

        write, fsync, close                3071.141 ops/sec     326 usecs/op

        write, close, fsync                3303.946 ops/sec     303 usecs/op


Non-Sync'ed 8kB writes:

        write                            251321.188 ops/sec       4 usecs/op


On the host machine:

/usr/pgsql-9.4/bin/pg_test_fsync -f test.sync

5 seconds per test

O_DIRECT supported on this platform for open_datasync and open_sync.


Compare file sync methods using one 8kB write:

(in wal_sync_method preference order, except fdatasync

is Linux's default)

        open_datasync                     11364.136 ops/sec      88 usecs/op

        fdatasync                         12352.160 ops/sec      81 usecs/op

        fsync                              9833.745 ops/sec     102 usecs/op

        fsync_writethrough                              n/a

        open_sync                         14938.531 ops/sec      67 usecs/op


Compare file sync methods using two 8kB writes:

(in wal_sync_method preference order, except fdatasync

is Linux's default)

        open_datasync                      7703.471 ops/sec     130 usecs/op

        fdatasync                         11494.492 ops/sec      87 usecs/op

        fsync                              9029.837 ops/sec     111 usecs/op

        fsync_writethrough                              n/a

        open_sync                          6504.138 ops/sec     154 usecs/op


Compare open_sync with different write sizes:

(This is designed to compare the cost of writing 16kB

in different write open_sync sizes.)

         1 * 16kB open_sync write         14113.912 ops/sec      71 usecs/op

         2 *  8kB open_sync writes         7843.234 ops/sec     127 usecs/op

         4 *  4kB open_sync writes         3995.702 ops/sec     250 usecs/op

         8 *  2kB open_sync writes         1788.979 ops/sec     559 usecs/op

        16 *  1kB open_sync writes          937.177 ops/sec    1067 usecs/op


Test if fsync on non-write file descriptor is honored:

(If the times are similar, fsync() can sync data written

on a different descriptor.)

        write, fsync, close               10144.280 ops/sec      99 usecs/op

        write, close, fsync                8378.558 ops/sec     119 usecs/op


Non-Sync'ed 8kB writes:

        write                            159176.122 ops/sec       6 usecs/op


The file system "inside" and "outside" the same - ext4 on LVM. Disk scheduler  "inside" and "outside" set to "noop". Fstab options same to, setted to rw,noatime,nodiratime,barrier=0. OS on host and guest the same CentOS release 6.5 (Final).

Guest volume options:

Disk bus: Virtio

Cache mode: none

IO mode: native 


Any ideas? 





 




Gmane