[PERFORM] Bad optimization/planning on Postgres window-based queries (partition by(, group by?)) - 1000x speedup

[and]

see
http://stackoverflow.com/questions/26237463/bad-optimization-planning-on-postgres-window-based-queries-partition-by-group
for details



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Bad-optimization-planning-on-Postgres-window-based-queries-partition-by-group-by-1000x-speedup-tp5822190.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] help: function failing

[Andrew Dunstan]

On 10/07/2014 04:41 PM, Sergey Konoplev wrote:

On Thu, Oct 2, 2014 at 4:00 PM, George Neuner  wrote:

--- code 
CREATE OR REPLACE FUNCTION gen_random()
   RETURNS double precision AS
$BODY$
DECLARE
num   float8 := 0;
den   float8 := 281474976710655; -- 0x
bytes bytea[6];
BEGIN
-- get random bytes from crypto module
bytes := ext.gen_random_bytes(6);

-- assemble a double precision value
num := num + get_byte( bytes, 0 );
FOR i IN 1..5 LOOP
   num := num * 256;
   num := num + get_byte( bytes, i );
   END LOOP;

   -- normalize value to range 0.0 .. 1.0
   RETURN num / den;
END;
$BODY$
   LANGUAGE plpgsql VOLATILE;
--- code 

The error is:
ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: PL/pgSQL function gen_random() line 8 at assignment

which, if I'm counting correctly, is
bytes := ext.gen_random_bytes(6);

Guessing on the name of ext.gen_random_bytes(6) it returns a value
that is incompatible with bytea[] array representation time from time,
so take a closer look at ext.gen_random_bytes() first. You can test
the case using DO block.


If I comment out that line, it then tells me  get_byte()   is undefined,
which should be impossible because it's built in.

Feels like somewhere inside ext.gen_random_bytes() you set a
search_path that allows to see get_byte() and the search_path that was
set before the gen_random() call doesn't allow it.



Why does this code want an array of byteas?

It looks like the code thinks bytea[6] is a declaration of a bytea of 
length 6, which of course it is not. Shouldn't it just be declared as:


bytes bytea;

?


Oh, and pgsql-performance is completely the wrong forum for this query. 
usage questions should be on pgsql-general.


cheers

andrew


--
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] help: function failing

[Sergey Konoplev]

On Thu, Oct 2, 2014 at 4:00 PM, George Neuner  wrote:
> --- code 
> CREATE OR REPLACE FUNCTION gen_random()
>   RETURNS double precision AS
> $BODY$
> DECLARE
>num   float8 := 0;
>den   float8 := 281474976710655; -- 0x
>bytes bytea[6];
> BEGIN
>-- get random bytes from crypto module
>bytes := ext.gen_random_bytes(6);
>
>-- assemble a double precision value
>num := num + get_byte( bytes, 0 );
>FOR i IN 1..5 LOOP
>   num := num * 256;
>   num := num + get_byte( bytes, i );
>   END LOOP;
>
>   -- normalize value to range 0.0 .. 1.0
>   RETURN num / den;
> END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE;
> --- code 
>
> The error is:
> ERROR: array value must start with "{" or dimension information
> SQL state: 22P02
> Context: PL/pgSQL function gen_random() line 8 at assignment
>
> which, if I'm counting correctly, is
> bytes := ext.gen_random_bytes(6);

Guessing on the name of ext.gen_random_bytes(6) it returns a value
that is incompatible with bytea[] array representation time from time,
so take a closer look at ext.gen_random_bytes() first. You can test
the case using DO block.

> If I comment out that line, it then tells me  get_byte()   is undefined,
> which should be impossible because it's built in.

Feels like somewhere inside ext.gen_random_bytes() you set a
search_path that allows to see get_byte() and the search_path that was
set before the gen_random() call doesn't allow it.

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray...@gmail.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] query plan question, nested loop vs hash join

[Marti Raudsepp]

On Fri, Oct 3, 2014 at 6:38 PM, Andrey Lizenko  wrote:
> Is it possible to force optimizer choose the second plan without doing  "set
> enable_hashjoin = off;" ?
>
> Increasing  of 'effective_cache_size' leads to similar thing with mergejoin,
> other options (work_mem, shared_buffers. etc) do not change anything.

Have you tried changing random_page_cost?

In small databases where most of the data is cached anyway, lowering
random_page_cost to somewhere between 1 and 2 usually leads to better
planner decisions.

Regards,
Marti


-- 
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] pg_basebackup - odd performance

[Mike Blackwell]

Thanks for your reply.  Adding '-c fast' does seem to improve the initial
delay.  I'm still seeing delays of several minutes between write bursts.
The server has light OLTP loading.

__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *

On Mon, Oct 6, 2014 at 9:59 PM, Jov  wrote:

> what is the cmd？the default checkpoint method is spread,so pg_basebackup
> will wait the checkpoint complete on the master.you can set the checkpoint
> method to fast to avoid the waiting.
> 2014年10月7日 2:54 AM于 "Mike Blackwell" 写道：
>
> I'm seeing some strange behavior running pg_basebackup on 9.3.5.  It
>> appears that there are long pauses - up to a couple of minutes - between
>> relatively short bursts of disk activity.
>>
>> I'm not noticing any lock requests outstanding.  What might I be missing?
>>
>>
>> __
>> *Mike Blackwell | Technical Analyst, Distribution Services/Rollout
>> Management | RR Donnelley*
>> 1750 Wallace Ave | St Charles, IL 60174-3401
>> Office: 630.313.7818
>> mike.blackw...@rrd.com
>> http://www.rrdonnelley.com
>>
>>
>> 
>> * *
>>
>

[PERFORM] query plan question, nested loop vs hash join

[Andrey Lizenko]

Hi,
I have similar problem as in
http://www.postgresql.org/message-id/flat/52b311c4.1070...@gmail.com#52b311c4.1070...@gmail.com

server version is 9.3.4

Here is only two quite simple tables:

db_new=# \d activities_example
  Table "public.activities_example"
 Column |  Type   | Modifiers
+-+---
 id | integer |
 order_chain_id | integer |
Indexes:
"activities_example_idx" btree (order_chain_id)

db_new=# \d orders_example
Table "public.orders_example"
 Column |  Type   | Modifiers
+-+---
 id | integer |

Number of rows as below:

db_new=# select count(*) from activities_example ;
  count
-
 3059965

db_new=# select count(*) from orders_example ;
 count
---
 19038

db_new=# select count(*) from activities_example where order_chain_id in
(select id from orders_example);
 count
---
 91426
(1 row)


and I can see that planner uses hashjoin with all enabled options and
nested loop with disabled parameter:

db_new=# explain analyze select * from activities_example where
order_chain_id in (select id from orders_example);
 QUERY PLAN

 Hash Semi Join  (cost=513.36..57547.59 rows=89551 width=8) (actual
time=18.340..966.367 rows=91426 loops=1)
   Hash Cond: (activities_example.order_chain_id = orders_example.id)
   ->  Seq Scan on activities_example  (cost=0.00..44139.65 rows=3059965
width=8) (actual time=0.018..294.216 rows=3059965 loops=1)
   ->  Hash  (cost=275.38..275.38 rows=19038 width=4) (actual
time=5.458..5.458 rows=19038 loops=1)
 Buckets: 2048  Batches: 1  Memory Usage: 670kB
 ->  Seq Scan on orders_example  (cost=0.00..275.38 rows=19038
width=4) (actual time=0.015..2.308 rows=19038 loops=1)
 Total runtime: 970.234 ms
(7 rows)

db_new=# set enable_hashjoin = off;
SET
db_new=# explain analyze select * from activities_example where
order_chain_id in (select id from orders_example);
 QUERY
PLAN
-
 Nested Loop  (cost=1629.09..166451.01 rows=89551 width=8) (actual
time=16.091..116.476 rows=91426 loops=1)
   ->  Unique  (cost=1628.66..1723.85 rows=19038 width=4) (actual
time=15.929..23.156 rows=19038 loops=1)
 ->  Sort  (cost=1628.66..1676.25 rows=19038 width=4) (actual
time=15.892..19.884 rows=19038 loops=1)
   Sort Key: orders_example.id
   Sort Method: external sort  Disk: 264kB
   ->  Seq Scan on orders_example  (cost=0.00..275.38
rows=19038 width=4) (actual time=0.015..2.747 rows=19038 loops=1)
   ->  Index Scan using activities_example_idx on activities_example
 (cost=0.43..8.60 rows=5 width=8) (actual time=0.002..0.004 rows=5
loops=19038)
 Index Cond: (order_chain_id = orders_example.id)
 Total runtime: 121.366 ms
(9 rows)

second runtime is much more quicker.

What is the reason of "Seq Scan on activities_example" in the first case?
Is it possible to force optimizer choose the second plan without doing
 "set enable_hashjoin = off;" ?

Increasing  of 'effective_cache_size' leads to similar thing with
mergejoin,
other options (work_mem, shared_buffers. etc) do not change anything.

Thanks in advance.

-- 
Regards, Andrey Lizenko

[PERFORM] help: function failing

[George Neuner]

Hi,

I am using Postgresql 9.3.5 on Ubuntu and I have a sudden, unexplained 
failure in a function that has been working for a long time.


--- code 
CREATE OR REPLACE FUNCTION gen_random()
  RETURNS double precision AS
$BODY$
DECLARE
   num   float8 := 0;
   den   float8 := 281474976710655; -- 0x
   bytes bytea[6];
BEGIN
   -- get random bytes from crypto module
   bytes := ext.gen_random_bytes(6);

   -- assemble a double precision value
   num := num + get_byte( bytes, 0 );
   FOR i IN 1..5 LOOP
  num := num * 256;
  num := num + get_byte( bytes, i );
  END LOOP;

  -- normalize value to range 0.0 .. 1.0
  RETURN num / den;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
--- code 

The error is:
ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: PL/pgSQL function gen_random() line 8 at assignment

which, if I'm counting correctly, is
bytes := ext.gen_random_bytes(6);

If I comment out that line, it then tells me  get_byte()   is undefined, 
which should be impossible because it's built in.



This gen_random() function is in public, the pgcrypto function 
gen_random_bytes() is in a separate utility schema "ext".  This is in a 
test database which I am in process of modifying, but it works perfectly 
when dumped and restored to a different computer.  This gen_random()  
function - and its environment - has been working in multiple systems 
for quite a while.


I suspect that the Postgresql installation somehow has been hosed and 
that I'm looking at a reinstall, but I have no idea how I managed it.  
I'd like to know what happened so I can (try to) avoid it going 
forward.  There haven't been any recent system updates, and AFAIK there 
haven't been any crashes either.  Occasionally pgAdmin3 does hang up, 
but that happens very infrequently and has occurred on all the working 
systems as well.  I have been adding new tables and functions to the 
public schema on this test system, but I haven't touched anything that 
was already working.


It seems like Postgresql just snapped.  Any ideas?  Anything in 
particular I might look at for a clue?


Thanks,
George



--
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] Performance degradation in 324577f39bc8738ed0ec24c36c5cb2c2f81ec660

[Tom Lane]

Vladimir Kamarzin  writes:
> After upgrade from 9.3.1 to 9.3.5 we expirienced a slight performance 
> degradation of all queries. Query time increased to some amount of ms, mostly 
> in range of 100ms. Some actions in our application results in a lot of small 
> queries and in such cases performance degradation is very significant - total 
> action performs for a 2-3 times longer then before (15s -> 40s, etc).
> Using git-bisect we've found a bad revision causes performance drop: it is 
> 324577f39bc8738ed0ec24c36c5cb2c2f81ec660

Hm.  If you're going to do queries that involve update/delete across large
inheritance trees, that bug fix is unavoidably going to cost you some
cycles.  Having said that, though, the append_rel_list data structures
aren't especially large or complex, so it's a mite astonishing that you
could notice this extra copying cost in the context of everything else
that happens in a large inherited UPDATE.  I am wondering if you've
misidentified the commit that made the difference --- especially since you
claim there's a penalty for "all" queries, which there manifestly couldn't
be with this particular patch.  If not, there must be something rather
unusual about your queries or schema.  Care to provide a self-contained
test case?

regards, tom lane


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

[PERFORM] Performance degradation in 324577f39bc8738ed0ec24c36c5cb2c2f81ec660

[Vladimir Kamarzin]

After upgrade from 9.3.1 to 9.3.5 we expirienced a slight performance 
degradation of all queries. Query time increased to some amount of ms, mostly 
in range of 100ms. Some actions in our application results in a lot of small 
queries and in such cases performance degradation is very significant - total 
action performs for a 2-3 times longer then before (15s -> 40s, etc).

Using git-bisect we've found a bad revision causes performance drop: it is 
324577f39bc8738ed0ec24c36c5cb2c2f81ec660

All tests were performed on the same server with same postgresql.conf, the only 
load on this server is postgresql test setup.

Here is example query plan of one query: http://explain.depesz.com/s/CWA
Anecdotally, when such a query executed in psql, it shows different results 
than when executes as a part of application-induced batch of queries. For 
example, the above query takes 24ms on "fast" postgres version, and 80ms on 
"slow" postgres versions. But when executed in "standalone" mode from psql, it 
takes 9.5-13 ms independently on postgres version. So we're logged all 
statements from our test suite using auto_explain module.

Here is query time difference on different postgresql versions:
# grep "duration: " 9.3-fast.log |awk '{sum += $6}; END { print sum}'
8309.05
# grep "duration: " 9.3.5.log |awk '{sum += $6}; END { print sum}'
24142

Log from postgres from 1 revision before bad: 
http://tgt72.ru/static/tmp/9.3-fast.log
Log from 9.3.5: http://tgt72.ru/static/tmp/9.3.5.log
Database schema: http://tgt72.ru/static/tmp/gits.sql

postgresql.conf:
data_directory = '/var/lib/postgresql/9.3/main' # use data in another 
directory
hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'   # host-based 
authentication file
ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'   # ident configuration 
file
external_pid_file = '/var/run/postgresql/9.3-main.pid'  # write 
an extra PID file
listen_addresses = '*'  # what IP address(es) to listen on;
port = 5432 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of 
directories
ssl = true  # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'  # (change 
requires restart)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change 
requires restart)
vacuum_cost_delay = 50
vacuum_cost_page_hit = 0
vacuum_cost_limit = 600
fsync = on  # turns forced synchronization on or off
synchronous_commit = off# synchronization level;
log_line_prefix = '%t:%r:%u@%d:[%p]: '
log_statement = 'none'  # none, ddl, mod, all
log_timezone = 'localtime'
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.0195
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 10
autovacuum_vacuum_cost_limit = 300
vacuum_freeze_table_age = 5
datestyle = 'iso, dmy'
timezone = 'localtime'
client_encoding = utf8  # actually, defaults to database
lc_messages = 'en_US.UTF-8' # locale for system error 
message
lc_monetary = 'ru_RU.UTF-8' # locale for monetary formatting
lc_numeric = 'ru_RU.UTF-8'  # locale for number formatting
lc_time = 'ru_RU.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.russian'
max_locks_per_transaction = 128 # min 10
default_statistics_target = 50 # pgtune wizard 2013-11-20
maintenance_work_mem = 1GB # pgtune wizard 2013-11-20
constraint_exclusion = on # pgtune wizard 2013-11-20
checkpoint_completion_target = 0.9 # pgtune wizard 2013-11-20
effective_cache_size = 12GB # pgtune wizard 2013-11-20
work_mem = 96MB # pgtune wizard 2013-11-20
wal_buffers = 8MB # pgtune wizard 2013-11-20
checkpoint_segments = 24
shared_buffers = 4GB # pgtune wizard 2013-11-20
max_connections = 300 # pgtune wizard 2013-11-20
shared_preload_libraries = 'auto_explain'
auto_explain.log_analyze = 1
auto_explain.log_min_duration = 0
auto_explain.log_buffers = 1
auto_explain.log_nested_statements = 1


Here is number of tuples estimation:
SELECT relname,reltuples::numeric FROM pg_class order by reltuples DESC limit 
100;
  relname  | reltuples 
---+---
 schedule_line_audit_2013_06_time_fact_idx |  80649500
 schedule_line_audit_2013_06_checkpoint_idx|  80649500
 schedule_line_audit_2013_06_audit_timestamp_idx   |  80649500
 schedule_line_audit_2013_06_time_plan_idx |  80649500
 schedule_line_audit_2013_06_pk|  80649500
 schedule_line_audit_2013_06   |  80649500
 schedule_line_audit_2013_06_schedule_end_datetime_idx |  80649500
 schedule_line_audit_2013_06_id_idx|  80649500
 tl_