Re: [PERFORM] PostgreSQL 9.6 wals management

[Mariel Cherkassky]

Thank you for the clarification.

בתאריך 20 בנוב׳ 2017 14:28,‏ "Michael Paquier" 
כתב:

> On Mon, Nov 20, 2017 at 6:02 PM, Mariel Cherkassky
>  wrote:
> > This morning , I set the wal_keep_segments to 100 and I set the
> > archive_timeout to 6 minutes. Now, after setting those settings and
> starting
> > the cluster wals switch is working fine and I didnt see that many wals
> were
> > However, doesnt the old wals should be deleted automaticly ? Can I
> > delete archives safely ?
>
> Archives are useful if they can be used with a base backup which would
> allow it to recover close to the point has created WAL activity, up to
> the last finished segment to be precise. So if you have no base
> backups or standbys (for example disconnected for a long) that would
> use them, there is no point in keeping them. What defines the archive
> and base backup retention is your data retention policy. Do not touch
> the files of pg_xlog though, those are managed by PostgreSQL itself.
> It is also good practice to put the archives on a different partition,
> and to not have the archives in a sub-path of the main data folder as
> you do as those would get included in all base backups taken.
> --
> Michael
>

Re: [PERFORM] PostgreSQL 9.6 wals management

[Michael Paquier]

On Mon, Nov 20, 2017 at 6:02 PM, Mariel Cherkassky
 wrote:
> This morning , I set the wal_keep_segments to 100 and I set the
> archive_timeout to 6 minutes. Now, after setting those settings and starting
> the cluster wals switch is working fine and I didnt see that many wals were
> However, doesnt the old wals should be deleted automaticly ? Can I
> delete archives safely ?

Archives are useful if they can be used with a base backup which would
allow it to recover close to the point has created WAL activity, up to
the last finished segment to be precise. So if you have no base
backups or standbys (for example disconnected for a long) that would
use them, there is no point in keeping them. What defines the archive
and base backup retention is your data retention policy. Do not touch
the files of pg_xlog though, those are managed by PostgreSQL itself.
It is also good practice to put the archives on a different partition,
and to not have the archives in a sub-path of the main data folder as
you do as those would get included in all base backups taken.
-- 
Michael


-- 
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] CREATE STATISTICS and join selectivity

[David Rowley]

On 16 November 2017 at 09:19, Justin Pryzby  wrote:
> I just noticed that PG10 CREATE STATISTICS (dependencies) doesn't seem to work
> for joins on multiple columns; is that right?

Unfortunately, for now, they're not used for join selectivity
estimates, only for the base rel selectivities. That's all there was
time for with PG10. This is highly likely to be improved sometime in
the future.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] query performance issue

[Pavel Stehule]

2017-11-15 20:58 GMT+01:00 Gunther :

>
> On 11/15/2017 8:12, Pavel Stehule wrote:
>
> There is wrong plan due wrong estimation
>
> for this query you should to penalize nested loop
>
> set enable_nestloop to off;
>
> before evaluation of this query
>
>
> You are not the only one with this issue. May I suggest to look at this
> thread a little earlier this month.
>
> http://www.postgresql-archive.org/OLAP-reporting-queries-
> fall-into-nested-loops-over-seq-scans-or-other-horrible-
> planner-choices-tp5990160.html
>
> where this has been discussed in some length.
>

It is typical issue. The source of these problems are correlations between
columns (it can be fixed partially by multicolumn statistics in PostgreSQL
10). Another problem is missing multi table statistics - PostgreSQL planner
expects so any value from dictionary has same probability, what is not
usually true. Some OLAP techniques like calendar tables has usually very
bad impact on estimations with this results.

Regards

Pavel


> regards,
> -Gunther
>
>
>

Re: [PERFORM] query performance issue

[Gunther]

On 11/15/2017 8:12, Pavel Stehule wrote:

There is wrong plan due wrong estimation

for this query you should to penalize nested loop

set enable_nestloop to off;

before evaluation of this query


You are not the only one with this issue. May I suggest to look at this 
thread a little earlier this month.


http://www.postgresql-archive.org/OLAP-reporting-queries-fall-into-nested-loops-over-seq-scans-or-other-horrible-planner-choices-tp5990160.html

where this has been discussed in some length.

regards,
-Gunther

Re: [PERFORM] query performance issue

[Justin Pryzby]

On Wed, Nov 15, 2017 at 03:03:39PM +0530, Samir Magar wrote:
> I am having performance issues with one of the query.
> The query is taking 39 min to fetch 3.5 mil records.
> 
> I want to reduce that time to 15 mins.
> could you please suggest something to its performance?

> "HashAggregate  (cost=4459.68..4459.69 rows=1 width=27) (actual 
> time=2890035.403..2892173.601 rows=3489861 loops=1)"

Looks to me like the problem is here:

> "  ->  Index Only Scan using idxdq7 on dlr_qlfy  (cost=0.43..4.45 ROWS=1 
> width=16) (actual time=0.009..0.066 ROWS=121 loops=103987)"
> "Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 
> 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))"
> "Heap Fetches: 0"

Returning 100x more rows than expected and bubbling up through a cascade of
nested loops.

Are those 3 conditions independent ?  Or, perhaps, are rows for which
"qlfy_grp_id=dlr_grp.dlr_grp_id" is true always going to have
"cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id" ?

Even if it's not "always" true, if rows which pass the one condition are more
likely to pass the other condition, this will cause an underestimate, as
obvserved.

You can do an experiment SELECTing just from those two tables joined and see if
you can reproduce the problem with poor rowcount estimate (hopefully in much
less than 15min).

If you can't drop one of the two conditions, you can make PG treat it as a
single condition for purpose of determining expected selectivity, using a ROW()
comparison like:

ROW(qlfy_grp_id, cog_grp_id) = ROW(dlr_grp.dlr_grp_id, 
dlr_grp_dlr_xref_1.dlr_grp_id)

If you're running PG96+ you may also be able to work around this by adding FKs.

Justin


-- 
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 performance issue

[Pavel Stehule]

2017-11-15 13:54 GMT+01:00 Samir Magar :

> please find the EXPLAIN ANALYZE output.
>
> On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>> please send EXPLAIN ANALYZE output.
>>
>> Regards
>>
>> Pavel
>>
>> 2017-11-15 10:33 GMT+01:00 Samir Magar :
>>
>>> Hello,
>>> I am having performance issues with one of the query.
>>> The query is taking 39 min to fetch 3.5 mil records.
>>>
>>> I want to reduce that time to 15 mins.
>>> could you please suggest something to its performance?
>>>
>>> server configuration:
>>>  CPUs = 4
>>> memory = 16 GM
>>> shared_buffers = 3 GB
>>> work_mem = 100MB
>>> effective_cache_size = 12 GB
>>>
>>> we are doing the vacuum/analyze regularly on the database.
>>>
>>> attached is the query with its explain plan.
>>>
>>>

There is wrong plan due wrong estimation

for this query you should to penalize nested loop

set enable_nestloop to off;

before evaluation of this query


Thanks,
>>> Samir Magar
>>>
>>>
>>> --
>>> 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 performance issue

[Samir Magar]

please find the EXPLAIN ANALYZE output.

On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule 
wrote:

> Hi
>
> please send EXPLAIN ANALYZE output.
>
> Regards
>
> Pavel
>
> 2017-11-15 10:33 GMT+01:00 Samir Magar :
>
>> Hello,
>> I am having performance issues with one of the query.
>> The query is taking 39 min to fetch 3.5 mil records.
>>
>> I want to reduce that time to 15 mins.
>> could you please suggest something to its performance?
>>
>> server configuration:
>>  CPUs = 4
>> memory = 16 GM
>> shared_buffers = 3 GB
>> work_mem = 100MB
>> effective_cache_size = 12 GB
>>
>> we are doing the vacuum/analyze regularly on the database.
>>
>> attached is the query with its explain plan.
>>
>> Thanks,
>> Samir Magar
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>
"HashAggregate  (cost=4459.68..4459.69 rows=1 width=27) (actual 
time=2890035.403..2892173.601 rows=3489861 loops=1)"
"  Group Key: dlr_qlfy.dlr_qlfy_id, nmq_req.grace_prd, nmq_req.hide_prg_flg, 
nmq_req.ntfy_dlr_flg, dlr_loc.acct_num, nmq_req.nmq_req_id, new_mdl.pi_mdl_id"
"  ->  Nested Loop  (cost=3.59..4459.67 rows=1 width=27) (actual 
time=0.228..2864594.177 rows=12321289 loops=1)"
"->  Nested Loop  (cost=3.31..4459.29 rows=1 width=27) (actual 
time=0.221..2819927.249 rows=12321289 loops=1)"
"  ->  Nested Loop  (cost=3.03..4451.45 rows=1 width=15) (actual 
time=0.158..36816.304 rows=12612983 loops=1)"
"Join Filter: (lead_loc.dlr_loc_id = 
dlr_grp_1.lead_dlr_loc_id)"
"->  Nested Loop  (cost=0.58..1358.94 rows=263 width=15) 
(actual time=0.046..363.150 rows=52261 loops=1)"
"  ->  Nested Loop  (cost=0.29..1227.46 rows=169 
width=15) (actual time=0.024..86.909 rows=12151 loops=1)"
"->  Seq Scan on dlr_loc lead_loc  
(cost=0.00..757.80 rows=169 width=4) (actual time=0.010..31.028 rows=12151 
loops=1)"
"  Filter: (acct_num = cog_parnt_acct)"
"  Rows Removed by Filter: 21593"
"->  Index Only Scan using "IDX_101" on dlr_loc 
cog_lead  (cost=0.29..2.77 rows=1 width=11) (actual time=0.003..0.004 rows=1 
loops=12151)"
"  Index Cond: (dlr_loc_id = 
lead_loc.dlr_loc_id)"
"  Heap Fetches: 0"
"  ->  Index Scan using idx_14 on stg_acflx_nmq_dlrs  
(cost=0.29..0.63 rows=15 width=14) (actual time=0.008..0.019 rows=4 
loops=12151)"
"Index Cond: (rltnp_lead_acct = 
cog_lead.acct_num)"
"->  Nested Loop  (cost=2.45..11.75 rows=1 width=33) 
(actual time=0.058..0.615 rows=241 loops=52261)"
"  ->  Index Only Scan using idx3 on dlr_grp dlr_grp_1  
(cost=0.29..0.32 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=52261)"
"Index Cond: ((lead_dlr_loc_id = 
cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
"Heap Fetches: 0"
"  ->  Nested Loop  (cost=2.17..11.42 rows=1 width=37) 
(actual time=0.051..0.530 rows=236 loops=53436)"
"Join Filter: (dlr_loc_2.acct_num = 
dlr_loc.acct_num)"
"->  Nested Loop  (cost=0.58..0.77 rows=1 
width=11) (actual time=0.015..0.016 rows=1 loops=53436)"
"  ->  Index Only Scan using idx6 on 
dlr_loc dlr_loc_2  (cost=0.29..0.32 rows=1 width=11) (actual time=0.009..0.009 
rows=1 loops=53436)"
"Index Cond: ((acct_num = 
stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
"Heap Fetches: 0"
"  ->  Index Only Scan using idx7 on 
dlr_grp_dlr_xref dlr_grp_dlr_xref_1  (cost=0.29..0.43 rows=1 width=8) (actual 
time=0.004..0.005 rows=1 loops=53402)"
"Index Cond: ((dlr_loc_id = 
dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
"Heap Fetches: 0"
"->  Nested Loop  (cost=1.58..10.64 rows=1 
width=26) (actual time=0.036..0.425 rows=243 loops=51988)"
"  ->  Index Only Scan using idx10 on 
dlr_loc  (cost=0.29..0.32 rows=1 width=7) (actual time=0.009..0.009 rows=1 
loops=51988)"
"Index Cond: ((is_actv = 
'Y'::bpchar) AND (acct_num = stg_acflx_nmq_dlrs.acct_id))"
"Heap Fetches: 0"
"  ->  Nested Loop  (cost=1.29..10.30 
rows=1 width=19) (actual time=0.026..0.354 rows=243 

Re: [PERFORM] query performance issue

[Pavel Stehule]

Hi

please send EXPLAIN ANALYZE output.

Regards

Pavel

2017-11-15 10:33 GMT+01:00 Samir Magar :

> Hello,
> I am having performance issues with one of the query.
> The query is taking 39 min to fetch 3.5 mil records.
>
> I want to reduce that time to 15 mins.
> could you please suggest something to its performance?
>
> server configuration:
>  CPUs = 4
> memory = 16 GM
> shared_buffers = 3 GB
> work_mem = 100MB
> effective_cache_size = 12 GB
>
> we are doing the vacuum/analyze regularly on the database.
>
> attached is the query with its explain plan.
>
> Thanks,
> Samir Magar
>
>
> --
> 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 planner gaining the ability to replanning after start of query execution.

[Arne Roland]

Hello,

that method is bound to introduce errors if the physical location of a row 
correlates strongly with a column, imagine "and my_timestamp> now() - INTERVAL 
'1 year'" as part of a where clause of a query without limit on an insert only 
table which is one and a half years old with a seqscan. There might be similar 
effects if an index on the timestamp is used to go about a query, if other rows 
of the filter correlate.

This method furthermore only judges filter predicates.
So it's not that easy to just go about the expected rowcount of a single node, 
since the underling node might return a totally inaccurate number of rows. 
While that isn't that common with underlying seqscans, it is very frequent if 
an indexscan is used without being able to rely on the MCV. While it's still 
possible to notice a misestimation there is no sense of how wrong it is, until 
the rows are already processed.

Furthermore: Did you think about parallel plans and most importantly cursors?

Best regards
Arne Roland

-Original Message-
From: Oliver Mattos [mailto:omat...@gmail.com] 
Sent: Monday, November 13, 2017 10:52 PM
To: Arne Roland <a.rol...@index.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query planner gaining the ability to replanning after 
start of query execution.

> Can you be more elaborate how you'd want to go about it?

My initial approach would be to try to identify places in the plan
where selectivity is seriously over or underestimated.   I would reuse
the instrumentation infrastructure's counts of filtered and returned tuples for 
each execnode, and periodically call back into the planner (for example at 
every power of 2 tuples processed).

The planner would have a wrapper to clauselist_selectivity which somehow 
combines the existing estimate with the filtered and returned
tuples so far.   Exactly how to merge them isn't clear, but I could
imagine using a poisson distribution to calculate the probability that the 
selectivity estimate is representative of the filtered and returned numbers, 
and then blending the two linearly based on that estimate.

When the planner has re-estimated the cost of the current plan, a discount 
would be applied for the percentage of each execnode completed (rows processed 
/ estimated rows), and all candidate plans compared.

If another candidate plan is now lower cost, the current plan would be 
terminated[1] by setting a flag instructing each execnode to return as if it 
had reached the end of the input, although still caching the node selectivity 
values, and the new plan started from scratch.

The old plan is kept within the query planner candidate list, together with 
it's cached selectivity values.  If at some point it again is cheaper, it is 
started from scratch too.


> Even if we know the cardinality is overestimated, we have no idea 
> whether the cardinality of a = 3 or b = 40 is wrong or they just 
> correlate

The goal would be not to know which is wrong, but to try each, discarding it if 
it turns out worse than we estimated.  Processing a few hundred rows of each of 
5 plans is tiny compared to a scan of 1M rows...


[1]:   An improvement here (with much more code complexity) is to keep
multiple partially executed plans around, so that whichever one is most 
promising can be worked on, but can be halted and resumed later as selectivity 
(and hence cost) estimates change.

On Mon, Nov 13, 2017 at 8:06 PM, Arne Roland <a.rol...@index.de> wrote:
> Hello,
>
> I'd love to have some sort of dynamic query feedback, yet it's very 
> complicated to do it right. I am not convinced that changing the plan during 
> a single execution is the right way to do it, not only because it sounds 
> intrusive to do crazy things in the executor, but also because don't 
> understand why the new plan should be any better than the old one. Can you be 
> more elaborate how you'd want to go about it?
>
> In your example (which presumably just has a single relation), we have 
> no notion of whether the scan returns no rows because we were unlucky, 
> because just the first few pages were empty of matching rows (which in my 
> experience happens more often), or because the cardinality estimation is 
> wrong. Even if the cardinality estimation is wrong, we have no notion of 
> which predicate or predicate combination actually caused the misestimation. 
> If the first few pages where empty, the same might happen with every order 
> (so also with every available indexscan). Imagine a very simple seqscan plan 
> of select * from mytab where a = 3 and b = 40 limit 1 Even if we know the 
> cardinality is overestimated, we have no idea whether the cardinality of a = 
> 3 or b = 40 is wrong or they just correlate, so there is no notion of which 
> is actually the cheapest plan. Usual workaround for most of these queries is 
> to add an order by (which has the 

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

[Arne Roland]

Hello,

I'd love to have some sort of dynamic query feedback, yet it's very complicated 
to do it right. I am not convinced that changing the plan during a single 
execution is the right way to do it, not only because it sounds intrusive to do 
crazy things in the executor, but also because don't understand why the new 
plan should be any better than the old one. Can you be more elaborate how you'd 
want to go about it?

In your example (which presumably just has a single relation), we have no 
notion of whether the scan returns no rows because we were unlucky, because 
just the first few pages were empty of matching rows (which in my experience 
happens more often), or because the cardinality estimation is wrong. Even if 
the cardinality estimation is wrong, we have no notion of which predicate or 
predicate combination actually caused the misestimation. If the first few pages 
where empty, the same might happen with every order (so also with every 
available indexscan). Imagine a very simple seqscan plan of 
select * from mytab where a = 3 and b = 40 limit 1
Even if we know the cardinality is overestimated, we have no idea whether the 
cardinality of a = 3 or b = 40 is wrong or they just correlate, so there is no 
notion of which is actually the cheapest plan. Usual workaround for most of 
these queries is to add an order by (which has the nice addition of having a 
deterministic result) with an appropriate complex index, usually resulting in 
indexscans.

While we actually know more after the first execution of a nodes like 
materialize, sort or hash nodes, I rarely encounter materialize nodes in the 
wild. Consequently that is the place where the work is usually already done, 
which is especially true with the hash node. Even though it still might be more 
optimal to switch from a mergejoin to a hashjoin in some cases, I doubt that's 
worth any work (and even less the maintenance).

Best regards
Arne Roland

-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Oliver Mattos
Sent: Monday, November 13, 2017 5:45 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Query planner gaining the ability to replanning after start 
of query execution.

I am interested in giving the query planner the ability to replan (or re-rank 
plans) after query execution has begun, based on the progression of the query 
so far.

Example use case:

*  A LIMIT 1 query is planned using an expensive scan which the planner expects 
to return a large number of results, and to terminate
early.   The reality is the query actually produces no results, and
the scan must run to completion, potentially taking thousands of times longer 
than expected.

*  If this plans costs were adjusted mid-execution to reflect the fact that the 
scan is producing far fewer rows than expected, then another query plan might 
come out ahead, which would complete far faster.


Has this been done before?   Are there any pitfalls to beware of?


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




-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Oliver Mattos
Sent: Monday, November 13, 2017 5:45 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Query planner gaining the ability to replanning after start 
of query execution.

I am interested in giving the query planner the ability to replan (or re-rank 
plans) after query execution has begun, based on the progression of the query 
so far.

Example use case:

*  A LIMIT 1 query is planned using an expensive scan which the planner expects 
to return a large number of results, and to terminate
early.   The reality is the query actually produces no results, and
the scan must run to completion, potentially taking thousands of times longer 
than expected.

*  If this plans costs were adjusted mid-execution to reflect the fact that the 
scan is producing far fewer rows than expected, then another query plan might 
come out ahead, which would complete far faster.


Has this been done before?   Are there any pitfalls to beware of?


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




-- 
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 planner gaining the ability to replanning after start of query execution.

[Oliver Mattos]

>  You can't just restart from scratch, because we may already have shipped 
> rows to the client

For v1, replanning wouldn't be an option if rows have already been
shipped, or for DML statements.

> parallel plans and most importantly cursors?
Parallel plans look do-able with the same approach, but cursor use I'd
probably stop replanning as soon as the first row is delivered to the
client, as above.   One could imagine more complex approaches like a
limited size buffer of 'delivered' rows, allowing a new plan to be
selected and the delivered rows excluded from the new plans resultset
via a special extra prepending+dupe filtering execnode.   The memory
and computation costs of that execnode would be factored into the
replanning decision like any other node.


>errors if the physical location of a row correlates strongly with a column
This is my largest concern.  These cases already lead to large errors
currently (SELECT * FROM foo WHERE created_date = today LIMIT 1) might
scan all data, only to find all of today's records in the last
physical block.

It's hard to say if replacing one bad estimate with another will lead
to overall better/worse results...   My hope is that in most cases a
bunch of plans will be tried, all end up with cost estimates revised
up a lot, and then one settled on as rows start getting passed to
upper layers.

>underling node might return a totally inaccurate number of rows for index scans
One might imagine using the last returned row as an extra histogram
point when estimating how many rows are left in an index scan.   That
should at least make the estimate more accurate than it is without
feedback.


-- 
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 planner gaining the ability to replanning after start of query execution.

[Tom Lane]

Oliver Mattos  writes:
>> Can you be more elaborate how you'd want to go about it?

> ... If another candidate plan is now lower cost, the current plan would be
> terminated[1] by setting a flag instructing each execnode to return as
> if it had reached the end of the input, although still caching the
> node selectivity values, and the new plan started from scratch.

Quite aside from the implementation difficulties you'll have, that
approach is a show-stopper right there.  You can't just restart from
scratch, because we may already have shipped rows to the client, or
for DML cases already inserted/updated/deleted rows (and even if you
could roll those back, we've possibly fired triggers with unpredictable
side effects).  Queries containing volatile functions are another no-fly
zone for this approach.

I can't see any way around that without unacceptable performance costs
(i.e. buffering all the rows until we're done) or wire-protocol breakage.

I think that a more practical way to address the class of problems
you're talking about is to teach the planner to have some notion of
worst-case as well as expected-case costs, and then incorporate some
perhaps-configurable amount of risk aversion in its choices.

regards, tom lane

PS: please do not top-post, and do not quote the entire darn thread
in each message.


-- 
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 planner gaining the ability to replanning after start of query execution.

[Oliver Mattos]

> Can you be more elaborate how you'd want to go about it?

My initial approach would be to try to identify places in the plan
where selectivity is seriously over or underestimated.   I would reuse
the instrumentation infrastructure's counts of filtered and returned
tuples for each execnode, and periodically call back into the planner
(for example at every power of 2 tuples processed).

The planner would have a wrapper to clauselist_selectivity which
somehow combines the existing estimate with the filtered and returned
tuples so far.   Exactly how to merge them isn't clear, but I could
imagine using a poisson distribution to calculate the probability that
the selectivity estimate is representative of the filtered and
returned numbers, and then blending the two linearly based on that
estimate.

When the planner has re-estimated the cost of the current plan, a
discount would be applied for the percentage of each execnode
completed (rows processed / estimated rows), and all candidate plans
compared.

If another candidate plan is now lower cost, the current plan would be
terminated[1] by setting a flag instructing each execnode to return as
if it had reached the end of the input, although still caching the
node selectivity values, and the new plan started from scratch.

The old plan is kept within the query planner candidate list, together
with it's cached selectivity values.  If at some point it again is
cheaper, it is started from scratch too.


> Even if we know the cardinality is overestimated, we have no idea whether the 
> cardinality of a = 3 or b = 40 is wrong or they just correlate

The goal would be not to know which is wrong, but to try each,
discarding it if it turns out worse than we estimated.  Processing a
few hundred rows of each of 5 plans is tiny compared to a scan of 1M
rows...


[1]:   An improvement here (with much more code complexity) is to keep
multiple partially executed plans around, so that whichever one is
most promising can be worked on, but can be halted and resumed later
as selectivity (and hence cost) estimates change.

On Mon, Nov 13, 2017 at 8:06 PM, Arne Roland  wrote:
> Hello,
>
> I'd love to have some sort of dynamic query feedback, yet it's very 
> complicated to do it right. I am not convinced that changing the plan during 
> a single execution is the right way to do it, not only because it sounds 
> intrusive to do crazy things in the executor, but also because don't 
> understand why the new plan should be any better than the old one. Can you be 
> more elaborate how you'd want to go about it?
>
> In your example (which presumably just has a single relation), we have no 
> notion of whether the scan returns no rows because we were unlucky, because 
> just the first few pages were empty of matching rows (which in my experience 
> happens more often), or because the cardinality estimation is wrong. Even if 
> the cardinality estimation is wrong, we have no notion of which predicate or 
> predicate combination actually caused the misestimation. If the first few 
> pages where empty, the same might happen with every order (so also with every 
> available indexscan). Imagine a very simple seqscan plan of
> select * from mytab where a = 3 and b = 40 limit 1
> Even if we know the cardinality is overestimated, we have no idea whether the 
> cardinality of a = 3 or b = 40 is wrong or they just correlate, so there is 
> no notion of which is actually the cheapest plan. Usual workaround for most 
> of these queries is to add an order by (which has the nice addition of having 
> a deterministic result) with an appropriate complex index, usually resulting 
> in indexscans.
>
> While we actually know more after the first execution of a nodes like 
> materialize, sort or hash nodes, I rarely encounter materialize nodes in the 
> wild. Consequently that is the place where the work is usually already done, 
> which is especially true with the hash node. Even though it still might be 
> more optimal to switch from a mergejoin to a hashjoin in some cases, I doubt 
> that's worth any work (and even less the maintenance).
>
> Best regards
> Arne Roland
>
> -Original Message-
> From: pgsql-performance-ow...@postgresql.org 
> [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Oliver Mattos
> Sent: Monday, November 13, 2017 5:45 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Query planner gaining the ability to replanning after 
> start of query execution.
>
> I am interested in giving the query planner the ability to replan (or re-rank 
> plans) after query execution has begun, based on the progression of the query 
> so far.
>
> Example use case:
>
> *  A LIMIT 1 query is planned using an expensive scan which the planner 
> expects to return a large number of results, and to terminate
> early.   The reality is the query actually produces no results, and
> the scan must run to completion, potentially taking thousands of times longer 
> 

Re: [PERFORM] DB slowness after upgrade from Postgres 9.1 to 9.4

[Tom Lane]

p kirti  writes:
> We have recently upgraded our project with a huge DB from Postgres v9.1 to
> v9.4. The whole system performance has degraded alarmingly after the
> upgrade. Simple operations that were taking only a few seconds in Postgres
> 9.1 are now taking minutes of time.

Are you certain nothing else changed?  Same hardware, same OS, same
database configuration settings?

Once you've eliminated issues like that, you'd need to drill down deeper.
There's useful advice to help crystallize the situation at
https://wiki.postgresql.org/wiki/Slow_Query_Questions

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

Re: [PERFORM] overestimate on empty table

[Tom Lane]

Justin Pryzby  writes:
> On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote:
>> One idea is to say that relpages = reltuples = 0 is only the state that
>> prevails for a freshly-created table, and that VACUUM or ANALYZE should
>> always set relpages to at least 1 even if the physical size is zero.

>> Dunno if that would confuse people.

> What about adding && rel->rd_rel->reltuples==0, and make VACUUM/ANALYZE 
> instead
> set only reltuples=1, since that's already done at costsize.c: clamp_row_est()
> and therefor no additional confusion?

1 tuple in 0 pages is a physically impossible situation, so I'm quite
sure that way *would* confuse people.

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

Re: [PERFORM] overestimate on empty table

[Justin Pryzby]

On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > (or, the opposite of the more common problem)

> > As the queued_alters table is typically empty (and autoanalyzed with
> > relpages=0), I see "why":
> 
> > ./src/backend/optimizer/util/plancat.c
> > |if (curpages < 10 &&
> > |rel->rd_rel->relpages == 0 &&
> > |!rel->rd_rel->relhassubclass &&
> > |rel->rd_rel->relkind != RELKIND_INDEX)
> > |curpages = 10;
> 
> So I'm sure you read the comment above that, too.

> One idea is to say that relpages = reltuples = 0 is only the state that
> prevails for a freshly-created table, and that VACUUM or ANALYZE should
> always set relpages to at least 1 even if the physical size is zero.

> Dunno if that would confuse people.

What about adding && rel->rd_rel->reltuples==0, and make VACUUM/ANALYZE instead
set only reltuples=1, since that's already done at costsize.c: clamp_row_est()
and therefor no additional confusion?

Justin


-- 
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] overestimate on empty table

[Tom Lane]

Justin Pryzby  writes:
> As the queued_alters table is typically empty (and autoanalyzed with
> relpages=0), I see "why":

> ./src/backend/optimizer/util/plancat.c
> |if (curpages < 10 &&
> |rel->rd_rel->relpages == 0 &&
> |!rel->rd_rel->relhassubclass &&
> |rel->rd_rel->relkind != RELKIND_INDEX)
> |curpages = 10;

So I'm sure you read the comment above that, too.

I'm loath to abandon the principle that the planner should not believe
that tables are empty/tiny without some forcing function.  There are
going to be way more people screaming about the plans they get from
too-small rowcount estimates than the reverse.  However, maybe we could
do better about detecting whether a vacuum or analyze has really happened.
(Autovacuum won't normally touch a table until a fair number of rows have
been put in it, so if a table is tiny but has been vacuumed, we can
presume that that was a manual action.)

One idea is to say that relpages = reltuples = 0 is only the state that
prevails for a freshly-created table, and that VACUUM or ANALYZE should
always set relpages to at least 1 even if the physical size is zero.
Dunno if that would confuse people.  Or we could bite the bullet and
add a "relanalyzed" bool flag to pg_class.  It's not like that's going
to be a noticeable percentage increase in the row width ...

> But is there a better way (I don't consider adding a row of junk to be a 
> significant improvement).

Not ATM.

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

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Ulf Lohbrügge]

2017-11-08 0:45 GMT+01:00 Tom Lane :

> =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=  writes:
> > I just ran "check_postgres.pl --action=bloat" and got the following
> output:
> > ...
> > Looks fine, doesn't it?
>
> A possible explanation is that something is taking an exclusive lock
> on some system catalog and holding it for a second or two.  If so,
> turning on log_lock_waits might provide some useful info.
>
> regards, tom lane
>

I just checked my configuration and found out that "log_lock_waits" was
already enabled.

Unfortunately there is no log output of locks when those long running "SET
ROLE" statements occur.

Regards,
Ulf

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Tom Lane]

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=  writes:
> I just ran "check_postgres.pl --action=bloat" and got the following output:
> ...
> Looks fine, doesn't it?

A possible explanation is that something is taking an exclusive lock
on some system catalog and holding it for a second or two.  If so,
turning on log_lock_waits might provide some useful info.

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

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Ulf Lohbrügge]

2017-11-07 22:39 GMT+01:00 Scott Marlowe :

> On Tue, Nov 7, 2017 at 2:25 PM, Ulf Lohbrügge 
> wrote:
> > 2017-11-07 20:45 GMT+01:00 Andres Freund :
> >>
> >> On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote:
> >> > Hi,
> >> >
> >> > 2017-11-07 16:11 GMT+01:00 Andres Freund :
> >> >
> >> > > Hi,
> >> > >
> >> > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:
> >> > > > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution
> >> > > > of
> >> > > some
> >> > > > basic SET statements.
> >> > > >
> >> > > > I created about 1600 roles and use that setup for a multi tenancy
> >> > > > application:
> >> > >
> >> > > Hm. How often do you drop/create these roles?  How many other
> >> > > roles/groups is one role a member of?
> >> > >
> >> >
> >> > I create between 10-40 roles per day.
> >>
> >> Could you VACUUM (VERBOSE, FREEZE) that table and report the output?  Do
> >> you ever delete roles?
> >
> >
> > Which table do you mean exactly? pg_catalog.pg_authid?
> >
> > Sorry, forgot to write that: I delete about 2-3 roles per day.
>
> I'm gonna take a guess that pg_users or pg_roles has gotten bloated
> over time. Try running a vacuum full on both of them. It's also
> possible some other pg_xxx table is bloated out here too you might
> need to download something like checkpostgres.pl to check for bloat in
> system catalog tables.
>

As pg_user and pg_roles are views: Do you mean pg_authid? That table is
just 432kb large:

--snip--
postgres=# select pg_size_pretty(pg_total_relation_size('pg_authid'));
 pg_size_pretty

 432 kB
(1 row)
--snap--

I don't want to start a vacuum full right now because I'm not quite sure if
things will lock up. But I will do it later when there is less traffic.

I just ran "check_postgres.pl --action=bloat" and got the following output:

--snip--
POSTGRES_BLOAT OK: DB "postgres" (host:localhost) (db postgres) index
pg_shdepend_depender_index rows:? pages:9615 shouldbe:4073 (2.4X) wasted
bytes:45400064 (43 MB) | pg_shdepend_depender_index=45400064B
pg_catalog.pg_shdepend=9740288B pg_shdepend_reference_index=4046848B
pg_depend_reference_index=98304B pg_depend_depender_index=57344B
pg_catalog.pg_class=32768B pg_catalog.pg_description=16384B
pg_amop_fam_strat_index=8192B pg_amop_opr_fam_index=8192B
pg_catalog.pg_amop=8192B pg_catalog.pg_depend=8192B pg_class_oid_index=0B
pg_class_relname_nsp_index=0B pg_class_tblspc_relfilenode_index=0B
pg_description_o_c_o_index=0B
--snap--

Looks fine, doesn't it?

Cheers,
Ulf

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Scott Marlowe]

On Tue, Nov 7, 2017 at 2:25 PM, Ulf Lohbrügge  wrote:
> 2017-11-07 20:45 GMT+01:00 Andres Freund :
>>
>> On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote:
>> > Hi,
>> >
>> > 2017-11-07 16:11 GMT+01:00 Andres Freund :
>> >
>> > > Hi,
>> > >
>> > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:
>> > > > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution
>> > > > of
>> > > some
>> > > > basic SET statements.
>> > > >
>> > > > I created about 1600 roles and use that setup for a multi tenancy
>> > > > application:
>> > >
>> > > Hm. How often do you drop/create these roles?  How many other
>> > > roles/groups is one role a member of?
>> > >
>> >
>> > I create between 10-40 roles per day.
>>
>> Could you VACUUM (VERBOSE, FREEZE) that table and report the output?  Do
>> you ever delete roles?
>
>
> Which table do you mean exactly? pg_catalog.pg_authid?
>
> Sorry, forgot to write that: I delete about 2-3 roles per day.

I'm gonna take a guess that pg_users or pg_roles has gotten bloated
over time. Try running a vacuum full on both of them. It's also
possible some other pg_xxx table is bloated out here too you might
need to download something like checkpostgres.pl to check for bloat in
system catalog tables.


-- 
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 execution of SET ROLE, SET search_path and RESET ROLE

[Ulf Lohbrügge]

2017-11-07 20:45 GMT+01:00 Andres Freund :

> On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote:
> > Hi,
> >
> > 2017-11-07 16:11 GMT+01:00 Andres Freund :
> >
> > > Hi,
> > >
> > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:
> > > > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of
> > > some
> > > > basic SET statements.
> > > >
> > > > I created about 1600 roles and use that setup for a multi tenancy
> > > > application:
> > >
> > > Hm. How often do you drop/create these roles?  How many other
> > > roles/groups is one role a member of?
> > >
> >
> > I create between 10-40 roles per day.
>
> Could you VACUUM (VERBOSE, FREEZE) that table and report the output?  Do
> you ever delete roles?
>

Which table do you mean exactly? pg_catalog.pg_authid?

Sorry, forgot to write that: I delete about 2-3 roles per day.


> > > Can you manually reproduce the problem? What times do you get if you
> > > manually run the statement?
> > >
> >
> > Unfortunately not. Every time I manually execute "SET ROLE ..." the
> > statement is pretty fast. I created a simple SQL file that contains the
> > following statements:
> >
> > --snip--
> > SET ROLE tenant382;
> > SET ROLE tenant1337;
> > SET ROLE tenant2;
> > -- repeat the lines above 100k times
> > --snap--
> >
> > When I execute those statements via 'time psql < set-roles.sql', the call
> > lasts 138,7 seconds. So 300k "SET ROLE" statements result in 0,46ms per
> > call on average.
>
> And most of that is going to be roundtrip time. Hm. Could it be that
> you're just seeing the delays when pgbouncer establishes new pooling
> connections and you're attributing that to SET ROLE in your app?
>

I stopped using pgbouncer when I solely started using role 'admin' with
"SET ROLE" statements. I use a connection pool (HikariCP) that renews
connections after 30 minutes. I couldn't find a pattern yet when those slow
statements occur.

Does using a few thousands roles and schemata in postgres scale well? I
only found some theoretical descriptions of multi tenancy setups with
postgres while googling.
Using tabulator in psql cli is pretty slow, mainly
because pg_table_is_visible() is being called for many entries in pg_class.

Cheers,
Ulf

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Andres Freund]

On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote:
> Hi,
> 
> 2017-11-07 16:11 GMT+01:00 Andres Freund :
> 
> > Hi,
> >
> > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:
> > > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of
> > some
> > > basic SET statements.
> > >
> > > I created about 1600 roles and use that setup for a multi tenancy
> > > application:
> >
> > Hm. How often do you drop/create these roles?  How many other
> > roles/groups is one role a member of?
> >
> 
> I create between 10-40 roles per day.

Could you VACUUM (VERBOSE, FREEZE) that table and report the output?  Do
you ever delete roles?

> > Can you manually reproduce the problem? What times do you get if you
> > manually run the statement?
> >
> 
> Unfortunately not. Every time I manually execute "SET ROLE ..." the
> statement is pretty fast. I created a simple SQL file that contains the
> following statements:
> 
> --snip--
> SET ROLE tenant382;
> SET ROLE tenant1337;
> SET ROLE tenant2;
> -- repeat the lines above 100k times
> --snap--
> 
> When I execute those statements via 'time psql < set-roles.sql', the call
> lasts 138,7 seconds. So 300k "SET ROLE" statements result in 0,46ms per
> call on average.

And most of that is going to be roundtrip time. Hm. Could it be that
you're just seeing the delays when pgbouncer establishes new pooling
connections and you're attributing that to SET ROLE in your app?

Greetings,

Andres Freund


-- 
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 execution of SET ROLE, SET search_path and RESET ROLE

[Ulf Lohbrügge]

Hi,

2017-11-07 16:11 GMT+01:00 Andres Freund :

> Hi,
>
> On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:
> > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of
> some
> > basic SET statements.
> >
> > I created about 1600 roles and use that setup for a multi tenancy
> > application:
>
> Hm. How often do you drop/create these roles?  How many other
> roles/groups is one role a member of?
>

I create between 10-40 roles per day.

The roles tenant1 to tenant1600 are not members of any other roles. Only
the role 'admin' is member of many roles (tenant1 to tenant1600).


>
>
> > My application solely uses the role 'admin' to connect to the database.
> > When performing sql statements for a specific tenant (e.g. tenant1337), a
> > connection with user 'admin' is established and the following commands
> are
> > executed:
> >
> > SET ROLE 1337;
> > SET search_path = tenant1337;
> >
> > Then the application uses that connection to perform various statements
> in
> > the database.
>
> Just to be sure: You realize bad application code could escape from
> that, right?
>

Yes, I do. :)
My application executes all statements via an ORM tool (Hibernate). But
evil code could still get the plain DB-Connection and execute "SET ROLE
..." statements. My application used to connect as tenant1 to tenant1600
but that lead to a vast amount of postgresql connections (even with
pgbouncer).


>
>
> > My application is a web service that approximately executes some hundred
> > statements per second.
> >
> > I set "log_min_duration_statement = 200ms" and I get about 2k to 4k lines
> > per day with statements like "SET ROLE"", "SET search_path ..." and
> "RESET
> > ROLE":
> >
> > --snip--
> > 2017-11-07 09:44:30 CET [27760]: [3-1] user=admin,db=mydb LOG:  duration:
> > 901.591 ms  execute : SET ROLE "tenant762"
> > 2017-11-07 09:44:30 CET [27659]: [4-1] user=admin,db=mydb LOG:  duration:
> > 1803.971 ms  execute : SET ROLE "tenant392"
>
> That is weird.
>
>
> > Besides those peaks in statement duration, my application performs (i.e.
> > has acceptable response times) most of the time.
> >
> > Is there anything I can do to improve performance here?
> > Any help is greatly appreciated!
>
> Can you manually reproduce the problem? What times do you get if you
> manually run the statement?
>

Unfortunately not. Every time I manually execute "SET ROLE ..." the
statement is pretty fast. I created a simple SQL file that contains the
following statements:

--snip--
SET ROLE tenant382;
SET ROLE tenant1337;
SET ROLE tenant2;
-- repeat the lines above 100k times
--snap--

When I execute those statements via 'time psql < set-roles.sql', the call
lasts 138,7 seconds. So 300k "SET ROLE" statements result in 0,46ms per
call on average.

Cheers,
Ulf

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

[Andres Freund]

Hi,

On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:
> I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of some
> basic SET statements.
> 
> I created about 1600 roles and use that setup for a multi tenancy
> application:

Hm. How often do you drop/create these roles?  How many other
roles/groups is one role a member of?


> My application solely uses the role 'admin' to connect to the database.
> When performing sql statements for a specific tenant (e.g. tenant1337), a
> connection with user 'admin' is established and the following commands are
> executed:
> 
> SET ROLE 1337;
> SET search_path = tenant1337;
> 
> Then the application uses that connection to perform various statements in
> the database.

Just to be sure: You realize bad application code could escape from
that, right?


> My application is a web service that approximately executes some hundred
> statements per second.
> 
> I set "log_min_duration_statement = 200ms" and I get about 2k to 4k lines
> per day with statements like "SET ROLE"", "SET search_path ..." and "RESET
> ROLE":
> 
> --snip--
> 2017-11-07 09:44:30 CET [27760]: [3-1] user=admin,db=mydb LOG:  duration:
> 901.591 ms  execute : SET ROLE "tenant762"
> 2017-11-07 09:44:30 CET [27659]: [4-1] user=admin,db=mydb LOG:  duration:
> 1803.971 ms  execute : SET ROLE "tenant392"

That is weird.


> Besides those peaks in statement duration, my application performs (i.e.
> has acceptable response times) most of the time.
> 
> Is there anything I can do to improve performance here?
> Any help is greatly appreciated!

Can you manually reproduce the problem? What times do you get if you
manually run the statement?

Greetings,

Andres Freund


-- 
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 loss upgrading from 9.3 to 9.6

[Justin Pryzby]

On 11/6/17, 9:21 AM, "Justin Pryzby"  wrote:
> see if statistics improve:
> SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, 
> tablename, attname, n_distinct, array_length(most_common_vals,1) n_mcv,
> FROM pg_stats WHERE attname~'customers_customer' AND tablename='id' GROUP 
> BY 1,2,3,4,5 ORDER BY 1

On Mon, Nov 06, 2017 at 09:12:01PM +, Adam Torres wrote:
> I changed the statistics on av.customer_id as suggested and the number
> returned by pg_stats went from 202,333 to 904,097.

Do you mean n_distinct ?  It' be useful to see that query on pg_stats.  Also I
don't know that we've seen \d output for the tables (or at least the joined
columns) or the full query ?

> There are 11.2 million distinct customer_ids on the 14.8 million vehicle
> records.

If there's so many distinct ids, updating stats won't help the rowcount
estimate (and could even hurt) - it can only store 1 most-common-values.

Are there as many distinct values for cc.id ?

I would try to reproduce the rowcount problem with a minimal query:
explain analyze SELECT FROM av JOIN cc ON av.customer_id=cc.id; --WHERE 
cc.id<99;
Maybe the rows estimate is okay for some values and not for others, so maybe
you need to try various WHERE (with JOIN an additional tables if need be...but
without reimplementing the whole query).

I just noticed there are two conditions on dealer_id, one from table av and one
from table cc_1.  It seems likely those are co-related/non-independent
conditions..but postgres probably doesn't know that (unless you used PG96 FK
logic, or PG10 multi-variable stats). 

As a test, you could try dropping one of those conditions, or maybe a hacky
change like ROW(av.dealer_id, cc_1.dealer_id)=ROW('EC79', 'EC79'),
which postgres estimates as no more selective than a single equality test.  BTW
this is all from src/backend/utils/adt/selfuncs.c.

Justin


-- 
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 loss upgrading from 9.3 to 9.6

[Adam Brusselback]

> It has now been decided to try upgrading to 9.4 as that is the minimum to 
> support Django 1.11 (which we are trying to upgrade a backend service to).  
> The hope is whatever feature we have not configured properly in 9.6 is not 
> there in 9.4.
It's entirely possible whatever is causing your performance issue is
caused by the migration, rather than anything inherently different in
9.6.  The best test for that is setting another 9.3 server up,
restoring a backup, and testing there.  If that is very different than
what you are getting on 9.6 then it's something which changed in
Postgres, if not it's just bad stats.

I do think that it's probably better to fix your query rather than
choosing to upgrade to 9.4 rather than 9.6.  You have a crazy amount
of your query time spent in a single node.  That plan is not good.  If
that's the only query giving you trouble, work on optimizing it.

Just my $0.02

-Adam


-- 
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 loss upgrading from 9.3 to 9.6

[Adam Torres]

Justin,
Thanks for the reply.

I changed the statistics on av.customer_id as suggested and the number returned 
by pg_stats went from 202,333 to 904,097.  There are 11.2 million distinct 
customer_ids on the 14.8 million vehicle records.  Rerunning the query showed 
no significant change in time (624 seconds vs. 639 seconds) - plan is at 
https://explain.depesz.com/s/e2fo.

I went through the query looking for fields used in joins and conditions and 
applied the same steps to 7 other fields over 4 of the tables.  Most n_distinct 
values did not change much but two did change from 1.# million to -1 wrote:

On Mon, Nov 06, 2017 at 01:18:00PM +, Adam Torres wrote:
> Good morning all,
> 
> We have a problem with performance after upgrading from 9.3 to 9.6 where 
certain queries take 9 times longer to run.  On our initial attempt to upgrade, 
we noticed the system as a whole was taking longer to run through normal daily 
processes.  The query with the largest run time was picked to act as a 
measuring stick.

> https://explain.depesz.com/s/z71u
> Planning time: 8.218 ms
> Execution time: 639319.525 ms
> 
> Same query as run on 9.3
> https://explain.depesz.com/s/gjN3
> Total runtime: 272897.150 ms

Actually it looks to me like both query plans are poor..

..because of this:
| Hash Join (cost=85,086.25..170,080.80 ROWS=40 width=115) (actual 
time=32.673..84.427 ROWS=13,390 loops=1)
|Hash Cond: (av.customer_id = cc_1.id)

If there are a large number of distinct customer_ids (maybe with nearly 
equal
frequencies), it might help to
ALTER TABLE av ALTER customer_id SET STATISTICS 400
..same for cc_1.id.  And re-analyze those tables (are they large??).

see if statistics improve:
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, 
tablename, attname, n_distinct, array_length(most_common_vals,1) n_mcv,
FROM pg_stats WHERE attname~'customers_customer' AND tablename='id' GROUP 
BY 1,2,3,4,5 ORDER BY 1

Goal is to get at least an accurate value for n_distinct (but preferably 
also
storing the most frequent IDs).  I wouldn't bother re-running the query 
unless
you find that increasing stats target causes the plan to change.

Justin



-- 
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] Index-Advisor Tools

[Baron Schwartz]

On Tue, Oct 31, 2017 at 8:06 PM Julien Rouhaud  wrote:

> On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes
>  wrote:
> > I will be very happy with a tool(or a stats table) that shows the most
> > searched values from a table(since a statistic reset).
>

As a vendor, I normally stay silent on this list, but I feel compelled to
speak here. This is a feature we built support for in VividCortex. (I'm the
founder and CEO). Unlike most PostgreSQL monitoring tools, our product not
only aggregates query activity into metrics, but retains a rich and
representative sample set of the actual statements that executed, including
full parameters (even for prepared statements), and all of the properties
for the query: the connection's origin, the timestamp, latency, etc. These
are mapped visually to a scatterplot, and you can instantly see where there
are clusters of latency outliers, etc, and inspect those quickly. It
includes EXPLAIN plans and everything else you need to understand how that
statement executed. VividCortex may not be suitable for your scenario, but
our customers do use it frequently for finding queries that need indexes
and determining what indexes to add.

Re: [PERFORM] Performance loss upgrading from 9.3 to 9.6

[Justin Pryzby]

On Mon, Nov 06, 2017 at 01:18:00PM +, Adam Torres wrote:
> Good morning all,
> 
> We have a problem with performance after upgrading from 9.3 to 9.6 where 
> certain queries take 9 times longer to run.  On our initial attempt to 
> upgrade, we noticed the system as a whole was taking longer to run through 
> normal daily processes.  The query with the largest run time was picked to 
> act as a measuring stick.

> https://explain.depesz.com/s/z71u
> Planning time: 8.218 ms
> Execution time: 639319.525 ms
> 
> Same query as run on 9.3
> https://explain.depesz.com/s/gjN3
> Total runtime: 272897.150 ms

Actually it looks to me like both query plans are poor..

..because of this:
| Hash Join (cost=85,086.25..170,080.80 ROWS=40 width=115) (actual 
time=32.673..84.427 ROWS=13,390 loops=1)
|Hash Cond: (av.customer_id = cc_1.id)

If there are a large number of distinct customer_ids (maybe with nearly equal
frequencies), it might help to
ALTER TABLE av ALTER customer_id SET STATISTICS 400
..same for cc_1.id.  And re-analyze those tables (are they large??).

see if statistics improve:
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, 
attname, n_distinct, array_length(most_common_vals,1) n_mcv,
FROM pg_stats WHERE attname~'customers_customer' AND tablename='id' GROUP BY 
1,2,3,4,5 ORDER BY 1

Goal is to get at least an accurate value for n_distinct (but preferably also
storing the most frequent IDs).  I wouldn't bother re-running the query unless
you find that increasing stats target causes the plan to change.

Justin


-- 
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] Unnecessary DISTINCT while primary key in SQL

[David Rowley]

On 5 November 2017 at 04:20, 刘瑞  wrote:
> CREATE TABLE test_tbl ( k INT PRIMARY KEY, col text)
> INSERT into test_tbl select generate_series(1,1000), 'test';
>
> SQL with DISTINCT:
> test=# explain analyze select distinct col, k from test_tbl order by k limit
> 1000;
> QUERY PLAN
> --
>  Limit  (cost=1277683.22..1277690.72 rows=1000 width=36) (actual
> time=12697.994..12698.382 rows=1000 loops=1)
>->  Unique  (cost=1277683.22..1329170.61 rows=6864985 width=36) (actual
> time=12697.992..12698.311 rows=1000 loops=1)
>  ->  Sort  (cost=1277683.22..1294845.68 rows=6864985 width=36)
> (actual time=12697.991..12698.107 rows=1000 loops=1)
>Sort Key: k, col
>Sort Method: external sort  Disk: 215064kB
>->  Seq Scan on test_tbl  (cost=0.00..122704.85 rows=6864985
> width=36) (actual time=0.809..7561.215 rows=1000 loops=1)
>  Planning time: 2.368 ms
>  Execution time: 12728.471 ms
> (8 rows)

The current planner does not make much of an effort into recording
which columns remain distinct at each level. I have ideas on how to
improve this and it would include improving your case here.

9.6 did improve a slight variation of your query, but this was for
GROUP BY instead of DISTINCT. Probably there's no reason why the same
optimisation could not be applied to DISTINCT, I just didn't think of
it when writing the patch.

The item from the release notes [1] reads "Ignore GROUP BY columns
that are functionally dependent on other columns"

So, if you were to write the query as:

explain analyze select col, k from test_tbl group by col, k order by k
limit 1000;

It should run much more quickly, although still not as optimal as it could be.

[1] https://www.postgresql.org/docs/9.6/static/release-9-6.html

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Dave Nicponski]

Thank you Gunther for bringing this up.  It's been bothering me quite a bit
over time as well.

Forgive the naive question, but does the query planner's cost estimator
only track a single estimate of cost that gets accumulated and compared
across plan variants?  Or is it keeping a range or probabilistic
distribution?  I'm suspecting the former, but i bet either of the latter
would fix this rapidly.

The cases that frustrate me are where NL is chosen over something like HJ,
where if the query planner is slightly wrong on the lower side, then NL
would certainly beat HJ (but by relatively small amounts), but a slight
error on the higher side mean that the NL gets punished tremendously, do to
the big-o penalty difference it's paying over the HJ approach.  Having the
planner with some notion of the distribution might help it make a better
assessment of the potential consequences for being slightly off in its
estimates.  If it notices that being off on a plan involving a NL sends the
distribution off into hours instead of seconds, it could potentially avoid
it even if it might be slightly faster in the mean.

 If i ever find time, maybe i'll try to play around with this idea
and see how it performs... 

   -dave-

On Fri, Nov 3, 2017 at 11:13 AM, Gunther  wrote:

> On 11/3/2017 10:55, legrand legrand wrote:
>
>> To limit NL usage, wouldn't a modified set of Planner Cost Constants
>> https://www.postgresql.org/docs/current/static/runtime-config-query.html
>> > >
>>
>> seq_page_cost
>> random_page_cost
>> cpu_tuple_cost
>> cpu_index_tuple_cost
>> cpu_operator_cost
>>
>> be more hash join freindly (as Oracle' optimizer_index_cost_adj )?
>>
>> I twiddled with some of these and could nudge it toward a Sort Merge
> instead NL. But it's hit or miss.
>
> May be there should be a tool which you can run periodically which will
> test out the installation to see how IO, CPU, and memory performs. Or,
> again, these statistics should be collected during normal operation so that
> nobody needs to guess them or test them in complex procedures. As the
> system runs, it should sample the seq_page_cost and random_page_cost
> (noticing that it has a SSD or HDD) and it should see how much disk read is
> from cache and how much goes out to disk. Why isn't the executor of queries
> the best person to ask for these cost constants?
>
> regards,
> -Gunther
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



-- 

Dave Nicponski

Chief Technology Officer

917.696.3081


|


d...@seamlessdocs.com

30 Vandam Street. 2nd Floor. NYC
855.77.SEAMLESS | SeamlessGov.com 

Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Gunther]

On 11/3/2017 10:55, legrand legrand wrote:

To limit NL usage, wouldn't a modified set of Planner Cost Constants
https://www.postgresql.org/docs/current/static/runtime-config-query.html


seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost

be more hash join freindly (as Oracle' optimizer_index_cost_adj )?

I twiddled with some of these and could nudge it toward a Sort Merge 
instead NL. But it's hit or miss.


May be there should be a tool which you can run periodically which will 
test out the installation to see how IO, CPU, and memory performs. Or, 
again, these statistics should be collected during normal operation so 
that nobody needs to guess them or test them in complex procedures. As 
the system runs, it should sample the seq_page_cost and random_page_cost 
(noticing that it has a SSD or HDD) and it should see how much disk read 
is from cache and how much goes out to disk. Why isn't the executor of 
queries the best person to ask for these cost constants?


regards,
-Gunther


--
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] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Gunther]

Just throwing out some more innovative ideas.

Materialized join tables, I have read somewhere. OK, difficult to keep 
consistent with transactions. Forget that.


But, why not collect statistics on every join that is processed, even if 
the query is interrupted. Then as more and more plans are run, and 
interrupted for being too slow, statistics on the joins are collected 
and can inform the optimizer next time not to use that approach.


Would work like magic for a user.

User writes a query. It runs 3 minutes and as no result. User interrupts 
the query (THANKS PgSQL for allowing that, unlike Oracle!). Now the 
statistics has already been gathered.


User reruns the query, not changing anything. Because the statistics on 
(some of) the joins has been gathered, at least with an initial sample, 
now the planner will likely choose a different plan. Say, now the 
results come in at 2 minutes and the user is satisfied. But still more 
complete statistics was collected.


Now the user changes a few query parameters and runs the query again, or 
puts it into a more complex query. This time the planner has even more 
statistics and chooses an even better plan. And lo and behold now the 
results come in at 10 seconds!


At no point did the user have to analyze the explain plan, come up with 
hints and tricks and nudges to the optimizer. And at no point did the 
user have to become DBA to run some outlandish PL/SQL procedures for 
which he does not have the license key or the special privileges.


But until that is done, please put in the pg_hint_plan.c. Hints don't 
hurt. If you don't like them, don't use them.,


regards,
-Gunther


--
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] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Gunther]

On Fri, Nov 3, 2017 at 5:28 AM, Thomas Kellerer  wrote:

I do like Oracle's approach with SQL profiles, where you can force the
optimizer to try harder to find a good execution plan. I _think_ it even
runs the statement with multiple plans and compares the expected outcome
with the actual values. Once a better plan is found that plan can be
attached to that query and the planner will use that plan with subsequent
executions.
I have used that approach with Oracle. I didn't like it. It is too 
difficult, too complicated. Requires all sorts of DBA privileges. 
Nothing that would help a lowly user trying his ad-hoc queries.


I think a "dynamic feedback plan optimization" would be more innovative 
and ultimately deliver better on the original RDBMS vision. The RDBMS 
should exert all intelligence that it can to optimize the query 
execution. (I know that means: no reliance on hints.)


There is so much more that could be done, such as materialized and 
potentially indexed partial results. (I know Oracle as materialized 
partial results).


But the dynamic feedback plan would be even cooler.  So that means the 
outer relation should be built or sampled to estimate the selectivity, 
the inner relation should be built completely, and if it is too large, 
it should be thrown back to the optimizer to change the plan.


Or may be the planner needs some second look pattern matching 
criticizer: Any pattern of Nested Loop I would re-check and possibly 
sample a few rows. And Nested Loop with costly inner loop should almost 
always be avoided. Nested Loop of Seq Scan is a no-no unless it can be 
proven that the cardinality of the inner relation to scan is less than 100.


But even more, once you have the inner and outer table of a Nested Loop 
built or sampled, there should be no reason not to run the Hash Join. I 
guess I still don't get why the optimizer even today would EVER consider 
a Nested Loop over a Hash Join, unless there is some clear indication 
that the query will be used to just get the FIRST ROWS (Oracle hint) and 
that those first rows will actually exist (user waits 30 minutes at 100% 
CPU only to be informed that the query has no results!), and that the 
results are likely to come out early in the Nested Loop! So many 
constraints to make that Nested Loop plan a successful strategy. Why 
ever choose it???


I guess, hints or no hints, I think Nested Loops should not be used by 
the optimizer unless it has positive indication  that it meets all the 
criteria for being a good strategy, i.e., that there is a continuous 
path of indexed columns starting with constant query parameters. This is 
the usual OLTP query. And that is what Nested Loops are for. But in all 
other cases, and if space allows at all, always use Hash Joins. It is 
even cheaper to do a trial and error! Assume that space will allow, and 
quit if it doesn't, rather than being sheepish and going to a 1 hour CPU 
bound operation. Because if space does not allow, the chance for Nested 
Loop being a good idea is also close to nil! So if space doesn't allow, 
it would be Sort-Merge on Disk. Especially if the query has a DISTINCT 
or ORDER BY clause anyway! Why is that not always a better strategy?


And yes, until all this is figured out: by all means include the 
pg_hint_plan.c -- pretty please!


regards,
-Gunther



--
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] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Adam Brusselback]

On Fri, Nov 3, 2017 at 5:28 AM, Thomas Kellerer  wrote:
> I do like Oracle's approach with SQL profiles, where you can force the
> optimizer to try harder to find a good execution plan. I _think_ it even
> runs the statement with multiple plans and compares the expected outcome
> with the actual values. Once a better plan is found that plan can be
> attached to that query and the planner will use that plan with subsequent
> executions.

I also think that this is a really cool approach.  For those specific
problem queries, pretty much tell the optimizer "do your best to make
this as efficient as possible".

To make that more useful though, you'd probably need a shared query
cache that would be persisted through restarts.  I'd assume if you
have a problem query, this very heavy "planning / optimization"
operation would not be something you wanted every connection to have
to do every time they connect.

I wish I was more knowledgeable about the internals so I could more
clearly see how a system like that could come together, and what other
groundwork would be needed building up to it.


-- 
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] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Laurenz Albe]

Gunther wrote:
> > Bad choices are almost always caused by bad estimates.
> > Granted, there is no way that estimates can ever be perfect.
> > ...
> > Looking deeper, I would say that wrongly chosen nested loop joins
> > often come from an underestimate that is close to zero.
> > PostgreSQL already clamps row count estimates to 1, that is, it will
> > choose an estimate of 1 whenever it thinks fewer rows will be returned.
> > 
> > Perhaps using a higher clamp like 2 would get rid of many of your
> > problems, but it is a difficult gamble as it will also prevent some
> > nested loop joins that would have been the best solution.
> 
> Wow, that is very interesting! Are you saying that if PgSQL can't know 
> what the cardinality is, it assumes a default of 1? That would be very 
> slanted a guess. I would think a couple of hundred would be more 
> appropriate, or 10% of the average of the base tables for which it does 
> have statistics. I would wonder if changing 1 to 2 would make much 
> difference, as Seq Search over 1 to 10 tuples should generally be better 
> than any other approach, as long as the 1-10 tuples are already readily 
> available.

No, it is not like that.
When PostgreSQL cannot come up with a "real" estimate, it uses
default selectivity estimates.

See include/utils/selfuncs.h:

/*
 * Note: the default selectivity estimates are not chosen entirely at random.
 * We want them to be small enough to ensure that indexscans will be used if
 * available, for typical table densities of ~100 tuples/page.  Thus, for
 * example, 0.01 is not quite small enough, since that makes it appear that
 * nearly all pages will be hit anyway.  Also, since we sometimes estimate
 * eqsel as 1/num_distinct, we probably want DEFAULT_NUM_DISTINCT to equal
 * 1/DEFAULT_EQ_SEL.
 */

/* default selectivity estimate for equalities such as "A = b" */
#define DEFAULT_EQ_SEL  0.005

/* default selectivity estimate for inequalities such as "A < b" */
#define DEFAULT_INEQ_SEL  0.

/* default selectivity estimate for range inequalities "A > b AND A < c" */
#define DEFAULT_RANGE_INEQ_SEL  0.005

/* default selectivity estimate for pattern-match operators such as LIKE */
#define DEFAULT_MATCH_SEL   0.005

/* default number of distinct values in a table */
#define DEFAULT_NUM_DISTINCT  200

/* default selectivity estimate for boolean and null test nodes */
#define DEFAULT_UNK_SEL 0.005
#define DEFAULT_NOT_UNK_SEL (1.0 - DEFAULT_UNK_SEL)

Those selectivity estimates are factors, not absolute numbers.

The clamp to 1 happens when, after applying all selectivity factors, the
result is less than 1, precisely to keep the optimizer from choosing a plan
that would become very expensive if a branch is executed *at all*.

> > Finally, even though the official line of PostgreSQL is to *not* have
> > query hints, and for a number of good reasons, this is far from being
> > an unanimous decision.  The scales may tip at some point, though I
> > personally hope that this point is not too close.
> 
> I am glad to hear that hints are not completely ruled out by the 
> development team. Definitely Oracle hints are painful and should not be 
> replicated as is.  Butmay be I can nudge your (and others') personal 
> tastes with the following.

Didn't work for me.
Your hints look just like what Oracle does.

There have been better proposals that aim at fixing the selectivity
estimates, e.g. "multiply your estimate for this join by three".

> In my Aqua 
> Data Studio, if I put the set statement before the select statement, the 
> combined statement doesn't return any results. May be I am doing 
> something wrong. If there is a way, then I would ave what I need.

Check the SQL statements that are generated by your Aqua Data Studio!

Yours,
Laurenz Albe


-- 
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] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Gunther]

Thanks you for your thoughtful reply, Laurenz (funny that the people 
interested in this topic are named Laurent and Laurenz :)



PostgreSQL doesn't have a way to tell if a query is an OLAP query
running against a star schema or a regular OLTP query, it will treat
both in the same fashion.
right, of course, and I would not want to go down that road. There OLAP 
vs. OLTP are not just two cut and dry options, and neither is "star 
schema" but one way in which to lay out a simple data model. The real 
world is always more complex than such cut and dry choices

However, it is not true that PostgreSQL "perfers nested loops".
Sometimes a nested loop join is the only sane and efficient
way to process a query ...
of course, it's not preferring NLs deliberately, but it happens awfully 
often (and not just with PgSQL, same problems I have had with Oracle 
over the years).

Bad choices are almost always caused by bad estimates.
Granted, there is no way that estimates can ever be perfect.
...
Looking deeper, I would say that wrongly chosen nested loop joins
often come from an underestimate that is close to zero.
PostgreSQL already clamps row count estimates to 1, that is, it will
choose an estimate of 1 whenever it thinks fewer rows will be returned.

Perhaps using a higher clamp like 2 would get rid of many of your
problems, but it is a difficult gamble as it will also prevent some
nested loop joins that would have been the best solution.
Wow, that is very interesting! Are you saying that if PgSQL can't know 
what the cardinality is, it assumes a default of 1? That would be very 
slanted a guess. I would think a couple of hundred would be more 
appropriate, or 10% of the average of the base tables for which it does 
have statistics. I would wonder if changing 1 to 2 would make much 
difference, as Seq Search over 1 to 10 tuples should generally be better 
than any other approach, as long as the 1-10 tuples are already readily 
available.

Finally, even though the official line of PostgreSQL is to *not* have
query hints, and for a number of good reasons, this is far from being
an unanimous decision.  The scales may tip at some point, though I
personally hope that this point is not too close.


I am glad to hear that hints are not completely ruled out by the 
development team. Definitely Oracle hints are painful and should not be 
replicated as is.  Butmay be I can nudge your (and others') personal 
tastes with the following.


You suggested this:


One pragmatic solution would be to wrap every query that you know
to be an OLAP query with
BEGIN;
SET LOCAL enable_nestloop=off;
SELECT ...
COMMIT;
I would also like to put the set enable_nestloop = false statement into 
a combined statement, but when I do it in a transaction like you showed, 
it would not work for a normal PreparedStatement just expecting a 
ResultSet, or at least I haven't been able to make that work. In my Aqua 
Data Studio, if I put the set statement before the select statement, the 
combined statement doesn't return any results. May be I am doing 
something wrong. If there is a way, then I would ave what I need.


If not, I think it might be an easy thing to add.

We already have different scopes of these optimizer parameters like 
enable_nestloop


1. the system wide scope

2. a session wide scope

and I see no reason why one could not just add a non-disruptive syntax 
form to change these parameters on a statement-wide scope. By all means 
in a comment.


Why not

--! set enable_nestloop = false
--! set work_mem = '20 MB'
SELECT *
  FROM 
;

something like that. It would not be a big deal, no completely new 
obscure hint syntax.


And may be, if that is possible so far, then why not add a CTE scope as 
well:


WITH Foo AS (
--! set enable_nestloop = false
  SELECT * FROM ... INNER JOIN ... INNER JOIN ... INNER JOIN ... ...
) , Bar AS (
  SELECT * FROM Foo INNER JOIN IndexedTable USING(a, b, c)
)
SELECT * FROM Bar ...
;

this would keep the nestloop off for the CTE Foo with that complex join 
but allow it to be used for the CTE Bar or the ultimate query.


I think these features should be relatively easy to add without causing 
SQL compatibility issue and also not opening a can of worms with obscure 
hint features that need a lot of work to implement correctly.


But while we are at dreaming up solution, I think materialized indexed 
sub-plans would also be a nice ting, especially when dealing with CTEs. 
This could be controlled manually to begin with:


WITH Foo AS (
--! set enable_nestloop = false
  SELECT * FROM ... INNER JOIN ... INNER JOIN ... INNER JOIN ... ...
)  MATERIALIZE INDEX ON(a, b, c)
, Bar AS (
  SELECT * FROM Foo INNER JOIN IndexedTable USING(a, b, c)
)
SELECT * FROM Bar ...
;

And of course if we don't want to disturb SQL syntax, the "materialize 
index on ..." clause could be in a --! comment.


But then, to dream on, PgSQL could make sub-query plans a temporary 
table and add indexes that it needs for it on the fly, because 

Re: [PERFORM] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Gunther]

Thanks for your support Laurent.

I have an idea on one thing you said:


Just adding to your voice. I recently experienced the same issue with a complex 
multi-table view, including pivots, and was surprised to see all the nested 
loops everywhere

and here is the clue for me:

in spite of indices being available.
I would say that sometimes indexes are detrimental. If you don't need 
them for other reasons, you might want to not have them. And without the 
index, the Nested Loop strategy might not be chosen.


But that is a side-issue, because it can often not be avoided. Just 
saying in case it might help.


I also found the opposite now. In the query that made me "blow the lid" 
and "complain" here, my team decided to add an index and that did not 
get rid of Nested Loops but at least made the inner table access indexed 
rather than a table scan and the performance ended up OK. But it's not 
always predictable, and these indexes could trap the planner into 
sub-optimal solutions still.


I think there is an opportunity for a PgSQL query plan extension, 
especially wen dealing with CTE (WITH-clauses), PgSQL could make them a 
temporary table and add indexes that it needs for it on the fly, because 
after it has done one pass over the inner loop sequential scan it knows 
perfectly well how many rows it has, and knowing how many more 
iterations are coming from the sub-query that's driving the Nested Loop, 
it could decide that it's much faster to put an index on the nested 
relation, temporarily materialized. Or it could even decide to change 
it's plan mid-way and do the Hash Join.


This is why I had always dreamed that the PgSQL optimizer had some easy 
API where one could plug in experimental strategies. I personally am 
extremely efficient with XSLT for complex intelligent algorithms, and I 
dream of a PgSQL query plan structure exposed as XML which an XSLT 
plugin could then process to edit the plan. People could experiment with 
awesome intelligent new strategies based on statistics gathered along 
the way of the execution.


regards,
-Gunther


--
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] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[Laurenz Albe]

Gunther wrote:
> But there 
> is one thing that keeps bothering me both with Oracle and PgSQL. And 
> that is the preference for Nested Loops.

[...]

> But the issue is bulk searches, reports, and any analytic queries 
> scenarios. In those queries Nested Loops are almost always a bad choice, 
> even if there is an index. In over 20 years of working with RDBMs this 
> has been my unfailing heuristics. A report runs slow? Look at plan, is 
> there a Nested Loop? Yes? Squash it! And the report runs 10x faster 
> instantaneously.

[...]

> If you can set enable_nestloop off and the Hash Join is chosen and the 
> performance goes from 1 hour of 100% CPU to 10 seconds completion time, 
> then something is deadly wrong.

[...]

> The point is that Nested Loops should never be chosen except in index 
> lookup situations or may be memory constraints.
> 
> How can I prevent it on a query by query scope? I cannot set 
> enable_nestloop = off because one query will be for a full report, wile 
> another one might have indexed constraints running in the same session, 
> and I don't want to manage side effects and remember to set 
> enable_nestloop parameter on and off.
> 
> There must be a way to tell the optimizer to penalize nested loops to 
> make them the last resort. In Oracle there are those infamous hints, but 
> they don't always work either (or it is easy to make mistakes that you 
> get no feedback about).
> 
> Is there any chance PgSQL can get something like a hint feature?

PostgreSQL doesn't have a way to tell if a query is an OLAP query
running against a star schema or a regular OLTP query, it will treat
both in the same fashion.

I also have had to deal with wrongly chosen nested loop joins, and
testing a query with "enable_nestloop=off" is one of the first things
to try in my experience.

However, it is not true that PostgreSQL "perfers nested loops".
Sometimes a nested loop join is the only sane and efficient way to
process a query, and removing that capability would be just as
bad a disaster as you are experiencing with your OLAP queries.

Bad choices are almost always caused by bad estimates.
Granted, there is no way that estimates can ever be perfect.

So what could be done?

One pragmatic solution would be to wrap every query that you know
to be an OLAP query with

BEGIN;
SET LOCAL enable_nestloop=off;
SELECT ...
COMMIT;

Looking deeper, I would say that wrongly chosen nested loop joins
often come from an underestimate that is close to zero.
PostgreSQL already clamps row count estimates to 1, that is, it will
choose an estimate of 1 whenever it thinks fewer rows will be returned.

Perhaps using a higher clamp like 2 would get rid of many of your
problems, but it is a difficult gamble as it will also prevent some
nested loop joins that would have been the best solution.

Finally, even though the official line of PostgreSQL is to *not* have
query hints, and for a number of good reasons, this is far from being
an unanimous decision.  The scales may tip at some point, though I
personally hope that this point is not too close.

Yours,
Laurenz Albe


-- 
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] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

[l...@laurent-hasson.com]

> -Original Message-
> From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-
> ow...@postgresql.org] On Behalf Of Gunther
> Sent: Wednesday, November 01, 2017 20:29
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] OLAP/reporting queries fall into nested loops over seq
> scans or other horrible planner choices
> 
> Hi, this is Gunther, have been with PgSQL for decades, on an off this list.
> Haven't been on for a long time making my way just fine. But there is one 
> thing
> that keeps bothering me both with Oracle and PgSQL. And that is the
> preference for Nested Loops.
> 
> Over the years the archives have questions about Nested Loops being chosen
> over Hash Joins. But the responses seem too specific to the people's queries,
> ask many questions, make them post the query plans, and often end up
> frustrating with suggestions to change the data model or to add an index and
> stuff like that.
> 
> One should not have to go into that personal detail.
> 
> There are some clear boundaries that a smart database should just never cross.
> 
> Especially with OLAP queries. Think a database that is fine for OLTP, has
> indexes and the index based accesses for a few records joined with a dozen
> other tables all with indexes is no problem. If you fall into a Seq Scan 
> scenario
> or unwanted Hash Join, you usually forgot to add an index or forgot to put 
> index
> columns into your join or other constraints. Such are novice questions and we
> should be beyond that.
> 
> But the issue is bulk searches, reports, and any analytic queries scenarios. 
> In
> those queries Nested Loops are almost always a bad choice, even if there is an
> index. In over 20 years of working with RDBMs this has been my unfailing
> heuristics. A report runs slow? Look at plan, is there a Nested Loop? Yes?
> Squash it! And the report runs 10x faster instantaneously.
> 
> So, all the more troublesome is if any database system (here PgSQL) would
> ever fall into a Nested Loop trap with CPU spinning at 100% for several
> minutes, with a Nested Loop body of anything from a Seq Scan or worse with a
> cardinality of anything over 10 or 100. Nested Loops of Nested Loops or Nested
> Loops of other complex query plan fragments should be a no-no and chosen
> only as an absolute last resort when the system cannot find enough memory,
> even then disk based merge sort should be better, i.e., Nested Loops should
> never be chosen. Period.
> 
> If you can set enable_nestloop off and the Hash Join is chosen and the
> performance goes from 1 hour of 100% CPU to 10 seconds completion time,
> then something is deadly wrong. And it doesn't matter to me if I should have
> re-written my query in some funny ways or tweaked my data model, these are
> all unacceptable options when you have a complex system with hybrid
> OLTP/OLAP uses. Don't tell me to de-normalize. I know I can materialize joins
> in tables which I can then use again in joins to save time. But that is not 
> the
> point here.
> 
> And I don't think tweaking optimizer statistics is the solution either.
> Because optimizer statistics quickly become worthless when your criteria get
> more complex.
> 
> The point is that Nested Loops should never be chosen except in index lookup
> situations or may be memory constraints.
> 
> How can I prevent it on a query by query scope? I cannot set enable_nestloop =
> off because one query will be for a full report, wile another one might have
> indexed constraints running in the same session, and I don't want to manage
> side effects and remember to set enable_nestloop parameter on and off.
> 
> There must be a way to tell the optimizer to penalize nested loops to make
> them the last resort. In Oracle there are those infamous hints, but they don't
> always work either (or it is easy to make mistakes that you get no feedback
> about).
> 
> Is there any chance PgSQL can get something like a hint feature? Or is there a
> way to use postgresql.conf to penalize nested loops so that they would only 
> ever
> be chosen in the most straight-forward situations as with query parameters
> that are indexed? I know I need to have sufficient work_mem, but if you can 
> set
> enable_nestloop = off and you get the desired Hash Join, there is obviously
> sufficient work_mem, so that isn't the answer either.
> 
> Thanks for listening to my rant.
> 
> regards,
> -Gunther
> 
> 
> 
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

 [Laurent Hasson] 
Hello Gunther,

Just adding to your voice. I recently experienced the same issue with a complex 
multi-table view, including pivots, and was surprised to see all the nested 
loops everywhere in spite of indices being available. I spent a lot of time 
optimizing the query and went from about 1h to about 3mn, but penalizing nested 
loops in favor of other "joining" 

Re: [PERFORM] Cursor vs Set Operation

[Merlin Moncure]

On Mon, Oct 30, 2017 at 5:51 PM, patibandlakoshal
 wrote:
> From performance standpoint I  thought set operation was better than Cursor.
> But I found Cursor to be more effective than Set operation. Is there a way
> we can force optimizer to use cursor plan. QUERY PLAN

You're going to have to be  a little more specific.  In particular, I
have no idea what a 'cursor plan' is.  What precise operations did you
do?

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] Index-Advisor Tools

[Julien Rouhaud]

On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes
 wrote:
> I will be very happy with a tool(or a stats table) that shows the most
> searched values from a table(since a statistic reset).  i.e.:
>
> table foo (id int, year int)
>
> top 3 searched value for year field: 2017(500x), 2016(300x), 2015(55x)
>
> With this info we can create partial indexes or do a table partitioning.
>
>
>
> 2017-10-31 15:25 GMT-02:00 Neto pr :
>>
>> Thanks for reply Antony.
>> But from what I've read, HYPOPG only allows you to create hypothetical
>> indexes, so the DBA can analyze if it brings benefits.
>> What I would like is a tool that from a SQL Query indicates which indexes
>> would be recommended to decrease the response time.

powa + pg_qualstats will give you this kind of information, and it can
analyse the actual queries and suggest indexes that could boost them,
or show constant repartition for the different WHERE clauses.

You can get more information on http://powa.readthedocs.io/en/latest/.


-- 
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] Index-Advisor Tools

[Yves Dorfsman]

I have not used it yet, but from the presentation, very promising:

https://medium.com/@ankane/introducing-dexter-the-automatic-indexer-for-postgres-5f8fa8b28f27

https://github.com/ankane/dexter

-- 
https://yves.zioup.com
gpg: 4096R/32B0F416 



-- 
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] Index-Advisor Tools

[Alexandre de Arruda Paes]

I will be very happy with a tool(or a stats table) that shows the most
searched values from a table(since a statistic reset).  i.e.:

table foo (id int, year int)

top 3 searched value for year field: 2017(500x), 2016(300x), 2015(55x)

With this info we can create partial indexes or do a table partitioning.



2017-10-31 15:25 GMT-02:00 Neto pr :

> Thanks for reply Antony.
> But from what I've read, HYPOPG only allows you to create hypothetical
> indexes, so the DBA can analyze if it brings benefits.
> What I would like is a tool that from a SQL Query indicates which indexes
> would be recommended to decrease the response time.
>
> Best Regards
> Neto
>
> 2017-10-31 15:19 GMT-02:00 Anthony Sotolongo :
>
>> Hi Neto,  maybe HypoPG
>> Can help you:
>>
>> https://github.com/dalibo/hypopg
>>
>> El 31 oct. 2017 2:13 PM, "Neto pr"  escribió:
>>
>>>
>>> Hello All I'm researching on Index-Advisor Tools to be applied in SQL
>>> queries. At first I found this: - EnterpriseDB -
>>> https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgre
>>> s_Advanced_Server_Guide.1.56.html Someone would know of other tools for
>>> this purpose. I'd appreciate it if you can help me.
>>>
>>> Best Regards
>>> Neto
>>>
>>
>

Re: [PERFORM] Index-Advisor Tools

[Neto pr]

Thanks for reply Antony.
But from what I've read, HYPOPG only allows you to create hypothetical
indexes, so the DBA can analyze if it brings benefits.
What I would like is a tool that from a SQL Query indicates which indexes
would be recommended to decrease the response time.

Best Regards
Neto

2017-10-31 15:19 GMT-02:00 Anthony Sotolongo :

> Hi Neto,  maybe HypoPG
> Can help you:
>
> https://github.com/dalibo/hypopg
>
> El 31 oct. 2017 2:13 PM, "Neto pr"  escribió:
>
>>
>> Hello All I'm researching on Index-Advisor Tools to be applied in SQL
>> queries. At first I found this: - EnterpriseDB -
>> https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgre
>> s_Advanced_Server_Guide.1.56.html Someone would know of other tools for
>> this purpose. I'd appreciate it if you can help me.
>>
>> Best Regards
>> Neto
>>
>

Re: [PERFORM] Index-Advisor Tools

[Anthony Sotolongo]

Hi Neto,  maybe HypoPG
Can help you:

https://github.com/dalibo/hypopg

El 31 oct. 2017 2:13 PM, "Neto pr"  escribió:

>
> Hello All I'm researching on Index-Advisor Tools to be applied in SQL
> queries. At first I found this: - EnterpriseDB -
> https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_
> Postgres_Advanced_Server_Guide.1.56.html Someone would know of other
> tools for this purpose. I'd appreciate it if you can help me.
>
> Best Regards
> Neto
>

Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

[Tom Lane]

Benjamin Coutu  writes:
> Please consider the following three semantically equivalent, but differently 
> written queries:
> ...
> Queries A + B generate the same plan and execute as follows:

> ->  Finalize HashAggregate  (cost=32879.78..33102.62 rows=22285 
> width=12) (actual time=450.724..458.667 rows=15521 loops=1)
>   Group Key: b.item
>   Filter: (sum(b.amount) >= '1'::double precision)
>   Rows Removed by Filter: 48277

> Plan C though, thanks to the "offset optimization fence", executes the 
> following, more efficient plan:

>   ->  Subquery Scan on c  (cost=32768.35..33269.76 rows=7428 width=12) 
> (actual time=456.591..475.204 rows=15521 loops=1 total=475.204)
> Filter: (c.stock >= '1'::double precision)
> Rows Removed by Filter: 48277
> ->  Finalize HashAggregate  (cost=32768.35..32991.20 rows=22285 
> width=12) (actual time=456.582..468.124 rows=63798 loops=1 total=468.124)
>   Group Key: b.item

Huh.  So we can see that the grouping step produces 63798 rows in reality,
of which 15521 pass the >= filter condition.  In Plan C, the planner
estimates the total number of group rows at 22285; then, having no
information about the statistics of c.stock, it uses DEFAULT_INEQ_SEL
(0.333) as the filter selectivity estimate, arriving at 7428 as the
estimated number of result rows for the subquery.

In Plan A+B, the planner presumably estimated the number of group rows at
22285 as well, but then it comes up with 22285 as the overall result.
Uh, what about the HAVING?

Evidently, the difference between 7428 and 22285 estimated rows out of
the subquery is enough to prompt a change in join plan for this query.
Since the true number is in between, it's just luck that Plan C is faster.
I don't put any great amount of stock in one join plan or the other
having been chosen for this case based on those estimates.

But ... what about the HAVING?  I took a quick look around and couldn't
find anyplace where the selectivity of an aggregate's filter condition
gets accounted for, which explains this observed behavior.  That seems
like a big oversight :-(

Now, it's true that we're basically never gonna be able to do better than
default selectivity estimates for post-aggregation filter conditions.
Maybe, at some point in the dim past, somebody intentionally decided that
applying the standard selectivity estimation logic to HAVING clauses was a
loser.  But I don't see any comments to that effect, and anyway taking the
selectivity as 1.0 all the time doesn't seem very bright either.

Changing this in back branches might be too much of a behavioral change,
but it seems like we oughta change HEAD to apply standard selectivity
estimation to the HAVING clause.

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

Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

[Benjamin Coutu]

There is actually another separate issue here apart from that the planner 
obviously choosing the wrong plan as originally described in my last message, a 
plan it knows to be more expensive based on cost estimates.

Take a look at the way the filter condition is treated differently when 
estimating the number of returned rows when applied in different nodes.

Queries A/B:

 ->  Finalize HashAggregate  (cost=32879.78..33102.62 rows=22285 width=12) 
(actual time=450.724..458.667 rows=15521 loops=1)
  Group Key: b.item
  Filter: (sum(b.amount) >= '1'::double precision)
  Rows Removed by Filter: 48277
  ->  Gather ...

Query C:

 ->  Subquery Scan on c  (cost=32768.35..33269.76 rows=7428 width=12) (actual 
time=456.591..475.204 rows=15521 loops=1)
Filter: (c.stock >= '1'::double precision)
Rows Removed by Filter: 48277
->  Finalize HashAggregate  (cost=32768.35..32991.20 rows=22285 
width=12) (actual time=456.582..468.124 rows=63798 loops=1)
  Group Key: b.item
  ->  Gather ...

Interestingly enough the subquery scan with query C correctly accounts for the 
filter when estimating rows=7428, while A/B doesn't seem to account for the 
filter in the HasAggregate node (estimated rows=22285). This looks like a bug.



-- 
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] Cheaper subquery scan not considered unless offset 0

[Benjamin Coutu]

It's not a modified postgres version. It's simply for my convenience that my 
tooling calculats "total" as "actual time" multiplied by "loops". Looks like I 
didn't properly strip that away when copy-pasting.

Here are the queries and original plans again, sorry for the confusion.

Query A:

SELECT * FROM items a INNER JOIN (
  SELECT item, sum(amount) stock FROM stocktransactions b GROUP BY item HAVING 
sum(amount) >= 1
) c ON c.item = a."ID"

Query B:

SELECT * FROM items a INNER JOIN (
  SELECT item, sum(amount) stock FROM stocktransactions b GROUP BY item
) c ON c.item = a."ID" WHERE c.stock >= 1

Query C:

SELECT * FROM items a INNER JOIN (
  SELECT item, sum(amount) stock FROM stocktransactions b GROUP BY item OFFSET 0
) c ON c.item = a."ID" WHERE c.stock >= 1

Queries A + B generate the same plan and execute as follows:

Merge Join  (cost=34935.30..51701.59 rows=22285 width=344) (actual 
time=463.824..659.553 rows=15521 loops=1)
  Merge Cond: (a."ID" = b.item)
  ->  Index Scan using "PK_items_ID" on items a  (cost=0.42..15592.23 
rows=336083 width=332) (actual time=0.012..153.899 rows=336064 loops=1)
  ->  Sort  (cost=34934.87..34990.59 rows=22285 width=12) (actual 
time=463.677..466.146 rows=15521 loops=1)
Sort Key: b.item
Sort Method: quicksort  Memory: 1112kB
->  Finalize HashAggregate  (cost=32879.78..33102.62 rows=22285 
width=12) (actual time=450.724..458.667 rows=15521 loops=1)
  Group Key: b.item
  Filter: (sum(b.amount) >= '1'::double precision)
  Rows Removed by Filter: 48277
  ->  Gather  (cost=27865.65..32545.50 rows=44570 width=12) (actual 
time=343.715..407.243 rows=162152 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Partial HashAggregate  (cost=26865.65..27088.50 
rows=22285 width=12) (actual time=336.416..348.105 rows=54051 loops=3)
  Group Key: b.item
  ->  Parallel Seq Scan on stocktransactions b  
(cost=0.00..23281.60 rows=716810 width=12) (actual time=0.015..170.646 
rows=579563 loops=3)
Planning time: 0.277 ms
Execution time: 661.342 ms

Plan C though, thanks to the "offset optimization fence", executes the 
following, more efficient plan:

Nested Loop  (cost=32768.77..41146.56 rows=7428 width=344) (actual 
time=456.611..525.395 rows=15521 loops=1)
  ->  Subquery Scan on c  (cost=32768.35..33269.76 rows=7428 width=12) (actual 
time=456.591..475.204 rows=15521 loops=1)
Filter: (c.stock >= '1'::double precision)
Rows Removed by Filter: 48277
->  Finalize HashAggregate  (cost=32768.35..32991.20 rows=22285 
width=12) (actual time=456.582..468.124 rows=63798 loops=1)
  Group Key: b.item
  ->  Gather  (cost=27865.65..32545.50 rows=44570 width=12) (actual 
time=348.479..415.463 rows=162085 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Partial HashAggregate  (cost=26865.65..27088.50 
rows=22285 width=12) (actual time=343.952..355.912 rows=54028 loops=3)
  Group Key: b.item
  ->  Parallel Seq Scan on stocktransactions b  
(cost=0.00..23281.60 rows=716810 width=12) (actual time=0.015..172.235 
rows=579563 loops=3)
  ->  Index Scan using "PK_items_ID" on items a  (cost=0.42..1.05 rows=1 
width=332) (actual time=0.003..0.003 rows=1 loops=15521)
Index Cond: ("ID" = c.item)
Planning time: 0.223 ms
Execution time: 526.203 ms


== Original ==
From: David Rowley <david.row...@2ndquadrant.com>
To: Benjamin Coutu <ben.co...@zeyos.com>
Date: Sun, 29 Oct 2017 12:46:42 +0100
Subject: Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

> 
> 
> On 30 October 2017 at 00:24, Benjamin Coutu <ben.co...@zeyos.com> wrote:
> >   ->  Index Scan using "PK_items_ID" on items a  (cost=0.42..1.05 rows=1 
> > width=332) (actual time=0.003..0.003 rows=1 loops=15521 total=46.563)
> 
> I've never seen EXPLAIN output like that before.
> 
> Is this some modified version of PostgreSQL?
>



-- 
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] Cheaper subquery scan not considered unless offset 0

[David Rowley]

On 30 October 2017 at 00:24, Benjamin Coutu  wrote:
>   ->  Index Scan using "PK_items_ID" on items a  (cost=0.42..1.05 rows=1 
> width=332) (actual time=0.003..0.003 rows=1 loops=15521 total=46.563)

I've never seen EXPLAIN output like that before.

Is this some modified version of PostgreSQL?

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] WAL still kept in pg_xlog even long after heavy workload is done

[MichaelDBA]

To get the values right, you have to consider the "unit" column in 
pg_settings.  On mine, it is 16M for both min and max wal size.  So it 
would be
1024 x 1024 x 16 x  (pg_settings.min_wal_size or 
pg_settings.max_wal_size)


The result of this formula should be close to what you specified in 
postgresql.conf.


So modify your SQL a bit:
psql -c "select name, setting, unit from pg_settings where name like 
'%wal_size';"


Regards,
Michael Vitale


Stefan Petrea 
Friday, October 27, 2017 7:28 AM
Hello,

We're encountering some problems with WAL growth in production with
PostgreSQL 9.6.3 and 9.6.2. From what I know a WAL file can either be
recycled(and would be reused) or deleted.
We'd like to have better control over the amount of WAL that is kept 
around.

There were a few occasions where we had to resize partitions because
pg_xlog grew as much as it did.

According to the docs [1] there are some parameters in GUC 
(postgresql.conf) about this.

The parameters I've been able to identify are the following:

* wal_keep_segments
* max_wal_size
* min_wal_size

Our WAL grows a lot around the time of our product upgrades (that is,
when we upgrade to a new version of our database, so not a Pg upgrade,
just a newer version of our db schema, plpgsql code etc).
As part of this upgrade, we add new columns or have some large UPDATEs
on tables as big as 300M (but in one case we also have one with 1.5B 
rows).


I am seeing the following int he docs [3]

min_wal_size (integer)
As long as WAL disk usage stays below this setting, old WAL files are
always recycled for future use at a checkpoint, rather than removed.
This can be used to ensure that enough WAL space is reserved to handle
spikes in WAL usage, for example when running large batch jobs. The 
default

is 80 MB. This parameter can only be set in the postgresql.conf file or
on the server command line.

This sounds very familiar because, that's essentially what we're 
doing. There
are some large jobs that cause a lot of workload and changes and 
generate a lot of WAL.


So far, the way I interpret this is min_wal_size is the amount of WAL
recycled (that is kept around to be reused) and max_wal_size is the
total amount of WAL allowed to be kept on disk.

I would also like to interpret the default values of min_wal_size and 
max_wal_size.

So if I run the following query:

psql -c "select name, setting from pg_settings where name like 
'%wal_size';"


I get the following:

max_wal_size|2097152
min_wal_size|1048576

Do these two values look ok?

Both these values were generated by pgtune [4], but it seems like pgtune
thinks they're expressed by default in KB.
Looking at the PostgreSQL code, it seems to me that these two are
expressed in MB, at least that's what I understand when I see
GUC_UNIT_MB in the source code [6].

So maybe the pgtune fork we're using has a bug in the sense that it
produces an incorrect value for those two parameters? (should be in MB
but is expressed in KB, therefore much higher than what it should be).

Another question is, how can I use any of the checkpoint settings
to control the WAL that is kept around?

* checkpoint_timeout
* checkpoint_completion_target
* checkpoint_flush_after
* checkpoint_warning

=

I actually tried something with these settings on a test environment.
I've used the following settings:

checkpoint_timeout = 40s
min_wal_size = 600MB
max_wal_size = 900MB

Then I've created a db named x1 and ran this on it four or five times.

pgbench -i -s 70 x1

The pg_xlog directory grew to 2.2G and after a few minutes, it 
decreased to 2.0G

After about 40 minutes it decreased to 1.4G and it's not going any lower.
I was expecting pg_xlog's size to be 600MB after the first WAL removal 
had run.
Should I expect that the size will eventually drop to 600MB or will it 
just sit there at 1.4G?


=

Other thoughts:

I have looked a bit at Pg internals too, I'm seeing four functions
there that are responsible for removing WAL: XLogArchiveIsReady,
RemoveXlogFile, RemoveOldXlogFiles, XLOGfileslop.
All of these belong to /src/backend/access/transam/xlog.c

The only place in the code that seems to take a decision about how much
WAL to recycle and how much to remove is the function XLOGfileslop [2].

It seems like XLOGfileslop is an estimate for the number of WAL to keep
around(recycled WAL). Both max_wal_size and min_wal_size are used inside
XLOGfileslop.

As far as checkpoint_* GUC settings go, they seem to be involved as well.
So far, the only thing I know about checkpoints is that between
checkpoints, many WAL are created. The amount of WAL between checkpoints
can vary. I don't have a good understanding about the interplay between
checkpoints and WAL.


I'd be grateful for any thoughts on how to improve this, and better 
control

the amount of WAL kept in pg_xlog.

Thank you,
Stefan

[1] https://www.postgresql.org/docs/9.6/static/wal-configuration.html
[2] 

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

[Justin Pryzby]

On Tue, Oct 24, 2017 at 04:15:59PM +0200, johannes graën wrote:
> Hi Pavel, *,
> 
> you were right with ANALYZing the DB first. However, even after doing
> so, I frequently see Seq Scans where an index was used before. This
> usually cooccurs with parallelization and looked different before
> upgrading to 10. I can provide an example for 10 [1], but I cannot
> generate a query plan for 9.6 anymore.
> 
> Any ideas what makes the new version more seqscanny?

Is it because max_parallel_workers_per_gather now defaults to 2 ?

BTW, I would tentatively expect a change in default to be documented in the
release notes but can't see that it's.
77cd477c4ba885cfa1ba67beaa82e06f2e182b85

Justin


-- 
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 drop after upgrade (9.6 > 10)

[Alvaro Herrera]

johannes graën wrote:
> Hi Pavel, *,
> 
> you were right with ANALYZing the DB first. However, even after doing
> so, I frequently see Seq Scans where an index was used before. This
> usually cooccurs with parallelization and looked different before
> upgrading to 10. I can provide an example for 10 [1], but I cannot
> generate a query plan for 9.6 anymore.

https://wiki.postgresql.org/wiki/Slow_Query_Questions

-- 
Álvaro Herrerahttps://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] performance drop after upgrade (9.6 > 10)

[Johannes Graën]

On 2017-10-24 17:18, Justin Pryzby wrote:
> You could (re)install PG96 alongside PG10 and run a copy of the DB (even from
> your homedir, or on a difference server) and pg_dump |pg_restore the relevant
> tables (just be sure to specify the alternate host/port/user/etc as needed for
> the restore invocation).

I considered that but it is far too expensive just for getting the old
query plan. The database is more than 1 TB big and replaying it from a
dump to another server took us several days, primarily due to the heavy
use of materialized views that are calculated over all rows of some
large tables. As long as there is no safe pg_downgrade --link I'd rather
keep trying to improve query performance on the current version.


-- 
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] postgresql tuning with perf

[legrand legrand]

Once again you are speaking about edb port of postgresql. The edb pl sql code
is not public. This is not the good place to get support: please ask your
edb contract manager.
If you want support hère: please rewrite your oracle proc in pl pqsql, share
that code and commit strategy ... Postgres doesn't support commit in pl ...
This is a big difference



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html


-- 
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 drop after upgrade (9.6 > 10)

[Justin Pryzby]

On Tue, Oct 24, 2017 at 04:15:59PM +0200, johannes graën wrote:
> upgrading to 10. I can provide an example for 10 [1], but I cannot
> generate a query plan for 9.6 anymore.

You could (re)install PG96 alongside PG10 and run a copy of the DB (even from
your homedir, or on a difference server) and pg_dump |pg_restore the relevant
tables (just be sure to specify the alternate host/port/user/etc as needed for
the restore invocation).

Justin


-- 
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] postgresql tuning with perf

[Purav Chovatia]

We record like this: perf record  -g -u enterprisedb
We report like this: perf report -g -i perf.data

Is this what you were looking for? Sorry, we are new to perf so we might be
sharing something different as compared to what you asked.

We already shared the SP code in the original post.

Thanks

On 24 October 2017 at 20:21, legrand legrand 
wrote:

> Please share how you monitor your perfs.
>
> At less duration for each plpgsql proc / oracle proc.
> Please share your plpgsql code, and commit strategy.
>
> (for support with edb please check with your contract manager)
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
> --
> 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] postgresql tuning with perf

[legrand legrand]

Please share how you monitor your perfs.

At less duration for each plpgsql proc / oracle proc.
Please share your plpgsql code, and commit strategy.

(for support with edb please check with your contract manager)



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html


-- 
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 drop after upgrade (9.6 > 10)

[johannes graën]

Hi Pavel, *,

you were right with ANALYZing the DB first. However, even after doing
so, I frequently see Seq Scans where an index was used before. This
usually cooccurs with parallelization and looked different before
upgrading to 10. I can provide an example for 10 [1], but I cannot
generate a query plan for 9.6 anymore.

Any ideas what makes the new version more seqscanny?


[1] https://explain.depesz.com/s/gXD3


-- 
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] postgresql tuning with perf

[Purav Chovatia]

Hi Pascal,

Do you mean the sample program that acts as the application, do you want me
to share that? I can do that, but I guess my post will get blocked.

Yes, c1 is the PK. Pls see below:
bmdb=# desc dept_new
  Table "public.dept_new"
 Column | Type  | Modifiers
+---+---
 c1 | numeric(10,0) | not null
 c2 | numeric(10,0) |
.
.
.
.
.
 c205   | numeric(10,0) |
Indexes:
"dept_new_pkey" PRIMARY KEY, btree (c1)

bmdb=#

We dont analyze after loading the table. But I guess thats required only if
the query plan is in doubt, lets say its doing a full table scan or alike,
isnt it? That is not the case. The query is using PK index but it just
seems to be slow.

Thanks

On 24 October 2017 at 01:59, legrand legrand 
wrote:

> Hi,
> could you providence the code used with PG ?
> Has table dept_new an index/pk on c1 ?
> Do you analyze this table after loading it ?
>
> Regards
> PAscal
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
> --
> 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] postgresql tuning with perf

[Purav Chovatia]

The language used for stored procedures is EDBSPL. Even if we dont use
EDBSPL, and instead use PLPgPSQL, the performance is still the same.

Thanks

On 24 October 2017 at 03:29, Steve Atkins  wrote:

>
> > On Oct 23, 2017, at 12:19 PM, Purav Chovatia  wrote:
> >
> > Hello Experts,
> >
> > We are trying to tune our postgresql DB using perf. We are running a C
> program that connects to postgres DB and calls very simple StoredProcs, one
> each for SELECT, INSERT & UPDATE.
> >
> > The SPs are very simple.
> > SELECT_SP:
> > CREATE OR REPLACE PROCEDURE query_dept_new(p1 IN numeric, p2 OUT
> numeric,p3 OUT numeric,...,p205 OUT numeric) AS
> > BEGIN
> > SELECT c2,c3,..,c205
> > INTO p2,p3,...,p205
> > FROM dept_new
> > WHERE c1 = p1;
> > END;
>
> Perhaps I'm confused, but I didn't think PostgreSQL had stored procedures.
> If the code you're actually running looks like this then I don't think
> you're using PostgreSQL.
>
> Cheers,
>   Steve
>
> --
> 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] postgresql tuning with perf

[Steve Atkins]

> On Oct 23, 2017, at 12:19 PM, Purav Chovatia  wrote:
> 
> Hello Experts,
> 
> We are trying to tune our postgresql DB using perf. We are running a C 
> program that connects to postgres DB and calls very simple StoredProcs, one 
> each for SELECT, INSERT & UPDATE. 
> 
> The SPs are very simple. 
> SELECT_SP:
> CREATE OR REPLACE PROCEDURE query_dept_new(p1 IN numeric, p2 OUT numeric,p3 
> OUT numeric,...,p205 OUT numeric) AS
> BEGIN
> SELECT c2,c3,..,c205
> INTO p2,p3,...,p205
> FROM dept_new
> WHERE c1 = p1;
> END;

Perhaps I'm confused, but I didn't think PostgreSQL had stored procedures. If 
the code you're actually running looks like this then I don't think you're 
using PostgreSQL.

Cheers,
  Steve

-- 
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] postgresql tuning with perf

[Tomas Vondra]

On 10/23/2017 09:19 PM, Purav Chovatia wrote:
> Hello Experts,
> 
> We are trying to tune our postgresql DB using perf.

Can you share some of the perf reports, then?

> We are running a C program that connects to postgres DB and calls
> very simple StoredProcs, one each for SELECT, INSERT & UPDATE.
> 
> The SPs are very simple. 
> *SELECT_SP*:
> CREATE OR REPLACE PROCEDURE query_dept_new(p1 IN numeric, p2 OUT
> numeric,p3 OUT numeric,...,p205 OUT numeric) AS
> BEGIN
>     SELECT c2,c3,..,c205
>         INTO p2,p3,...,p205
>         FROM dept_new
>         WHERE c1 = p1;
> END;
> 
> *UPDATE_SP*:
> CREATE OR REPLACE PROCEDURE query_dept_update(p1 IN numeric, p2 IN
> numeric,,p205 IN numeric) AS
> BEGIN
>     update dept_new set  c2 = p2,c3 = p3,.,c205 = p205 
>         WHERE c1 = p1;
> commit;
> END;
> 
> *INSERT_SP*:
> CREATE OR REPLACE PROCEDURE query_dept_insert(p1 IN numeric, p2 IN
> numeric,.,p205 IN numeric) AS
> BEGIN
> insert into dept_new values(p1,p2,.,p205);
> commit;
> END;
> 
> As shown above, its all on a single table. Before every test, the table
> is truncated and loaded with 1m rows. WAL is on a separate disk.
> 

It'd be nice if you could share more details about the structure of the
table, hardware and observed metrics (throughput, ...). Otherwise we
can't try reproducing it, for example.

> Its about 3x slower as compared to Oracle and major events are WAL
> related. With fsync=off or sync_commit=off it gets 10% better but still
> far from Oracle. Vacuuming the table does not help. Checkpoint too is
> not an issue. 

So how do you know the major events are WAL related? Can you share how
you measure that and the measurements?

> 
> Since we dont see any other way to find out what is slowing it down, we
> gathered data using the perf tool. Can somebody pls help on how do we go
> about reading the perf report.

Well, that's hard to do when you haven't shared the report.

regards

-- 
Tomas Vondra  http://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] postgresql tuning with perf

[legrand legrand]

Hi,
could you providence the code used with PG ?
Has table dept_new an index/pk on c1 ?
Do you analyze this table after loading it ?

Regards
PAscal



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html


-- 
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] Low priority batch insert

[Michael Paquier]

On Fri, Oct 20, 2017 at 1:10 AM, Jean Baro  wrote:
> That's my first question in this mailing list! :)

Welcome!

> Is it possible (node.js connecting to PG 9.6 on RDS) to set a lower priority
> to a connection so that that particular process (BATCH INSERT) would have a
> low impact on other running processes on PG, like live queries and single
> inserts/updates?
>
> Is that a good idea? Is this feasible with Node.js + PG?

The server could be changed so as backend processes use setpriority
and getpriority using a GUC parameter, and you could leverage priority
of processes using that. The good news is that this can be done as a
module, see an example from Fujii Masao's pg_cheat_funcs that caught
my attention actually yesterday:
https://github.com/MasaoFujii/pg_cheat_funcs/commit/a39ec1549e2af72bf101da5075c4e12d079f7c5b
The bad news is that you are on RDS, so vendor locking is preventing
you from loading any custom modules.
-- 
Michael


-- 
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] memory allocation

[Laurenz Albe]

nijam J wrote:
> our server is getting too slow again and again

Use "vmstat 1" and "iostat -mNx 1" to see if you are
running out of memory, CPU capacity or I/O bandwith.

Figure out if the slowness is due to slow queries or
an overloaded system.

Yours,
Laurenz Albe


-- 
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] Row level security policy policy versus SQL constraints. Any performance difference?

[Joe Carlson]

Thanks for your suggestions.

I had pretty much given up on this idea. At first, I had thought there 
would only be 2 or 3 different constraint cases to consider. I had 
thought of using distinct credentials for my connection and using RLS to 
give different cuts on the same table. The different policies could be 
established in advance and never touched.


But then it became clear that I actually would need a very large number 
of different restrictions on the tables - too many to create in advance. 
At this point it's easiest to apply constraints on each select rather 
than apply a policy every time.


Thanks,

Joe

On 10/17/2017 03:06 PM, Tom Lane wrote:

Tomas Vondra  writes:

On 10/17/2017 10:44 PM, Joe Carlson wrote:

What I was wondering is what is the performance differences between a
row level security implementation:
...
and an implementation where I add on the constraints as part of each
select statement:

The main point of the RLS is enforcing an order in which the conditions
are evaluated.

Yeah.  Because of that, I would *not* recommend RLS if you can equally
well stick the equivalent conditions into your queries.  There is way
too much risk of taking a serious performance hit due to a bad plan.

An alternative you might consider, if simplifying the input queries
is useful, is to put the fixed conditions into a view and query the
view instead.  That way there's not an enforced evaluation order.

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

Re: [PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?

[Tom Lane]

Tomas Vondra  writes:
> On 10/17/2017 10:44 PM, Joe Carlson wrote:
>> What I was wondering is what is the performance differences between a
>> row level security implementation:
>> ...
>> and an implementation where I add on the constraints as part of each
>> select statement:

> The main point of the RLS is enforcing an order in which the conditions
> are evaluated.

Yeah.  Because of that, I would *not* recommend RLS if you can equally
well stick the equivalent conditions into your queries.  There is way
too much risk of taking a serious performance hit due to a bad plan.

An alternative you might consider, if simplifying the input queries
is useful, is to put the fixed conditions into a view and query the
view instead.  That way there's not an enforced evaluation order.

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

Re: [PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?

[Tomas Vondra]

Hi,

On 10/17/2017 10:44 PM, Joe Carlson wrote:
> Hello.
> 
> I have not used row level security policies in the past but am
> considering using them for a project in which I would like to restrict
> the set returned in a query based on specific fields. This is more as a
> convenience issue (for me) rather than a security issue.
> 
> What I was wondering is what is the performance differences between a
> row level security implementation:
> 
> CREATE POLICY  ON  TO  USING
> (=ANY());
> 
> DROP POLICY 
> 
> and an implementation where I add on the constraints as part of each
> select statement:
> 
> SELECT  FROM  WHERE  AND
> =ANY()
> 
> In my (admittedly small) number of EXPLAINs I've looked at, it appears
> that the policy logic is added to the SELECT statement as a constraint.
> So I would not expect any fundamental performance difference in the 2
> different forms.
> 
> Is this true? Or is there some extra behind-the-scenes things to be
> aware of? Can there be excessive overhead from the CREATE/DROP POLICY
> statements?
> 

The main point of the RLS is enforcing an order in which the conditions
are evaluated. That is, the "security" quals (coming from RLS policies)
have to be evaluated first, before any quals that might leak information
about the values (imagine a custom PL/pgSQL function inserting the data
somewhere, or perhaps just printing debug messages).

(Many built-in operators are however exempt from that, as we consider
them leak-proof. This allows us to use non-RLS conditions for index
scans etc. which might be impossible otherwise)

Otherwise yes - it's pretty much the same as if you combine the
conditions using AND. It's "just" much more convenient approach.

regards

-- 
Tomas Vondra  http://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] 99% time spent in WAL wait events

[Purav Chovatia]

Kindly ignore this post. It was an oversight - the wait times are in
millisec and hence even if we manage to reduce these waits to 0, we will
gain only 1000 msec of savings during a workload of 40min.

Regards

On 16 Oct 2017 7:04 pm, "Purav Chovatia"  wrote:

Hello,

We are running workload on a EDB Postgres Advanced Server 9.6 and we see
that 99% of the time is spent on WAL wait events:







*System Wait Information WAIT NAME COUNT WAIT TIME % WAIT
---
wal flush 564552 298.789464 41.67 wal write 521514 211.601124 29.51 wal
file sync 521546 205.519643 28.66*
Disk IO performance is not an issue and WAL is on a dedicated disk.

Can somebody pls suggest if there is any possibility to improve this & how?

We already tried wal_buffers=96m, wal_sync_method=open_sync/open_datasync,
checkpoint_completion_target=0.9 but none of those helped.

System has 32GB RAM and shared_buffers=8GB. All transactions are happening
on a single table which has about 1.5m records and the table size is 1.7GB
with just one PK index.

Many Thanks

Regards

Re: [PERFORM] Stored Procedure Performance

[phb07]

Le 11/10/2017 à 16:11, Purav Chovatia a écrit :

Thanks.

We looked at pg_stat_statements and we see execution count & total 
time taken. But that still does not help me to identify why is it slow 
or what is taking time or where is the wait.


btw, does pg_stat_statements add considerable overhead? Coming from 
the Oracle world, we are very used to such execution stats, and hence 
we are planning to add this extension as a default to all our 
production deployments.


Its a single row select using PK, single row update using PK and a 
single row insert, so I dont see anything wrong with the code. So 
auto_explain would not add any value, I believe.


Basically, on an Oracle server, I would minimally look at 
statspack/awr report & OS stats (like cpu, iostat & memory) to start 
with. What should I look for in case of a Postgres server.
You could have a look at the PoWA extension 
(http://dalibo.github.io/powa/). It has the same purpose as AWR.




Thanks & Regards

On 3 October 2017 at 20:58, Pavel Stehule > wrote:




2017-10-03 17:17 GMT+02:00 Adam Brusselback
>:

There is also the option of pg_stat_statements:
https://www.postgresql.org/docs/current/static/pgstatstatements.html

and auto_explain:
https://www.postgresql.org/docs/current/static/auto-explain.html


These should help you identify what is slowing things down. 
There is no reason I could think of you should be seeing a 10x

slowdown between Postgres and Oracle, so you'll likely have to
just profile it to find out.


depends what is inside.

The max 10x slow down is possible if you are hit some unoptimized
cases. The times about 1ms - 10ms shows so procedure (code) can be
very sensitive to some impacts.

Regards

Pavel

Re: [PERFORM] Rowcount estimation changes based on from clause order

[Ants Aasma]

On Thu, Oct 12, 2017 at 11:50 PM, Tom Lane  wrote:
> Ants Aasma  writes:
>> I stumbled upon a severe row count underestimation that confusingly
>> went away when two inner joins in the from clause were reordered.
>
> Hm, looks more like an overestimate in this example, but anyway ...
>
>> Does anybody have any idea what is going on here?
>
> set_joinrel_size_estimates says
>
>  * Since there is more than one way to make a joinrel for more than two
>  * base relations, the results we get here could depend on which component
>  * rel pair is provided.  In theory we should get the same answers no matter
>  * which pair is provided; in practice, since the selectivity estimation
>  * routines don't handle all cases equally well, we might not.  But there's
>  * not much to be done about it.
>
> In this example I think the core of the issue is actually not so much
> bad selectivity estimates as rowcount roundoff error.
>
> If we first consider joining "small" with "big", we get an estimate of
> 2000 rows (which is dead on for what would happen if we just joined
> those).  Then we estimate the final result size as the join of that to
> "lookup".  The selectivity number for that step is somewhat hogwash but
> happens to yield a result that's not awful (8 rows).
>
> In the other case we first estimate the size of the join of "small" with
> the "lookup" subquery, and we get a rounded-off estimate of one row,
> whereas without the roundoff it would have been probably about 0.01.
> When that's joined to "big", we are computing one row times 1 million rows
> times a selectivity estimate that's about right for the "small.id =
> big.small_id" clause; but because the roundoff already inflated the first
> join's size so much, you end up with an inflated final result.
>
> This suggests that there might be some value in considering the
> sub-relations from largest to smallest, so that roundoff error
> in the earlier estimates is less likely to contaminate the final
> answer.  Not sure how expensive it would be to do that or what
> sort of instability it might introduce into plan choices.
>
> Whether that's got anything directly to do with your original problem is
> hard to say.  Joins to subqueries, which we normally lack any stats for,
> tend to produce pretty bogus selectivity numbers in themselves; so the
> original problem might've been more of that nature.

Thanks for pointing me in the correct direction. The original issue
was that values from lookup joined to ref_id and the subset filter in
the small table were almost perfectly correlated, which caused the
underestimate. In the second case this was hidden by the intermediate
clamping to 1, accidentally resulting in a more correct estimate.

I actually think that it might be better to consider relations from
smallest to largest. The reasoning being - a join cannot produce a
fraction of a row, it will either produce 0 or 1, and we should
probably plan for the case when it does return something.

Going even further, and I haven't looked at how feasible this is, but
I have run into several cases lately where cardinality underestimates
clamping to 1 result in catastrophically bad plans. Like a stack of
nested loops with unparameterized GroupAggregates and HashAggregates
as inner sides bad. It seems to me that row estimates should clamp to
something slightly larger than 1 unless it's provably going to be 1.

Regards,
Ants Aasma


-- 
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] Rowcount estimation changes based on from clause order

[Tom Lane]

Ants Aasma  writes:
> I stumbled upon a severe row count underestimation that confusingly
> went away when two inner joins in the from clause were reordered.

Hm, looks more like an overestimate in this example, but anyway ...

> Does anybody have any idea what is going on here?

set_joinrel_size_estimates says

 * Since there is more than one way to make a joinrel for more than two
 * base relations, the results we get here could depend on which component
 * rel pair is provided.  In theory we should get the same answers no matter
 * which pair is provided; in practice, since the selectivity estimation
 * routines don't handle all cases equally well, we might not.  But there's
 * not much to be done about it.

In this example I think the core of the issue is actually not so much
bad selectivity estimates as rowcount roundoff error.

If we first consider joining "small" with "big", we get an estimate of
2000 rows (which is dead on for what would happen if we just joined
those).  Then we estimate the final result size as the join of that to
"lookup".  The selectivity number for that step is somewhat hogwash but
happens to yield a result that's not awful (8 rows).

In the other case we first estimate the size of the join of "small" with
the "lookup" subquery, and we get a rounded-off estimate of one row,
whereas without the roundoff it would have been probably about 0.01.
When that's joined to "big", we are computing one row times 1 million rows
times a selectivity estimate that's about right for the "small.id =
big.small_id" clause; but because the roundoff already inflated the first
join's size so much, you end up with an inflated final result.

This suggests that there might be some value in considering the
sub-relations from largest to smallest, so that roundoff error
in the earlier estimates is less likely to contaminate the final
answer.  Not sure how expensive it would be to do that or what
sort of instability it might introduce into plan choices.

Whether that's got anything directly to do with your original problem is
hard to say.  Joins to subqueries, which we normally lack any stats for,
tend to produce pretty bogus selectivity numbers in themselves; so the
original problem might've been more of that nature.

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

Re: [PERFORM] synchronization between PostgreSQL and Oracle

[Laurenz Albe]

ROS Didier wrote:
>    I would like your advice  and recommendation about the 
> following infrastructure problem :
> What is the best way to optimize synchronization between an instance 
> PostgreSQL on Windows 7 workstation and an Oracle 11gR2 database on linux 
> RHEL  ?
> Here are more detailed explanations
> In our company we have people who collect data in a 9.6 postgresql instance 
> on their workstation that is disconnected from the internet.
> In the evening, they connect to the Internet and synchronize the collected 
> data to a remote 11gr2 Oracle database.
> What is the best performant way to do this ( Oracle_FDW ?, flat files ?, …)

If the synchronization is triggered from the workstation with
PostgreSQL on it, you can either use oracle_fdw or pg_dump/sql*loader
to transfer the data.

Using oracle_fdw is probably simpler, but it is not very performant
for bulk update operations.

If performance is the main objective, use export/import.

Yours,
Laurenz Albe



-- 
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] synchronization between PostgreSQL and Oracle

[Rick Otten]

On Thu, Oct 12, 2017 at 5:13 AM, ROS Didier  wrote:

> Hi
>
>I would like your advice  and recommendation about the
> following infrastructure problem :
>
> What is the best way to optimize synchronization between an instance
> PostgreSQL on Windows 7 workstation and an Oracle 11gR2 database on linux
> RHEL  ?
>
> Here are more detailed explanations
>
> In our company we have people who collect data in a 9.6 postgresql
> instance on their workstation that is disconnected from the internet.
>
> In the evening, they connect to the Internet and synchronize the collected
> data to a remote 11gr2 Oracle database.
>
> What is the best performant way to do this ( Oracle_FDW ?, flat files ?, …)
>
>
>
There are several ways to go about this, but for your use case I'd
recommend SymmetricDS -- http://symmetricds.org   (or for the commercial
version:  http://jumpmind.com)

SymmetricDS was originally designed to collect data from cash registers in
a vastly distributed set of small databases and aggregate those results
back into both regional and national data warehouses.  It also pushed data
the other way - when pricing was updated at corporate headquarters, the
data was pushed back into the cash registers.  It works with a wide variety
of database technologies, scales well, and has many synchronization
options.  It is also being used by some organizations these days to
synchronize small databases on IOS and Android devices with their parent
databases back at HQ.

I first used it to implement an Oracle to PostgreSQL data migration that
had to be done without down time.   I've used it successfully for real time
data pushes from MySQL and PG OLTP systems into an Oracle DataMart.   I
also used to use it for PostgreSQL bidirectional replication before other
tools became easier to use.  Because of its great flexibility, SymmetricDS
has a ton of knobs to turn and buttons and configuration options and may
take a bit to get it working optimally.   If you are short on time to
implement a solution, I'd suggest going with the commercial version.

Re: [PERFORM] blocking index creation

[Scott Marlowe]

Try the queries here to check locks:

https://wiki.postgresql.org/wiki/Lock_Monitoring

On Wed, Oct 11, 2017 at 7:35 PM, Neto pr  wrote:
> Dear,
> With alternative, I tested the creation using concurrency
> (CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN);
>
> from what I saw the index already appeared in the query result, because
> before this, the index did not even appear in the result, only the Lineitem
> table:
>
> SELECT
>   L.mode, c.relname, locktype, l.GRANTED, l.transactionid,
> virtualtransaction
> FROM pg_locks l, pg_class c
> where c.oid = l.relation
>
> screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg
>
> Now, I'm waiting to finish creating the index.
>
> 2017-10-11 19:54 GMT-03:00 Neto pr :
>>
>> Hello all,
>> I ran the query on PG_STAT_ACTIVITY table (Select * From
>> pg_stat_activity),  see the complete result in this worksheet of the link
>> below.
>>
>>
>> https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls
>>
>> The CREATE INDEX command line is identified with the orange background.
>> At this point 18 hours have passed and the creation of a single index has
>> not yet been completed.
>> I have verified that the command is Active status, but I do not know if
>> it's waiting for anything, can you help me analyze the attached output.
>>
>> Regards
>> Neto
>>
>> 2017-10-11 18:08 GMT-03:00 Tomas Vondra :
>>>
>>>
>>>
>>> On 10/11/2017 04:11 PM, Neto pr wrote:
>>> >
>>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe >> > >:
>>> >
>>> > Neto pr wrote:
>>> > > When creating index on table of approximately 10GB of data, the
>>> > DBMS hangs (I think),
>>> > > because even after waiting 10 hours there was no return of the
>>> > command.
>>> > > It happened by creating Hash indexes and B + tree indexes.
>>> > > However, for some columns, it was successfully (L_RETURNFLAG,
>>> > L_PARTKEY).
>>> >
>>> > > If someone has a hint how to speed up index creation so that it
>>> > completes successfully.
>>> >
>>> > Look if CREATE INDEX is running or waiting for a lock (check the
>>> > "pg_locks" table, see if the backend consumes CPU time).
>>> >
>>> >
>>> > In this moment now, there is an index being created in the Lineitem
>>> > table (+ - 10 Gb), and apparently it is locked, since it started 7
>>> > hours
>>> > ago.
>>> > I've looked at the pg_locks table and look at the result, it's with
>>> > "ShareLock" lock mode.
>>> > Is this blocking correct? or should it be another type?
>>> >
>>>
>>> Yes, CREATE INDEX acquire SHARE lock, see
>>>
>>>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>>>
>>> > Before creating the index, should I set the type of transaction lock?
>>> > What?
>>>
>>> Eeee? Not sure I understand. The command acquires all necessary locks
>>> automatically.
>>>
>>> >
>>> > ---
>>> > SELECT
>>> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
>>> > virtualtransaction
>>> > FROM   pg_locks l, pg_class   c
>>> > where  c.oid = l.relation
>>> >
>>> > -- RESULT
>>> > --
>>> > AccessShareLock   pg_class_tblspc_relfilenode_index   relation
>>> > TRUE
>>> > (null)3/71
>>> > AccessShareLock   pg_class_relname_nsp_index  relation
>>> > TRUE(null)  3/71
>>> > AccessShareLock   pg_class_oid_index  relationTRUE
>>> > (null)  3/71
>>> > AccessShareLock   pg_classrelationTRUE(null)
>>> > 3/71
>>> > AccessShareLock   pg_locksrelationTRUE(null)
>>> > 3/71
>>> > ShareLock lineitemrelationTRUE(null)  21/3769
>>> >
>>> >
>>>
>>> Well, we see something is holding a SHARE lock on the "lineitem" table,
>>> but we don't really know what the session is doing.
>>>
>>> There's a PID in the pg_locks table, you can use it to lookup the
>>> session in pg_stat_activity which includes the query (and also "state"
>>> column that will tell you if it's active or waiting for a lock.
>>>
>>> regards
>>>
>>> --
>>> Tomas Vondra  http://www.2ndQuadrant.com
>>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>>
>



-- 
To understand recursion, one must first understand recursion.


-- 
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] blocking index creation

[Neto pr]

Dear,
With alternative, I tested the creation using concurrency
(CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN);

from what I saw the index already appeared in the query result, because
before this, the index did not even appear in the result, only the Lineitem
table:

SELECT
  L.mode, c.relname, locktype, l.GRANTED, l.transactionid,
virtualtransaction
FROM pg_locks l, pg_class c
where c.oid = l.relation

screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg

Now, I'm waiting to finish creating the index.

2017-10-11 19:54 GMT-03:00 Neto pr :

> Hello all,
> I ran the query on PG_STAT_ACTIVITY table (Select * From
> pg_stat_activity),  see the complete result in this worksheet of the link
> below.
>
> https://sites.google.com/site/goissbr/img/Resultado_pg_stat_
> activity-create_index.xls
>
> The CREATE INDEX command line is identified with the orange background.
> At this point 18 hours have passed and the creation of a single index has
> not yet been completed.
> I have verified that the command is Active status, but I do not know if
> it's waiting for anything, can you help me analyze the attached output.
>
> Regards
> Neto
>
> 2017-10-11 18:08 GMT-03:00 Tomas Vondra :
>
>>
>>
>> On 10/11/2017 04:11 PM, Neto pr wrote:
>> >
>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe > > >:
>> >
>> > Neto pr wrote:
>> > > When creating index on table of approximately 10GB of data, the
>> DBMS hangs (I think),
>> > > because even after waiting 10 hours there was no return of the
>> command.
>> > > It happened by creating Hash indexes and B + tree indexes.
>> > > However, for some columns, it was successfully (L_RETURNFLAG,
>> L_PARTKEY).
>> >
>> > > If someone has a hint how to speed up index creation so that it
>> completes successfully.
>> >
>> > Look if CREATE INDEX is running or waiting for a lock (check the
>> > "pg_locks" table, see if the backend consumes CPU time).
>> >
>> >
>> > In this moment now, there is an index being created in the Lineitem
>> > table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
>> > ago.
>> > I've looked at the pg_locks table and look at the result, it's with
>> > "ShareLock" lock mode.
>> > Is this blocking correct? or should it be another type?
>> >
>>
>> Yes, CREATE INDEX acquire SHARE lock, see
>>
>>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>>
>> > Before creating the index, should I set the type of transaction lock?
>> What?
>>
>> Eeee? Not sure I understand. The command acquires all necessary locks
>> automatically.
>>
>> > 
>> ---
>> > SELECT
>> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
>> > virtualtransaction
>> > FROM   pg_locks l, pg_class   c
>> > where  c.oid = l.relation
>> >
>> > -- RESULT
>> > --
>> > AccessShareLock   pg_class_tblspc_relfilenode_index
>>  relationTRUE
>> > (null)3/71
>> > AccessShareLock   pg_class_relname_nsp_index  relation
>> TRUE(null)  3/71
>> > AccessShareLock   pg_class_oid_index  relationTRUE
>> (null)  3/71
>> > AccessShareLock   pg_classrelationTRUE(null)
>> 3/71
>> > AccessShareLock   pg_locksrelationTRUE(null)
>> 3/71
>> > ShareLock lineitemrelationTRUE(null)  21/3769
>> >
>> >
>>
>> Well, we see something is holding a SHARE lock on the "lineitem" table,
>> but we don't really know what the session is doing.
>>
>> There's a PID in the pg_locks table, you can use it to lookup the
>> session in pg_stat_activity which includes the query (and also "state"
>> column that will tell you if it's active or waiting for a lock.
>>
>> regards
>>
>> --
>> Tomas Vondra  http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>

Re: [PERFORM] blocking index creation

[Neto pr]

Hello all,
I ran the query on PG_STAT_ACTIVITY table (Select * From
pg_stat_activity),  see the complete result in this worksheet of the link
below.

https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls

The CREATE INDEX command line is identified with the orange background.
At this point 18 hours have passed and the creation of a single index has
not yet been completed.
I have verified that the command is Active status, but I do not know if
it's waiting for anything, can you help me analyze the attached output.

Regards
Neto

2017-10-11 18:08 GMT-03:00 Tomas Vondra :

>
>
> On 10/11/2017 04:11 PM, Neto pr wrote:
> >
> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe  > >:
> >
> > Neto pr wrote:
> > > When creating index on table of approximately 10GB of data, the
> DBMS hangs (I think),
> > > because even after waiting 10 hours there was no return of the
> command.
> > > It happened by creating Hash indexes and B + tree indexes.
> > > However, for some columns, it was successfully (L_RETURNFLAG,
> L_PARTKEY).
> >
> > > If someone has a hint how to speed up index creation so that it
> completes successfully.
> >
> > Look if CREATE INDEX is running or waiting for a lock (check the
> > "pg_locks" table, see if the backend consumes CPU time).
> >
> >
> > In this moment now, there is an index being created in the Lineitem
> > table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
> > ago.
> > I've looked at the pg_locks table and look at the result, it's with
> > "ShareLock" lock mode.
> > Is this blocking correct? or should it be another type?
> >
>
> Yes, CREATE INDEX acquire SHARE lock, see
>
>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>
> > Before creating the index, should I set the type of transaction lock?
> What?
>
> Eeee? Not sure I understand. The command acquires all necessary locks
> automatically.
>
> > 
> ---
> > SELECT
> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
> > virtualtransaction
> > FROM   pg_locks l, pg_class   c
> > where  c.oid = l.relation
> >
> > -- RESULT
> > --
> > AccessShareLock   pg_class_tblspc_relfilenode_index   relation
>   TRUE
> > (null)3/71
> > AccessShareLock   pg_class_relname_nsp_index  relation
> TRUE(null)  3/71
> > AccessShareLock   pg_class_oid_index  relationTRUE
> (null)  3/71
> > AccessShareLock   pg_classrelationTRUE(null)
> 3/71
> > AccessShareLock   pg_locksrelationTRUE(null)
> 3/71
> > ShareLock lineitemrelationTRUE(null)  21/3769
> >
> >
>
> Well, we see something is holding a SHARE lock on the "lineitem" table,
> but we don't really know what the session is doing.
>
> There's a PID in the pg_locks table, you can use it to lookup the
> session in pg_stat_activity which includes the query (and also "state"
> column that will tell you if it's active or waiting for a lock.
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [PERFORM] blocking index creation

[Tomas Vondra]

On 10/11/2017 04:11 PM, Neto pr wrote:
> 
> 2017-10-11 10:46 GMT-03:00 Laurenz Albe  >:
> 
> Neto pr wrote:
> > When creating index on table of approximately 10GB of data, the DBMS 
> hangs (I think),
> > because even after waiting 10 hours there was no return of the command.
> > It happened by creating Hash indexes and B + tree indexes.
> > However, for some columns, it was successfully (L_RETURNFLAG, 
> L_PARTKEY).
> 
> > If someone has a hint how to speed up index creation so that it 
> completes successfully.
> 
> Look if CREATE INDEX is running or waiting for a lock (check the
> "pg_locks" table, see if the backend consumes CPU time).
> 
> 
> In this moment now, there is an index being created in the Lineitem
> table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
> ago.
> I've looked at the pg_locks table and look at the result, it's with
> "ShareLock" lock mode.
> Is this blocking correct? or should it be another type?
> 

Yes, CREATE INDEX acquire SHARE lock, see

   https://www.postgresql.org/docs/9.1/static/explicit-locking.html

> Before creating the index, should I set the type of transaction lock? What?

Eeee? Not sure I understand. The command acquires all necessary locks
automatically.

> ---
> SELECT
>   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
> virtualtransaction
> FROM   pg_locks l, pg_class   c
> where  c.oid = l.relation
> 
> -- RESULT
> --
> AccessShareLock   pg_class_tblspc_relfilenode_index   relation
> TRUE
> (null)3/71
> AccessShareLock   pg_class_relname_nsp_index  relationTRUE
> (null)  3/71
> AccessShareLock   pg_class_oid_index  relationTRUE(null)  
> 3/71
> AccessShareLock   pg_classrelationTRUE(null)  3/71
> AccessShareLock   pg_locksrelationTRUE(null)  3/71
> ShareLock lineitemrelationTRUE(null)  21/3769
> 
>  

Well, we see something is holding a SHARE lock on the "lineitem" table,
but we don't really know what the session is doing.

There's a PID in the pg_locks table, you can use it to lookup the
session in pg_stat_activity which includes the query (and also "state"
column that will tell you if it's active or waiting for a lock.

regards

-- 
Tomas Vondra  http://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] Stored Procedure Performance

[Pavel Stehule]

2017-10-11 18:52 GMT+02:00 Purav Chovatia :

> Yes, there is some code to catch exceptions like unique constraint
> violation and no data found. Do you suggest we trying by commenting that
> part? btw, the dataset is a controlled one, so what I can confirm is we are
> not hitting any exceptions.
>

If it is possible, don't do it in cycle, or use exception handling only
when it is necessary, not from pleasure.

Regards

Pavel


> Thanks
>
> On 11 October 2017 at 22:07, Adam Brusselback 
> wrote:
>
>> Is there any error handling in there?  I remember seeing performance
>> issues if you put in any code to catch exceptions.
>>
>
>

Re: [PERFORM] Stored Procedure Performance

[Adam Brusselback]

> Yes, there is some code to catch exceptions like unique constraint violation 
> and no data found. Do you suggest we trying by commenting that part?

That is likely it.  Comment that out and test.
If you still need to handle a unique violation, see if you can instead
use the ON CONFLICT clause on the INSERT.


-- 
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] Stored Procedure Performance

[Purav Chovatia]

Yes, there is some code to catch exceptions like unique constraint
violation and no data found. Do you suggest we trying by commenting that
part? btw, the dataset is a controlled one, so what I can confirm is we are
not hitting any exceptions.

Thanks

On 11 October 2017 at 22:07, Adam Brusselback 
wrote:

> Is there any error handling in there?  I remember seeing performance
> issues if you put in any code to catch exceptions.
>

Re: [PERFORM] Stored Procedure Performance

[Adam Brusselback]

Is there any error handling in there?  I remember seeing performance
issues if you put in any code to catch exceptions.


-- 
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] Stored Procedure Performance

[Purav Chovatia]

Thanks Pavel. Our SPs are not doing any mathematical calculations. Its
mostly if-else, so I would expect good performance.

On 11 October 2017 at 19:50, Pavel Stehule  wrote:

>
>
> 2017-10-11 15:59 GMT+02:00 Purav Chovatia :
>
>> Thanks Laurenz, am having a look at perf.
>>
>> Can you pls help understand what exactly do you mean when you say "PL/pgSQL
>> is not optimized for performance like PL/SQL". Do you mean to indicate that
>> app firing queries/DMLs directly would be a better option as compared to
>> putting those in Stored Procs?
>>
>
> PL/pgSQL is perfect glue for SQL. SQL queries has same speed without
> dependency on environment that executed it.
>
> This sentence mean, so PLpgSQL is not designed for intensive mathematics
> calculation.  PL/SQL is self govering environment ... it has own data
> types, it has own implementation of logical and mathematics operators.
> PLpgSQL is layer over SQL engine - and has not own types, has not own
> operators. Any expression is translated to SQL and then is interpreted by
> SQL expression interpret. Maybe in next few years there will be a JIT
> compiler. But it is not now. This is current bottleneck of PLpgSQL. If your
> PL code is glue for SQL queries (implementation of some business
> processes), then PLpgSQL is fast enough. If you try to calculate numeric
> integration or derivation of some functions, then PLpgSQL is slow. It is
> not too slow - the speed is comparable with PHP, but it is significantly
> slower than C language.
>
> PostgreSQL has perfect C API - so intensive numeric calculations are
> usually implemented as C extension.
>
> Regards
>
> Pavel
>
>
>>
>> Regards
>>
>> On 3 October 2017 at 20:24, Laurenz Albe 
>> wrote:
>>
>>> Purav Chovatia wrote:
>>> > I come from Oracle world and we are porting all our applications to
>>> postgresql.
>>> >
>>> > The application calls 2 stored procs,
>>> > - first one does a few selects and then an insert
>>> > - second one does an update
>>> >
>>> > The main table on which the insert and the update happens is truncated
>>> before every performance test.
>>> >
>>> > We are doing about 100 executions of both of these stored proc per
>>> second.
>>> >
>>> > In Oracle each exec takes about 1millisec whereas in postgres its
>>> taking 10millisec and that eventually leads to a queue build up in our
>>> application.
>>> >
>>> > All indices are in place. The select, insert & update are all single
>>> row operations and use the PK.
>>> >
>>> > It does not look like any query taking longer but something else. How
>>> can I check where is the time being spent? There are no IO waits, so its
>>> all on the CPU.
>>>
>>> You could profile the PostgreSQL server while it is executing the
>>> workload,
>>> see for example https://wiki.postgresql.org/wiki/Profiling_with_perf
>>>
>>> That way you could see where the time is spent.
>>>
>>> PL/pgSQL is not optimized for performance like PL/SQL.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>>
>>
>

Re: [PERFORM] Stored Procedure Performance

[Pavel Stehule]

2017-10-11 15:59 GMT+02:00 Purav Chovatia :

> Thanks Laurenz, am having a look at perf.
>
> Can you pls help understand what exactly do you mean when you say "PL/pgSQL
> is not optimized for performance like PL/SQL". Do you mean to indicate that
> app firing queries/DMLs directly would be a better option as compared to
> putting those in Stored Procs?
>

PL/pgSQL is perfect glue for SQL. SQL queries has same speed without
dependency on environment that executed it.

This sentence mean, so PLpgSQL is not designed for intensive mathematics
calculation.  PL/SQL is self govering environment ... it has own data
types, it has own implementation of logical and mathematics operators.
PLpgSQL is layer over SQL engine - and has not own types, has not own
operators. Any expression is translated to SQL and then is interpreted by
SQL expression interpret. Maybe in next few years there will be a JIT
compiler. But it is not now. This is current bottleneck of PLpgSQL. If your
PL code is glue for SQL queries (implementation of some business
processes), then PLpgSQL is fast enough. If you try to calculate numeric
integration or derivation of some functions, then PLpgSQL is slow. It is
not too slow - the speed is comparable with PHP, but it is significantly
slower than C language.

PostgreSQL has perfect C API - so intensive numeric calculations are
usually implemented as C extension.

Regards

Pavel


>
> Regards
>
> On 3 October 2017 at 20:24, Laurenz Albe  wrote:
>
>> Purav Chovatia wrote:
>> > I come from Oracle world and we are porting all our applications to
>> postgresql.
>> >
>> > The application calls 2 stored procs,
>> > - first one does a few selects and then an insert
>> > - second one does an update
>> >
>> > The main table on which the insert and the update happens is truncated
>> before every performance test.
>> >
>> > We are doing about 100 executions of both of these stored proc per
>> second.
>> >
>> > In Oracle each exec takes about 1millisec whereas in postgres its
>> taking 10millisec and that eventually leads to a queue build up in our
>> application.
>> >
>> > All indices are in place. The select, insert & update are all single
>> row operations and use the PK.
>> >
>> > It does not look like any query taking longer but something else. How
>> can I check where is the time being spent? There are no IO waits, so its
>> all on the CPU.
>>
>> You could profile the PostgreSQL server while it is executing the
>> workload,
>> see for example https://wiki.postgresql.org/wiki/Profiling_with_perf
>>
>> That way you could see where the time is spent.
>>
>> PL/pgSQL is not optimized for performance like PL/SQL.
>>
>> Yours,
>> Laurenz Albe
>>
>
>

Re: [PERFORM] blocking index creation

[Neto pr]

2017-10-11 10:46 GMT-03:00 Laurenz Albe :

> Neto pr wrote:
> > When creating index on table of approximately 10GB of data, the DBMS
> hangs (I think),
> > because even after waiting 10 hours there was no return of the command.
> > It happened by creating Hash indexes and B + tree indexes.
> > However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).
>
> > If someone has a hint how to speed up index creation so that it
> completes successfully.
>
> Look if CREATE INDEX is running or waiting for a lock (check the
> "pg_locks" table, see if the backend consumes CPU time).
>
>
In this moment now, there is an index being created in the Lineitem table
(+ - 10 Gb), and apparently it is locked, since it started 7 hours ago.
I've looked at the pg_locks table and look at the result, it's with
"ShareLock" lock mode.
Is this blocking correct? or should it be another type?

Before creating the index, should I set the type of transaction lock? What?
---
SELECT
  L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
virtualtransaction
FROM   pg_locks l, pg_class   c
where  c.oid = l.relation

-- RESULT
--
AccessShareLock pg_class_tblspc_relfilenode_index relation TRUE (null) 3/71
AccessShareLock pg_class_relname_nsp_index relation TRUE (null) 3/71
AccessShareLock pg_class_oid_index relation TRUE (null) 3/71
AccessShareLock pg_class relation TRUE (null) 3/71
AccessShareLock pg_locks relation TRUE (null) 3/71
ShareLock lineitem relation TRUE (null) 21/3769

> Maybe there is a long-running transaction that blocks the
> ACCESS EXCLUSIVE lock required.  It could also be a prepared
> transaction.
>
> Yours,
> Laurenz Albe
>

Best Regards
Neto

Re: [PERFORM] Stored Procedure Performance

[Purav Chovatia]

Thanks.

We looked at pg_stat_statements and we see execution count & total time
taken. But that still does not help me to identify why is it slow or what
is taking time or where is the wait.

btw, does pg_stat_statements add considerable overhead? Coming from the
Oracle world, we are very used to such execution stats, and hence we are
planning to add this extension as a default to all our production
deployments.

Its a single row select using PK, single row update using PK and a single
row insert, so I dont see anything wrong with the code. So auto_explain
would not add any value, I believe.

Basically, on an Oracle server, I would minimally look at statspack/awr
report & OS stats (like cpu, iostat & memory) to start with. What should I
look for in case of a Postgres server.

Thanks & Regards

On 3 October 2017 at 20:58, Pavel Stehule  wrote:

>
>
> 2017-10-03 17:17 GMT+02:00 Adam Brusselback :
>
>> There is also the option of pg_stat_statements: https://ww
>> w.postgresql.org/docs/current/static/pgstatstatements.html and
>> auto_explain: https://www.postgresql.org/docs/current/static
>> /auto-explain.html
>>
>> These should help you identify what is slowing things down.  There is no
>> reason I could think of you should be seeing a 10x slowdown between
>> Postgres and Oracle, so you'll likely have to just profile it to find out.
>>
>
> depends what is inside.
>
> The max 10x slow down is possible if you are hit some unoptimized cases.
> The times about 1ms - 10ms shows so procedure (code) can be very sensitive
> to some impacts.
>
> Regards
>
> Pavel
>
>

Re: [PERFORM] Stored Procedure Performance

[Purav Chovatia]

Thanks Laurenz, am having a look at perf.

Can you pls help understand what exactly do you mean when you say "PL/pgSQL
is not optimized for performance like PL/SQL". Do you mean to indicate that
app firing queries/DMLs directly would be a better option as compared to
putting those in Stored Procs?

Regards

On 3 October 2017 at 20:24, Laurenz Albe  wrote:

> Purav Chovatia wrote:
> > I come from Oracle world and we are porting all our applications to
> postgresql.
> >
> > The application calls 2 stored procs,
> > - first one does a few selects and then an insert
> > - second one does an update
> >
> > The main table on which the insert and the update happens is truncated
> before every performance test.
> >
> > We are doing about 100 executions of both of these stored proc per
> second.
> >
> > In Oracle each exec takes about 1millisec whereas in postgres its taking
> 10millisec and that eventually leads to a queue build up in our application.
> >
> > All indices are in place. The select, insert & update are all single row
> operations and use the PK.
> >
> > It does not look like any query taking longer but something else. How
> can I check where is the time being spent? There are no IO waits, so its
> all on the CPU.
>
> You could profile the PostgreSQL server while it is executing the
> workload,
> see for example https://wiki.postgresql.org/wiki/Profiling_with_perf
>
> That way you could see where the time is spent.
>
> PL/pgSQL is not optimized for performance like PL/SQL.
>
> Yours,
> Laurenz Albe
>

Re: [PERFORM] blocking index creation

[Laurenz Albe]

Neto pr wrote:
> When creating index on table of approximately 10GB of data, the DBMS hangs (I 
> think),
> because even after waiting 10 hours there was no return of the command.
> It happened by creating Hash indexes and B + tree indexes.
> However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).

> If someone has a hint how to speed up index creation so that it completes 
> successfully.

Look if CREATE INDEX is running or waiting for a lock (check the
"pg_locks" table, see if the backend consumes CPU time).

Maybe there is a long-running transaction that blocks the
ACCESS EXCLUSIVE lock required.  It could also be a prepared
transaction.

Yours,
Laurenz Albe


-- 
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 drop after upgrade (9.6 > 10)

[johannes graën]

On Wed, Oct 11, 2017 at 1:11 PM, Pavel Stehule  wrote:
> have you fresh statistics? After upgrade is necessary to run ANALYZE command

Yes, that was missing indeed. I did ANALYZE but apparently on all
databases but this one. I could have guessed that
1,098,956,679,131,935,754,413,282,631,696,252,928 is not a reasonable
cost value.

Thanks, Pavel.

Best
  Johannes


-- 
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 drop after upgrade (9.6 > 10)

[Pavel Stehule]

2017-10-11 13:06 GMT+02:00 johannes graën :

> Hi,
>
> I wrote a query that joins several tables usually returning less than
> 1000 rows, groups them and generates a JSON object of the result. In
> 9.6 is was a question of milliseconds for that query to return the
> requested data. Now, after upgrading to 10, the query never returns -
> at least it hasn't returned in the last hour.
>
> To see what happens, I requested the query plan [1]. It looks complex
> and shows a lot of parallelization. I don't have the query plan from
> 9.6, but I remember it being considerably simpler.
>
> Can anyone have a guess what altered the performance here so
> dramatically? Is there a way to disable new parallelization features
> just for this query to see if it makes any difference?
>
>

have you fresh statistics? After upgrade is necessary to run ANALYZE command

Regards

Pavel


Best
>   Johannes
>
>
> [1] https://explain.depesz.com/s/xsPP
>
>
> --
> 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] Regression from 9.4-9.6

[Jim Nasby]

On 10/8/17 3:37 PM, Tom Lane wrote:

Jim Nasby  writes:

On 10/8/17 2:34 PM, Tom Lane wrote:

Why has this indexscan's cost estimate changed so much?



Great question... the only thing that sticks out is the coalesce(). Let
me see if an analyze with a higher stats target changes anything. FWIW,
the 9.6 database is copied from the 9.4 one once a week and then
pg_upgraded. I'm pretty sure an ANALYZE is part of that process.


Hm, now that I see the SubPlan in there, I wonder whether 9.6 is
accounting more conservatively for the cost of the subplan.  It
probably is assuming that the subplan gets run for each row fetched
from the index, although the loops and rows-removed counts show
that the previous filter conditions reject 99% of the fetched rows.

But that code looks the same in 9.4, so I don't understand why
the 9.4 estimate isn't equally large ...


Besides the analyze issue, the other part of this is

asdid...@graceful.hou/20106> select 
pg_size_pretty(pg_relation_size('bdata_forks'));

 pg_size_pretty

 106 GB
(1 row)

asdid...@graceful.hou/20106> select relpages::bigint*8192/reltuples from 
pg_class where relname='bdata_forks';

 ?column?
--
 185.559397863791
(1 row)

With an effective_cache_size of 200GB that's not really helping things. 
But it's also another example of the planner's reluctance towards index 
scans.

--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
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] Regression from 9.4-9.6

[Tom Lane]

Jim Nasby  writes:
> On 10/8/17 2:34 PM, Tom Lane wrote:
>> Why has this indexscan's cost estimate changed so much?

> Great question... the only thing that sticks out is the coalesce(). Let 
> me see if an analyze with a higher stats target changes anything. FWIW, 
> the 9.6 database is copied from the 9.4 one once a week and then 
> pg_upgraded. I'm pretty sure an ANALYZE is part of that process.

Hm, now that I see the SubPlan in there, I wonder whether 9.6 is
accounting more conservatively for the cost of the subplan.  It
probably is assuming that the subplan gets run for each row fetched
from the index, although the loops and rows-removed counts show
that the previous filter conditions reject 99% of the fetched rows.

But that code looks the same in 9.4, so I don't understand why
the 9.4 estimate isn't equally large ...

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

Re: [PERFORM] Regression from 9.4-9.6

[Jim Nasby]

On 10/8/17 3:02 PM, Jim Nasby wrote:


-> Index Scan using bdata_filed_departuretime on bdata_forks 
(cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777 
rows=508 loops=1)


-> Index Scan using bdata_filed_departuretime on bdata_forks 
(cost=0.57..14894236.06 rows=1 width=36) (actual 
time=892.664..3025.653 rows=508 loops=1)


I think the reason it's discarding the preferable plan is that, with this
huge increment in the estimated cost getting added to both alternatives,
the two nestloop plans have fuzzily the same total cost, and it's picking
the one you don't want on the basis of some secondary criterion.


Great question... the only thing that sticks out is the coalesce(). Let 
me see if an analyze with a higher stats target changes anything. FWIW, 
the 9.6 database is copied from the 9.4 one once a week and then 
pg_upgraded. I'm pretty sure an ANALYZE is part of that process.


Turns out that analyze is the 'problem'. On the 9.4 database, pg_stats 
shows that the newest date in filed_departuretime is 3/18/2017, while 
the 9.6 database is up-to-date. If I change the query to use 2/9/2018 
instead of 7/20/2017 I get the same results.


So, the larger cost estimate is theoretically more correct. If I set 
random_page_cost = 1 I end up with a good plan.

--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
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] Regression from 9.4-9.6

[Jim Nasby]

On 10/8/17 2:34 PM, Tom Lane wrote:

Jim Nasby  writes:

I've got a query that's regressed from 9.4 to 9.6. I suspect it has
something to do with the work done around bad plans from single-row
estimates.


Why has this indexscan's cost estimate changed so much?


->  Index Scan using bdata_filed_departuretime on bdata_forks  
(cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777 rows=508 
loops=1)



->  Index Scan using bdata_filed_departuretime on bdata_forks  
(cost=0.57..14894236.06 rows=1 width=36) (actual time=892.664..3025.653 rows=508 
loops=1)


I think the reason it's discarding the preferable plan is that, with this
huge increment in the estimated cost getting added to both alternatives,
the two nestloop plans have fuzzily the same total cost, and it's picking
the one you don't want on the basis of some secondary criterion.


Great question... the only thing that sticks out is the coalesce(). Let 
me see if an analyze with a higher stats target changes anything. FWIW, 
the 9.6 database is copied from the 9.4 one once a week and then 
pg_upgraded. I'm pretty sure an ANALYZE is part of that process.


9.4:

 ->  Index Scan using bdata_filed_departuretime on bdata_forks  
(cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777 rows=508 
loops=1)
   Index Cond: ((filed_departuretime >= '2017-07-20 
05:00:00'::timestamp without time zone) AND (filed_departuretime <= '2017-07-30 
04:59:59'::timestamp without time zone))
   Filter: (((view_www IS NULL) OR (view_www IS TRUE)) AND (sch_block_out IS 
NOT NULL) AND (diverted IS NOT TRUE) AND (true_cancel IS NOT TRUE) AND (sch_block_out >= 
'2017-07-23 05:00:00'::timestamp without time zone) AND (sch_block_out <= '2017-07-24 
04:59:59'::timestamp without time zone) AND (COALESCE(actualarrivaltime, cancellation) 
>= actualdeparturetime) AND ((act_block_out - sch_block_out) >= '00:15:00'::interval) 
AND (((SubPlan 2))::text = 'KORD'::text))
   Rows Removed by Filter: 2696593
   SubPlan 2
 ->  Index Scan using bd_airport_pkey on bd_airport 
bd_airport_1  (cost=0.56..4.58 rows=1 width=20) (actual time=0.003..0.003 rows=1 
loops=21652)
   Index Cond: (id = bdata_forks.origin_id)


9.6:

 ->  Index Scan using bdata_filed_departuretime on bdata_forks  
(cost=0.57..14894236.06 rows=1 width=36) (actual time=892.664..3025.653 rows=508 
loops=1)
   Index Cond: ((filed_departuretime >= '2017-07-20 
05:00:00'::timestamp without time zone) AND (filed_departuretime <= '2017-07-30 
04:59:59'::timestamp without time zone))
   Filter: (((view_www IS NULL) OR (view_www IS TRUE)) AND (sch_block_out IS 
NOT NULL) AND (diverted IS NOT TRUE) AND (true_cancel IS NOT TRUE) AND (sch_block_out >= 
'2017-07-23 05:00:00'::timestamp without time zone) AND (sch_block_out <= '2017-07-24 
04:59:59'::timestamp without time zone) AND (COALESCE(actualarrivaltime, cancellation) 
>= actualdeparturetime) AND ((act_block_out - sch_block_out) >= '00:15:00'::interval) 
AND (((SubPlan 2))::text = 'KORD'::text))
   Rows Removed by Filter: 2696592
   SubPlan 2
 ->  Index Scan using bd_airport_pkey on bd_airport 
bd_airport_1  (cost=0.56..4.58 rows=1 width=20) (actual time=0.004..0.004 rows=1 
loops=21652)
   Index Cond: (id = bdata_forks.origin_id)



--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
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] Regression from 9.4-9.6

[Tom Lane]

Jim Nasby  writes:
> I've got a query that's regressed from 9.4 to 9.6. I suspect it has 
> something to do with the work done around bad plans from single-row 
> estimates.

Why has this indexscan's cost estimate changed so much?

>> ->  Index Scan using bdata_filed_departuretime on bdata_forks  
>> (cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777 
>> rows=508 loops=1)

>> ->  Index Scan using bdata_filed_departuretime on bdata_forks  
>> (cost=0.57..14894236.06 rows=1 width=36) (actual time=892.664..3025.653 
>> rows=508 loops=1)

I think the reason it's discarding the preferable plan is that, with this
huge increment in the estimated cost getting added to both alternatives,
the two nestloop plans have fuzzily the same total cost, and it's picking
the one you don't want on the basis of some secondary criterion.

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

Re: [PERFORM] How does max_parallel_workers_per_gather change load averages?

[David Rowley]

On 4 October 2017 at 08:48, Ben Nachtrieb  wrote:
> I have 2 cores and my max_parallel_workers_per_gather = 2 and
> max_worker_processes = 8, but my load averages are between 8 and 5 with
> scheduled at 1/189 to 5/195. Are these so high because I increased
> max_parallel_workers_per_gather? My understanding is that if my load
> averages are greater than my number of cores the system is overloaded.
> Should I think about it differently once I increase
> max_parallel_workers_per_gather? How should I think about it?

Parallel query is not 100% efficient. For example, adding twice the
CPU, in theory, will never double the performance, there's always some
overhead to this. It's really only useful to do on systems with spare
CPU cycles to perform this extra work. You don't seem to have much to
spare, so you may get along better if you disable parallel query.


-- 
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] select with max functions

[Mark Kirkwood]

On 03/10/17 04:29, Tom Lane wrote:

Mariel Cherkassky  writes:

explain analyze   SELECT Ma.User_Id,
   COUNT(*) COUNT
FROM   Manuim Ma
WHERE  Ma.Bb_Open_Date  =
   (SELECT Bb_Open_Date
FROM   Manuim Man
WHERE  Man.User_Id = Ma.User_Id order
by   bb_open_date desc limit 1
   )
GROUP  BY Ma.User_Id
HAVING COUNT(*) > 1;

The core problem with this query is that the sub-select has to be done
over again for each row of the outer table, since it's a correlated
sub-select (ie, it refers to Ma.User_Id from the outer table).  Replacing
a max() call with handmade logic doesn't do anything to help that.
I'd try refactoring it so that you calculate the max Bb_Open_Date just
once for each user id, perhaps along the lines of

SELECT Ma.User_Id,
COUNT(*) COUNT
FROM   Manuim Ma,
   (SELECT User_Id, max(Bb_Open_Date) as max
FROM   Manuim Man
GROUP BY User_Id) ss
WHERE  Ma.User_Id = ss.User_Id AND
   Ma.Bb_Open_Date = ss.max
GROUP  BY Ma.User_Id
HAVING COUNT(*) > 1;

This is still not going to be instantaneous, but it might be better.

It's possible that an index on (User_Id, Bb_Open_Date) would help,
but I'm not sure.

regards, tom lane




Further ideas based on Tom's rewrite: If that MAX is still expensive it 
might be worth breaking



SELECT User_Id, max(Bb_Open_Date) as max
   FROM   Manuim Man
   GROUP BY User_Id

out into a VIEW, and considering making it MATERIALIZED, or creating an 
equivalent  trigger based summary table (there are examples in the docs 
of how to do this).


Cheers

Mark


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