[PERFORM] Fast HashJoin only after a cluster/recreate table

2016-03-31 Thread Alexandre de Arruda Paes 
Hi,

In the query below, the planner choose an extreme slow mergejoin(380
seconds). 'Vacuum analyze' can't help.
If I CLUSTER (or recreate) table ES09T1, the planner choose a faster
hashjoin (about 10 seconds). But, obviously, I can't do that with the users
connected.
After some time after cluster(generally in the same day), the problem
returns. Autovacuum is on, but the tables are vacuumed forced after
pg_dump, 3 times in a day (00:00 - 12:00 - 23:00).

Postgresql 9.4.5
128GB RAM/10xRAID10 SAS 15k
shared_buffers = 8GB

work_mem = 256MB

maintenance_work_mem = 16GB
random_page_cost = 2.0

effective_cache_size = 120GB


db=# explain (buffers,analyze) SELECT T1.es09item, T1.es09status,
T3.es09usuari, T3.es09datreq, T2.es08desdoc AS es09desdoc, T1.es09numdoc,
T1.es09tipdoc AS es09tipdoc, T1.es09codemp, COALESCE( T4.es09quatre, 0) AS
es09quatre FROM (((ES09T1 T1 LEFT JOIN ES08T T2 ON T2.es08tipdoc =
T1.es09tipdoc) LEFT JOIN ES09T T3 ON T3.es09codemp = T1.es09codemp AND
T3.es09tipdoc = T1.es09tipdoc AND T3.es09numdoc = T1.es09numdoc) LEFT JOIN
(SELECT COUNT(*) AS es09quatre, es09codemp, es09tipdoc, es09numdoc FROM
ES09T1 GROUP BY es09codemp, es09tipdoc, es09numdoc ) T4 ON T4.es09codemp =
T1.es09codemp AND T4.es09tipdoc = T1.es09tipdoc AND T4.es09numdoc =
T1.es09numdoc) WHERE (T1.es09codemp = 1) and (T3.es09datreq >= '2016-02-02'
and T3.es09datreq <= '2016-02-02') and (T3.es09usuari like
'') and (T1.es09tipdoc like '%') ORDER BY
T1.es09codemp, T1.es09numdoc DESC, T1.es09tipdoc;

  QUERY PLAN

--
 Sort  (cost=289546.93..289546.94 rows=2 width=78) (actual
time=380405.796..380405.929 rows=2408 loops=1)
   Sort Key: t1.es09numdoc, t1.es09tipdoc
   Sort Method: quicksort  Memory: 435kB
   Buffers: shared hit=82163
   ->  Merge Left Join  (cost=47.09..289546.92 rows=2 width=78) (actual
time=1133.077..380398.160 rows=2408 loops=1)
 Merge Cond: (t1.es09tipdoc = es09t1.es09tipdoc)
 Join Filter: ((es09t1.es09codemp = t1.es09codemp) AND
(es09t1.es09numdoc = t1.es09numdoc))
 Rows Removed by Join Filter: 992875295
 Buffers: shared hit=82163
 ->  Merge Left Join  (cost=46.53..49.29 rows=2 width=70) (actual
time=12.206..18.155 rows=2408 loops=1)
   Merge Cond: (t1.es09tipdoc = t2.es08tipdoc)
   Buffers: shared hit=6821
   ->  Sort  (cost=9.19..9.19 rows=2 width=44) (actual
time=11.611..12.248 rows=2408 loops=1)
 Sort Key: t1.es09tipdoc
 Sort Method: quicksort  Memory: 285kB
 Buffers: shared hit=6814
 ->  Nested Loop  (cost=1.11..9.18 rows=2 width=44)
(actual time=0.040..10.398 rows=2408 loops=1)
   Buffers: shared hit=6814
   ->  Index Scan using ad_es09t_1 on es09t t3
 (cost=0.56..4.58 rows=1 width=42) (actual time=0.020..0.687 rows=1212
loops=1)
 Index Cond: ((es09codemp = 1) AND
(es09datreq >= '2016-02-02'::date) AND (es09datreq <= '2016-02-02'::date))
 Filter: (es09usuari ~~
''::text)
 Buffers: shared hit=108
   ->  Index Scan using es09t1_pkey on es09t1 t1
 (cost=0.56..4.59 rows=1 width=19) (actual time=0.006..0.007 rows=2
loops=1212)
 Index Cond: ((es09codemp = 1) AND
(es09tipdoc = t3.es09tipdoc) AND (es09numdoc = t3.es09numdoc))
 Filter: (es09tipdoc ~~ '%'::text)
 Buffers: shared hit=6706
   ->  Sort  (cost=37.35..38.71 rows=547 width=32) (actual
time=0.592..2.206 rows=2919 loops=1)
 Sort Key: t2.es08tipdoc
 Sort Method: quicksort  Memory: 67kB
 Buffers: shared hit=7
 ->  Seq Scan on es08t t2  (cost=0.00..12.47 rows=547
width=32) (actual time=0.003..0.126 rows=547 loops=1)
   Buffers: shared hit=7
 ->  Materialize  (cost=0.56..287644.85 rows=716126 width=23)
(actual time=0.027..68577.800 rows=993087854 loops=1)
   Buffers: shared hit=75342
   ->  GroupAggregate  (cost=0.56..278693.28 rows=716126
width=15) (actual time=0.025..4242.453 rows=3607573 loops=1)
 Group Key: es09t1.es09codemp, es09t1.es09tipdoc,
es09t1.es09numdoc
 Buffers: shared hit=75342
 ->  Index Only Scan using es09t1_pkey on es09t1
 (cost=0.56..199919.49 rows=7161253 width=15) (actual time=0.016..1625.031
rows=7160921 loops=1)
   Index Cond: (es09codemp = 1)
   Heap Fetches: 51499
   Buffers: shared hit=75342
 Planning time: 50.129 ms
 Execution time:

Re: [PERFORM] Big number of connections

2016-03-31 Thread Karl Denninger 
On 3/31/2016 17:47, Alvaro Herrera wrote:
> Andrew Dunstan wrote:
>
>> On 03/31/2016 03:12 PM, Igor Neyman wrote:
We are going to build system based on PostgreSQL database for huge 
 number of individual users (few thousands). Each user will have his own 
 account, for authorization we will use Kerberos (MIT or Windows).
 Most of users will have low activity, but for various reasons, connection 
 should be open all the time.
 I'd like to know what potential problems and limitations we can expect 
 with such deployment.
During preliminary testing we have found that for each connection we 
 need ~1MB RAM. Is there any way to decrease this ? Is there any risk, that 
 such number of users will degrade performance ?
I'll be happy to hear any remarks and suggestions related to design, 
 administration and handling of such installation.
>>> Take a look at PgBouncer.
>>> It should solve your problems.
>> If they are going to keep the client connections open, they would need to
>> run pgbouncer in statement or transaction mode.
> As I understand, in pgbouncer you cannot have connections that serve
> different users.  If each individual requires its own database-level
> user, pgbouncer would not help at all.
>
> I would look seriously into getting rid of the always-open requirement
> for connections.
I'm trying to figure out where the "always open" requirement comes from;
there are very, very few instances where that's real, when you get down
to it.

-- 
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [PERFORM] Big number of connections

2016-03-31 Thread Chris Cogdon 
Although somewhat academic, since pgboucer doesn’t support it (and might not 
ever), have a look at this ticket which, if it was ever supported, would give 
you what you needed:

https://github.com/pgbouncer/pgbouncer/issues/75 



> On Mar 31, 2016, at 15:47, Alvaro Herrera  wrote:
> 
>> If they are going to keep the client connections open, they would need to
>> run pgbouncer in statement or transaction mode.
> 
> As I understand, in pgbouncer you cannot have connections that serve
> different users.  If each individual requires its own database-level
> user, pgbouncer would not help at all.
> 
> I would look seriously into getting rid of the always-open requirement
> for connections.

— Chris Cogdon

Re: [PERFORM] Big number of connections

2016-03-31 Thread Alvaro Herrera 
Andrew Dunstan wrote:

> On 03/31/2016 03:12 PM, Igor Neyman wrote:

> > >   We are going to build system based on PostgreSQL database for huge 
> > > number of individual users (few thousands). Each user will have his own 
> > > account, for authorization we will use Kerberos (MIT or Windows).
> > >Most of users will have low activity, but for various reasons, connection 
> > >should be open all the time.
> > >I'd like to know what potential problems and limitations we can expect 
> > >with such deployment.
> > >   During preliminary testing we have found that for each connection we 
> > > need ~1MB RAM. Is there any way to decrease this ? Is there any risk, 
> > > that such number of users will degrade performance ?
> > >   I'll be happy to hear any remarks and suggestions related to design, 
> > > administration and handling of such installation.

> >Take a look at PgBouncer.
> >It should solve your problems.
> 
> If they are going to keep the client connections open, they would need to
> run pgbouncer in statement or transaction mode.

As I understand, in pgbouncer you cannot have connections that serve
different users.  If each individual requires its own database-level
user, pgbouncer would not help at all.

I would look seriously into getting rid of the always-open requirement
for connections.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Big number of connections

2016-03-31 Thread Igor Neyman 

-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Jarek
Sent: Thursday, March 31, 2016 3:08 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Big number of connections

Hello!

We are going to build system based on PostgreSQL database for huge 
number of individual users (few thousands). Each user will have his own 
account, for authorization we will use Kerberos (MIT or Windows). 
Most of users will have low activity, but for various reasons, connection 
should be open all the time.
I'd like to know what potential problems and limitations we can expect with 
such deployment.
During preliminary testing we have found that for each connection we 
need ~1MB RAM. Is there any way to decrease this ? Is there any risk, that such 
number of users will degrade performance ?
I'll be happy to hear any remarks and suggestions related to design, 
administration and handling of such installation.

best regards
Jarek

___

Take a look at PgBouncer.
It should solve your problems.

Regards,
Igor Neyman

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

[PERFORM] Big number of connections

2016-03-31 Thread Jarek 
Hello!

We are going to build system based on PostgreSQL database for huge
number of individual users (few thousands). Each user will have his own
account, for authorization we will use Kerberos (MIT or Windows). 
Most of users will have low activity, but for various reasons,
connection should be open all the time.
I'd like to know what potential problems and limitations we can expect
with such deployment.
During preliminary testing we have found that for each connection we
need ~1MB RAM. Is there any way to decrease this ? Is there any risk,
that such number of users will degrade performance ?
I'll be happy to hear any remarks and suggestions related to design,
administration and handling of such installation.

best regards
Jarek



-- 
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] Adding a ROLLUP switches to GroupAggregate unexpectedly

2016-03-31 Thread k...@rice.edu 
On Thu, Mar 31, 2016 at 02:56:48PM -0400, Tom Lane wrote:
> Chris Cogdon  writes:
> > Hi folks! I’ve a query where adding a rollup to the group by switches to
> > GroupAggregate unexpectedly, where the standard GROUP BY uses
> > HashAggregate.
> 
> The current implementation of rollup doesn't support using hashed
> aggregation.  I don't know if that's for lack of round tuits or because
> it's actually hard, but it's not the planner's fault.
> 
>   regards, tom lane
> 

Hi,

Cribbed from the mailing list:

http://www.postgresql.org/message-id/e1ytrd5-0005q7...@gemulon.postgresql.org

The current implementation of grouping sets only supports using sorting
for input. Individual sets that share a sort order are computed in one
pass. If there are sets that don't share a sort order, additional sort &
aggregation steps are performed. These additional passes are sourced by
the previous sort step; thus avoiding repeated scans of the source data.

The code is structured in a way that adding support for purely using
hash aggregation or a mix of hashing and sorting is possible. Sorting
was chosen to be supported first, as it is the most generic method of
implementation.

Regards,
Ken


-- 
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] Adding a ROLLUP switches to GroupAggregate unexpectedly

2016-03-31 Thread Tom Lane 
Chris Cogdon  writes:
> Hi folks! I’ve a query where adding a rollup to the group by switches to
> GroupAggregate unexpectedly, where the standard GROUP BY uses
> HashAggregate.

The current implementation of rollup doesn't support using hashed
aggregation.  I don't know if that's for lack of round tuits or because
it's actually hard, but it's not the planner's fault.

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] Adding a ROLLUP switches to GroupAggregate unexpectedly

2016-03-31 Thread Chris Cogdon 
Hi folks! I’ve a query where adding a rollup to the group by switches to 
GroupAggregate unexpectedly, where the standard GROUP BY uses HashAggregate. 
Since the rollup should only add one additional bucket, the switch to having to 
sort (and thus a to-disk temporary file) is very puzzling. This reads like a 
query optimiser bug to me. This is the first I’ve posted to the list, please 
forgive me if I’ve omitted any “before bugging the list” homework.


Description: Adding a summary row by changing “GROUP BY x” into “GROUP BY 
ROLLUP (x)” should not cause a switch from HashAggregate to GroupAggregate


Here’s the “explain” from the simple GROUP BY:

projectdb=> explain analyze verbose SELECT error_code, count ( * ) FROM 
api_activities GROUP BY error_code;
 QUERY PLAN 
 
-
 HashAggregate  (cost=3456930.11..3456930.16 rows=5 width=2) (actual 
time=26016.222..26016.223 rows=5 loops=1)
   Output: error_code, count(*)
   Group Key: api_activities.error_code
   ->  Seq Scan on public.api_activities  (cost=0.00..3317425.74 rows=27900874 
width=2) (actual time=0.018..16232.608 rows=36224844 loops=1)
 Output: id, client_id, date_added, kind, activity, error_code
 Planning time: 0.098 ms
 Execution time: 26016.337 ms
(7 rows)

Changing this to a GROUP BY ROLLUP switches to GroupAggregate (with the 
corresponding to-disk temporary table being created):

projectdb=> explain analyze verbose SELECT error_code, count ( * ) FROM 
api_activities GROUP BY rollup (error_code);
QUERY PLAN  
   
---
 GroupAggregate  (cost=7149357.90..7358614.52 rows=6 width=2) (actual 
time=54271.725..82354.144 rows=6 loops=1)
   Output: error_code, count(*)
   Group Key: api_activities.error_code
   Group Key: ()
   ->  Sort  (cost=7149357.90..7219110.09 rows=27900874 width=2) (actual 
time=54270.636..76651.121 rows=36222428 loops=1)
 Output: error_code
 Sort Key: api_activities.error_code
 Sort Method: external merge  Disk: 424864kB
 ->  Seq Scan on public.api_activities  (cost=0.00..3317425.74 
rows=27900874 width=2) (actual time=0.053..34282.239 rows=36222428 loops=1)
   Output: error_code
 Planning time: 2.611 ms
 Execution time: 82437.416 ms
(12 rows)


I’ve given the output of “EXPLAIN ANAYLZE VERBOSE” rather than non-analyze, but 
there was no difference in the plan.

Running VACUUM FULL ANALYZE on this table makes no difference. Switching to 
Count(error_code) makes no difference. Using GROUP BY GROUPING SETS ((), 
error_code) makes no difference.

I understand that a HashAggregate is possible only if it can fit all the 
aggregates into work_mem. There are 5 different error codes, and the statistics 
(from pg_stats) are showing that PG knows this. Adding just one more bucket for 
the “()” case should not cause a fallback to GroupAggregate.


PostgreSQL version: 9.5.2 (just upgraded today, Thank you! <3 )

(Was exhibiting same problem under 9.5.0)


How installed: apt-get package from apt.postgresql.org 



Settings differences:

 application_name: psql
 client_encoding: UTF8
 DateStyle: ISO, MDY
 default_text_search_config: pg_catalog.english
 dynamic_shared_memory_type: posix
 lc_messages: en_US.UTF-8
 lc_monetary: en_US.UTF-8
 lc_numeric: en_US.UTF-8
 lc_time: en_US.UTF-8
 listen_addresses: *
 log_line_prefix: %t [%p-%c-%l][%a][%i][%e][%s][%x-%v] %q%u@%d 
 log_timezone: UTC
 logging_collector: on
 max_connections: 100
 max_stack_depth: 2MB
 port: 5432
 shared_buffers: 1GB
 ssl: on
 ssl_cert_file: /etc/ssl/certs/ssl-cert-snakeoil.pem
 ssl_key_file: /etc/ssl/private/ssl-cert-snakeoil.key
 TimeZone: UTC
 work_mem: 128MB


OS and Version: Ubuntu Trusty: Linux 3.13.0-66-generic #108-Ubuntu SMP Wed Oct 
7 15:20:27 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux


Program used to connect: psql


Nothing unusual in the logs, apart from the query indicating that it took a 
while to run.


I know that there’s several workarounds I can use for this simple case, such as 
using a CTE, then doing a rollup on that, but I’m simply reporting what I think 
is a bug in the query optimizer.


Thank you for your attention! Please let me know if there’s any additional 
information you need, or additional tests you’d like to run.


— Chris Cogdon >
— Using PostgreSQL since 6.2!