[PERFORM] Slow query
Maybe someone can explain this. The following SQL will reproduce our issue:
DROP TABLE IF EXISTS t1 CASCADE;
CREATE TABLE t1 (name text,
state text);
CREATE INDEX t1_name ON t1(name);
CREATE INDEX t1_state ON t1(state);
CREATE INDEX t1_name_state ON t1(name,state);
-- Create some sample data
DO $$
DECLARE
states text[] := array['UNKNOWN', 'TODO', 'DONE', 'UNKNOWN'];
BEGIN
FOR v IN 1..20 LOOP
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
END LOOP;
END $$;
CREATE OR REPLACE FUNCTION state_to_int(state character varying) RETURNS
integer
LANGUAGE plpgsql IMMUTABLE STRICT
AS $$BEGIN
IF state = 'UNKNOWN' THEN RETURN 0;
ELSIF state = 'TODO' THEN RETURN 1;
ELSIF state = 'DONE' THEN RETURN 2;
ELSIF state = 'NOT REQUIRED' THEN RETURN 3;
ELSE RAISE EXCEPTION 'state_to_int called with invalid state value';
END IF;
END;$$;
CREATE OR REPLACE FUNCTION int_to_state(state integer) RETURNS character
varying
LANGUAGE plpgsql IMMUTABLE STRICT
AS $$BEGIN
IF state = 0 THEN RETURN 'UNKNOWN';
ELSIF state = 1 THEN RETURN 'TODO';
ELSIF state = 2 THEN RETURN 'DONE';
ELSIF state = 3 THEN RETURN 'NOT REQUIRED';
ELSE RAISE EXCEPTION 'int_to_state called with invalid state value';
END IF;
END;$$;
-- Why is this a lot slower
explain (analyse, buffers) select name,
int_to_state(min(state_to_int(state))) as status from t1 group by t1.name;
-- Than this?
explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE',
'NOT REQUIRED'])[min(
CASE state
WHEN 'UNKNOWN' THEN 0
WHEN 'TODO' THEN 1
WHEN 'DONE' THEN 2
WHEN 'NOT REQUIRED' THEN 3
END)] AS status from t1 group by t1.name;
-- This is also very much slower
explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE',
'NOT REQUIRED'])[min(state_to_int(state))] AS status from t1 group by
t1.name;
This was done on:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
We get results like this:
QUERY PLAN
---
GroupAggregate (cost=0.42..280042.62 rows=208120 width=15) (actual
time=0.076..2439.066 rows=20 loops=1)
Buffers: shared hit=53146
- Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=80
width=15) (actual time=0.009..229.477 rows=80 loops=1)
Buffers: shared hit=53146
Total runtime: 2460.860 ms
(5 rows)
QUERY PLAN
---
GroupAggregate (cost=0.42..36012.62 rows=208120 width=15) (actual
time=0.017..559.384 rows=20 loops=1)
Buffers: shared hit=53146
- Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=80
width=15) (actual time=0.008..197.133 rows=80 loops=1)
Buffers: shared hit=53146
Total runtime: 574.550 ms
(5 rows)
QUERY PLAN
---
GroupAggregate (cost=0.42..228012.62 rows=208120 width=15) (actual
time=0.042..2089.367 rows=20 loops=1)
Buffers: shared hit=53146
- Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=80
width=15) (actual time=0.008..237.854 rows=80 loops=1)
Buffers: shared hit=53146
Total runtime: 2111.004 ms
(5 rows)
We cannot change our table structure to reflect something more sensible.
What we would really like to know is why using functions is so much slower
than the unreadable method.
Regards
Ross
Re: [PERFORM] postgres 9.3 vs. 9.4
Hi Merlin, you are right, in 9.4 the debug_assertions are on: # /etc/init.d/postgresql-9.4 start Starting postgresql-9.4 service: [ OK ] # psql -U postgres psql (9.4beta2) Type help for help. postgres=# select name,setting from pg_settings where name='debug_assertions'; name | setting --+- debug_assertions | on (1 row) postgres=# \q # /etc/init.d/postgresql-9.4 stop Stopping postgresql-9.4 service: [ OK ] # /etc/init.d/postgresql-9.3 start Starting postgresql-9.3 service: [ OK ] # psql -U postgres psql (9.4beta2, server 9.3.5) Type help for help. postgres=# select name,setting from pg_settings where name='debug_assertions'; name | setting --+- debug_assertions | off (1 row) postgres=# \q # The rpms are coming from Postgres official repo: http://yum.postgresql.org/9.4/redhat/rhel-$releasever-$basearch Tigran. - Original Message - From: Merlin Moncure mmonc...@gmail.com To: Mark Kirkwood mark.kirkw...@catalyst.net.nz Cc: Tigran Mkrtchyan tigran.mkrtch...@desy.de, postgres performance list pgsql-performance@postgresql.org Sent: Monday, September 22, 2014 3:37:50 PM Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 On Fri, Sep 19, 2014 at 6:58 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 19/09/14 19:24, Mkrtchyan, Tigran wrote: - Original Message - From: Mark Kirkwood mark.kirkw...@catalyst.net.nz To: Tigran Mkrtchyan tigran.mkrtch...@desy.de Cc: Merlin Moncure mmonc...@gmail.com, postgres performance list pgsql-performance@postgresql.org Sent: Friday, September 19, 2014 8:26:27 AM Subject: Re: [PERFORM] postgres 9.3 vs. 9.4 On 19/09/14 17:53, Mkrtchyan, Tigran wrote: - Original Message - From: Mark Kirkwood mark.kirkw...@catalyst.net.nz Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3 one for 9.4), see below for results. I'm running xfs on them with trim/discard enabled: $ mount|grep pg /dev/sdd4 on /mnt/pg94 type xfs (rw,discard) /dev/sdc4 on /mnt/pg93 type xfs (rw,discard) I'm *not* seeing any significant difference between 9.3 and 9.4, and the numbers are both about 2x your best number, which is food for thought (those P320's should toast my M550 for write performance...). cool! any details on OS and other options? I still get the same numbers as before. Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation). I saw the suggestion that Didier made to run 9.3 on the SSD that you were using for 9.4, and see if it suddenly goes slow - then we'd know it's something about the disk (or filesystem/mount options). Can you test this? swapping the disks did not change the results. Do you mean that 9.3 was still faster using the disk that 9.4 had used? If so that strongly suggests that there is something you have configured differently in the 9.4 installation [1]. Not wanting to sound mean - but it is really easy to accidentally connect to the wrong instance when there are two on the same box (ahem, yes , done it myself). So perhaps another look at the 9.4 vs 9.3 setup (or even posti the config files postgresql.conf + postgresql.auto.conf for 9.4 here). Huh. Where did the 9.4 build come from? I wonder if there are some debugging options set. Can you check 9.4 pg_settings for value ofdebug_assertions? If it's set true, you might want to consider hand compiling postgres until 9.4 is released... merlin -- 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] Slow query
Ross Elliott-2 wrote
Maybe someone can explain this. The following SQL will reproduce our
issue:
DROP TABLE IF EXISTS t1 CASCADE;
CREATE TABLE t1 (name text,
state text);
CREATE INDEX t1_name ON t1(name);
CREATE INDEX t1_state ON t1(state);
CREATE INDEX t1_name_state ON t1(name,state);
-- Create some sample data
DO $$
DECLARE
states text[] := array['UNKNOWN', 'TODO', 'DONE', 'UNKNOWN'];
BEGIN
FOR v IN 1..20 LOOP
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
END LOOP;
END $$;
CREATE OR REPLACE FUNCTION state_to_int(state character varying) RETURNS
integer
LANGUAGE plpgsql IMMUTABLE STRICT
AS $$BEGIN
IF state = 'UNKNOWN' THEN RETURN 0;
ELSIF state = 'TODO' THEN RETURN 1;
ELSIF state = 'DONE' THEN RETURN 2;
ELSIF state = 'NOT REQUIRED' THEN RETURN 3;
ELSE RAISE EXCEPTION 'state_to_int called with invalid state value';
END IF;
END;$$;
CREATE OR REPLACE FUNCTION int_to_state(state integer) RETURNS character
varying
LANGUAGE plpgsql IMMUTABLE STRICT
AS $$BEGIN
IF state = 0 THEN RETURN 'UNKNOWN';
ELSIF state = 1 THEN RETURN 'TODO';
ELSIF state = 2 THEN RETURN 'DONE';
ELSIF state = 3 THEN RETURN 'NOT REQUIRED';
ELSE RAISE EXCEPTION 'int_to_state called with invalid state value';
END IF;
END;$$;
-- Why is this a lot slower
explain (analyse, buffers) select name,
int_to_state(min(state_to_int(state))) as status from t1 group by t1.name;
-- Than this?
explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE',
'NOT REQUIRED'])[min(
CASE state
WHEN 'UNKNOWN' THEN 0
WHEN 'TODO' THEN 1
WHEN 'DONE' THEN 2
WHEN 'NOT REQUIRED' THEN 3
END)] AS status from t1 group by t1.name;
-- This is also very much slower
explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE',
'NOT REQUIRED'])[min(state_to_int(state))] AS status from t1 group by
t1.name;
This was done on:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
We get results like this:
QUERY PLAN
---
GroupAggregate (cost=0.42..280042.62 rows=208120 width=15) (actual
time=0.076..2439.066 rows=20 loops=1)
Buffers: shared hit=53146
- Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=80
width=15) (actual time=0.009..229.477 rows=80 loops=1)
Buffers: shared hit=53146
Total runtime: 2460.860 ms
(5 rows)
QUERY PLAN
---
GroupAggregate (cost=0.42..36012.62 rows=208120 width=15) (actual
time=0.017..559.384 rows=20 loops=1)
Buffers: shared hit=53146
- Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=80
width=15) (actual time=0.008..197.133 rows=80 loops=1)
Buffers: shared hit=53146
Total runtime: 574.550 ms
(5 rows)
QUERY PLAN
---
GroupAggregate (cost=0.42..228012.62 rows=208120 width=15) (actual
time=0.042..2089.367 rows=20 loops=1)
Buffers: shared hit=53146
- Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=80
width=15) (actual time=0.008..237.854 rows=80 loops=1)
Buffers: shared hit=53146
Total runtime: 2111.004 ms
(5 rows)
We cannot change our table structure to reflect something more sensible.
What we would really like to know is why using functions is so much slower
than the unreadable method.
Regards
Ross
Pl/pgsql functions are black boxes and expensive to execute; you should
define these functions as SQL functions and see if that helps.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slow-query-tp5820086p5820096.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
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] postgres 9.3 vs. 9.4
On Tue, Sep 23, 2014 at 7:58 AM, Mkrtchyan, Tigran tigran.mkrtch...@desy.de wrote: Hi Merlin, you are right, in 9.4 the debug_assertions are on: # /etc/init.d/postgresql-9.4 start Starting postgresql-9.4 service: [ OK ] # psql -U postgres psql (9.4beta2) Type help for help. postgres=# select name,setting from pg_settings where name='debug_assertions'; name | setting --+- debug_assertions | on (1 row) (plz try to not top-post). That's not not really unexpected: 9.4 is still in beta. If you're just doing raw performance testing consider building a postgres instance from source (but, instead of compiling into /usr/local/bin, I'd keep it all in private user folder for easy removal). For example, if I downloaded the source into /home/mmoncure/pgdev/src, i'd approximately do: cd /home/mmoncure/pgdev/src ./configure --prefix=/home/mmoncure/pgdev # if configure gripes about missing readline, go grab the libreadline-dev rpm etc and repeat above make -j4 make install export PATH=/home/mmoncure/pgdev/bin:$PATH export PGDATA=/home/mmoncure/pgdev/data # use C locale. may not be appropriate in your case initdb --no-locale --encoding=UTF8 pg_ctl start This should suffice any beta performance testing you need to do. When 9.4 proper comes out, just stop the database and kill the pgdev folder (taking a backup first if you need to preserve stuff). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Which update action quicker?
Hello list, For a big table with more than 1,000,000 records, may I know which update is quicker please? (1) update t1 set c1 = a.c1 from a where pk and t1.c1 a.c1; .. update t1 set c_N = a.c_N from a where pk and t1.c_N a.c_N; (2) update t1 set c1 = a.c1 , c2 = a.c2, ... c_N = a.c_N from a where pk AND ( t1.c1 a.c1 OR t1.c2 a.c2. t1.c_N a.c_N) Or other quicker way for update action? Thank you Emi
