From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query
Date: Tue, 23 Sep 2014 06:05:16 -0700 (PDT)
Message-ID: <1411477516975-5820096.post@n5.nabble.com> (raw)
In-Reply-To: <CAFha2jKMoeHrAtZeGfJoT04C=BByO3=gTfSSHpDG5VqmY3ZNgA@mail.gmail.com>
References: <CAFha2jKMoeHrAtZeGfJoT04C=BByO3=gTfSSHpDG5VqmY3ZNgA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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..200000 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=200000 loops=1)
> Buffers: shared hit=53146
> -> Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=800000
> width=15) (actual time=0.009..229.477 rows=800000 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=200000 loops=1)
> Buffers: shared hit=53146
> -> Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=800000
> width=15) (actual time=0.008..197.133 rows=800000 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=200000 loops=1)
> Buffers: shared hit=53146
> -> Index Scan using t1_name on t1 (cost=0.42..21931.42 rows=800000
> width=15) (actual time=0.008..237.854 rows=800000 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: Slow query
In-Reply-To: <1411477516975-5820096.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox