Re: [HACKERS] increasing collapse_limits?

2011-05-06 Thread Jan UrbaƄski
On 01/05/11 21:16, Joshua Berkus wrote:
 Speaking of which, what happened to replacing GEQO with Simulated Annealing?  
 Where did that project go?

It stayed on github (https://github.com/wulczer/saio) and stagnated a
bit after I got my degree. It's on the top of my list of things to pick
up after the summer (or maybe even during the summer).

Cheers,
Jan

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


Re: [HACKERS] increasing collapse_limits?

2011-05-01 Thread Joshua Berkus

Pavel,

 Actually we had to solve a issue with slow SELECT. The problem was in
 low value of JOIN_COLLAPSE_LIMITS. Can we increase a default of this
 value. I checked some complex query, and planner needed about 200ms
 for JOIN_COLLAPSE_LIMIT = 16. So some around 12 can be well.

I'm not comfortable with increasing the default, yet.  While folks on dedicated 
good hardware can handle a collapse of 10-12 joins, a lot of people are running 
PostgreSQL on VMs these days whose real CPU power is no better than a Pentium 
IV.  Also, if you're doing OLTP queries on small tables, spending 20ms planning 
a query is unreasonably slow in a way it is not for a DW query.

It does make a reasonable piece of advice for those tuning for DW, though.  
I'll add it to my list.

Speaking of which, what happened to replacing GEQO with Simulated Annealing?  
Where did that project go?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco

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


Re: [HACKERS] increasing collapse_limits?

2011-05-01 Thread Robert Haas
On Apr 30, 2011, at 10:21 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 It also occurs to me to wonder if we could adjust the limit on-the-fly
 based on noticing whether or not the query is prone to worst-case
 behavior, ie how dense is the join connection graph.

I've had this thought - or a similar one - before also. I am not sure how to 
make it work mechanically but I think it would be tremendous if we could make 
it work. For most people, my previous naive suggestion (remove the limit 
entirely) would actually work fine, BUT if you hit the problem cases then even 
a small increase is too much. So I don't really think increasing the limit will 
eliminate  the need for manual fiddling - what we really need to do is come up 
with a more accurate measure of measure of complexity than number of tables.

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


Re: [HACKERS] increasing collapse_limits?

2011-05-01 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Joshua Berkus wrote:

 I'm not comfortable with increasing the default, yet.  While folks on 
 dedicated good hardware can handle a collapse of 10-12 joins, a lot 
 of people are running PostgreSQL on VMs these days whose real CPU 
 power is no better than a Pentium IV.

Really? First, I don't think that's true, the average CPU power 
is much higher than that. Second, this sounds like the 'ol 
tune it for a toaster trap where we never make improvements 
to the defaults because someone, somewhere, might *gasp* use 
Postgres on an underpowered server.

 Also, if you're doing OLTP queries on small tables, spending 20ms 
 planning a query is unreasonably slow in a way it is not for a 
 DW query.

Again, seriously? Do you have numbers to back that up?

I could see not going to 16 right away, but who would honestly have a 
problem with going to 10? I agree with Tom, let's bump this up a 
little bit and see what happens. My guess is that we won't see a 
single post in which we advise people to drop it down from 10 to 8. 
Personally, I'd like to see them go to 12, as that's the best sweet 
spot I've seen in the field, but I'll take 10 first. :)

Tom Lane asked re setting to 10:
 Don't know how much difference that would make in the real world though.

I've seen a handful of cases that have benefitted from 10, but many 
more* that benefitted from 12 (*okay, a larger handful anyway, it's not 
like I have to adjust it too often).

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201105012153
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk2+DqsACgkQvJuQZxSWSshRfQCgzX5JlnCmKTndA7WcF/mt0Kpk
b30AoLKrVKMm0rbZNNhgVjt/Xne4NDpj
=0deF
-END PGP SIGNATURE-



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


[HACKERS] increasing collapse_limits?

2011-04-30 Thread Pavel Stehule
Hello

Actually we had to solve a issue with slow SELECT. The problem was in
low value of JOIN_COLLAPSE_LIMITS. Can we increase a default of this
value. I checked some complex query, and planner needed about 200ms
for JOIN_COLLAPSE_LIMIT = 16. So some around 12 can be well.

Regards

Pavel Stehule

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


Re: [HACKERS] increasing collapse_limits?

2011-04-30 Thread Tom Lane
Pavel Stehule pavel.steh...@gmail.com writes:
 Actually we had to solve a issue with slow SELECT. The problem was in
 low value of JOIN_COLLAPSE_LIMITS. Can we increase a default of this
 value. I checked some complex query, and planner needed about 200ms
 for JOIN_COLLAPSE_LIMIT = 16. So some around 12 can be well.

I'd like to see a rather larger survey of cases before changing that.
Also, amount of memory consumed is at least as large a concern here
as runtime.

regards, tom lane

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


Re: [HACKERS] increasing collapse_limits?

2011-04-30 Thread Robert Haas
On Apr 30, 2011, at 7:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Pavel Stehule pavel.steh...@gmail.com writes:
 Actually we had to solve a issue with slow SELECT. The problem was in
 low value of JOIN_COLLAPSE_LIMITS. Can we increase a default of this
 value. I checked some complex query, and planner needed about 200ms
 for JOIN_COLLAPSE_LIMIT = 16. So some around 12 can be well.
 
 I'd like to see a rather larger survey of cases before changing that.
 Also, amount of memory consumed is at least as large a concern here
 as runtime.

I seem to remember that I was the last one to suggest raising these limits and 
someone demonstrated rather convincingly that for certain classes of queries 
that would cause really big problems.

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


Re: [HACKERS] increasing collapse_limits?

2011-04-30 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 I seem to remember that I was the last one to suggest raising these limits 
 and someone demonstrated rather convincingly that for certain classes of 
 queries that would cause really big problems.

You proposed removing the collapse limits altogether, but that crashed
and burned pretty quickly --- see the archives from 2009, eg here
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00358.php
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00947.php
http://archives.postgresql.org/pgsql-hackers/2009-11/msg00306.php

I'm not opposed to raising the limits somewhat, but I'd like to see a
more thorough case made for what to raise them to.  In principle there
are k! join orders for a k-way join problem, which means that raising
the limit from 8 to 12 could result in a 1-fold increase in planner
runtime and memory consumption.  In practice, because of the heuristic
that we avoid considering clauseless joins if possible, most queries
don't see growth rates that bad --- it would require a query in which
every relation is linked to every other relation by a join clause.
But that *can* happen (remember that clauses generated by transitive
equality do count).  So there needs to be some attention paid to both
average and worst case behaviors.

Raising them to 10 would only impose a worst case 100-fold growth,
which is not as scary as 1-fold, so maybe we should consider
that as an intermediate step.  Don't know how much difference that
would make in the real world though.

It also occurs to me to wonder if we could adjust the limit on-the-fly
based on noticing whether or not the query is prone to worst-case
behavior, ie how dense is the join connection graph.  Right now it'd be
difficult to do that with any reliability, though, because we don't look
for equivalence classes until after we've fixed our attention on a
particular join subproblem.

regards, tom lane

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


Re: [HACKERS] increasing collapse_limits?

2011-04-30 Thread Greg Stark
On Sat, Apr 30, 2011 at 9:21 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 - it would require a query in which
 every relation is linked to every other relation by a join clause.
 But that *can* happen (remember that clauses generated by transitive
 equality do count).

It sounds like you're describing precisely a star schema join which
isn't an uncommon design pattern at all.

-- 
greg

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


Re: [HACKERS] increasing collapse_limits?

2011-04-30 Thread Mark Kirkwood

On 01/05/11 11:53, Greg Stark wrote:

On Sat, Apr 30, 2011 at 9:21 PM, Tom Lanet...@sss.pgh.pa.us  wrote:

- it would require a query in which
every relation is linked to every other relation by a join clause.
But that *can* happen (remember that clauses generated by transitive
equality do count).

It sounds like you're describing precisely a star schema join which
isn't an uncommon design pattern at all.



Nice example here:

http://archives.postgresql.org/pgsql-bugs/2011-04/msg00100.php

Strictly only a 'star-like' query as the foreign key references go the 
opposite way from a true star. However it illustrates the planner memory 
growth well (1.1G on 32-bit 1.7G on 64-bit systems).


A point I didn't mention is that the memory use is quite dependent on 
the choice of word values for the AND keyword = 'word' clause - the 
text example had 6 all the same. Setting them all different (even after 
adjusting the data so the there *was* a number of matching rows to find) 
resulted in significantly less memory consumed (I can dig up some 
examples if it might be interesting).


Cheers

Mark


Re: [HACKERS] increasing collapse_limits?

2011-04-30 Thread Tom Lane
Mark Kirkwood mark.kirkw...@catalyst.net.nz writes:
 On 01/05/11 11:53, Greg Stark wrote:
 On Sat, Apr 30, 2011 at 9:21 PM, Tom Lanet...@sss.pgh.pa.us  wrote:
 - it would require a query in which
 every relation is linked to every other relation by a join clause.
 But that *can* happen (remember that clauses generated by transitive
 equality do count).

 It sounds like you're describing precisely a star schema join which
 isn't an uncommon design pattern at all.

A normal star schema doesn't really do this because the join conditions
are generally on different columns of the central fact table.
However...

 Nice example here:
 http://archives.postgresql.org/pgsql-bugs/2011-04/msg00100.php
 Strictly only a 'star-like' query as the foreign key references go the 
 opposite way from a true star. However it illustrates the planner memory 
 growth well (1.1G on 32-bit 1.7G on 64-bit systems).

 A point I didn't mention is that the memory use is quite dependent on 
 the choice of word values for the AND keyword = 'word' clause - the 
 text example had 6 all the same. Setting them all different (even after 
 adjusting the data so the there *was* a number of matching rows to find) 
 resulted in significantly less memory consumed (I can dig up some 
 examples if it might be interesting).

Yeah.  What you have there is that n.nodeid is equated to columns of six
other tables, so those seven tables form a group in which every table
can be joined directly to every other (because of transitive deduction
of equality clauses).  So it's kinda bad already.  But then, if the kwN
tables have keyword all equated to the same constant (and thus to each
other), that's another group of six tables that can all be joined
directly to each other.  So that results in a large increase in the
number of join sequences that will get explored.

regards, tom lane

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


Re: [HACKERS] increasing collapse_limits?

2011-04-30 Thread Pavel Stehule
Hello

a slow query is just simple

like
  SELECT FROM a
LEFT JOIN b ON ..
LEFT JOIN c ON ..
LEFT JOIN d ON ..
LEFT JOIN e ON ..
WHERE e.x = number

a slow query plan

explain analyze select * from v_vypis_parcel_puvodni where par_id = 1396907206



---

Nested Loop Left Join  (cost=4043.95..12777.12 rows=1 width=415)
(actual time=46813.256..47130.773 rows=1 loops=1)

  Join Filter: (budovy.id = parcely.bud_id)

  -  Nested Loop Left Join  (cost=0.00..27.42 rows=1 width=262)
(actual time=0.311..0.634 rows=1 loops=1)

Join Filter: (katastr_uzemi.kod = parcely.katuze_kod)

-  Nested Loop Left Join  (cost=0.00..20.55 rows=1
width=212) (actual time=0.282..0.301 rows=1 loops=1)

  -  Nested Loop Left Join  (cost=0.00..12.26 rows=1
width=208) (actual time=0.162..0.175 rows=1 loops=1)

Join Filter: (parcely.zdpaze_kod = zdroje_parcel_ze.kod)

-  Nested Loop Left Join  (cost=0.00..11.19
rows=1 width=145) (actual time=0.148..0.159 rows=1 loops=1)

  Join Filter: (d_pozemku.kod = parcely.drupoz_kod)

  -  Nested Loop Left Join  (cost=0.00..9.94
rows=1 width=140) (actual time=0.099..0.104 rows=1 loops=1)

Join Filter: (zp_vyuziti_poz.kod =
parcely.zpvypa_kod)

-  Index Scan using par_pk on
parcely  (cost=0.00..8.31 rows=1 width=84) (actual time=0.037..0.040
rows=1 loops=1)

  Index Cond: (id = 1396907206::numeric)

-  Seq Scan on zp_vyuziti_poz
(cost=0.00..1.28 rows=28 width=70) (actual time=0.005..0.023 rows=28
loops=1)

  -  Seq Scan on d_pozemku  (cost=0.00..1.11
rows=11 width=19) (actual time=0.023..0.033 rows=11 loops=1)

-  Seq Scan on zdroje_parcel_ze
(cost=0.00..1.03 rows=3 width=70) (actual time=0.004..0.006 rows=3
loops=1)

  -  Index Scan using tel_pk on telesa  (cost=0.00..8.28
rows=1 width=15) (actual time=0.112..0.116 rows=1 loops=1)

Index Cond: (parcely.tel_id = public.telesa.id)

-  Seq Scan on katastr_uzemi  (cost=0.00..4.72 rows=172
width=54) (actual time=0.019..0.160 rows=172 loops=1)

  -  Hash Left Join  (cost=4043.95..11787.52 rows=76968 width=164)
(actual time=19827.669..47069.869 rows=77117 loops=1)

Hash Cond: (budovy.typbud_kod = t_budov.kod)

-  Hash Left Join  (cost=4042.82..10728.08 rows=76968
width=141) (actual time=19827.625..46938.954 rows=77117 loops=1)

  Hash Cond: (budovy.caobce_kod = casti_obci.kod)

  -  Hash Left Join  (cost=4028.14..9827.78 rows=76968
width=46) (actual time=19826.622..46824.288 rows=77117 loops=1)

Hash Cond: (budovy.id = casti_budov.bud_id)

-  Hash Left Join  (cost=4015.38..8850.54
rows=76968 width=33) (actual time=19825.627..46710.476 rows=76968
loops=1)

  Hash Cond: (budovy.tel_id = public.telesa.id)

  -  Seq Scan on budovy  (cost=0.00..1903.68
rows=76968 width=40) (actual time=0.031..86.709 rows=76968 loops=1)

  -  Hash  (cost=2214.17..2214.17
rows=103617 width=15) (actual time=19691.650..19691.650 rows=103617
loops=1)

-  Seq Scan on telesa
(cost=0.00..2214.17 rows=103617 width=15) (actual time=0.015..96.548
rows=103617 loops=1)

-  Hash  (cost=9.79..9.79 rows=238 width=28)
(actual time=0.937..0.937 rows=238 loops=1)

  -  Hash Left Join  (cost=1.14..9.79
rows=238 width=28) (actual time=0.104..0.699 rows=238 loops=1)

Hash Cond: (casti_budov.typbud_kod =
t_bud_ii.kod)

-  Seq Scan on casti_budov
(cost=0.00..5.38 rows=238 width=25) (actual time=0.030..0.201 rows=238
loops=1)

-  Hash  (cost=1.06..1.06 rows=6
width=17) (actual time=0.032..0.032 rows=6 loops=1)

  -  Seq Scan on t_budov
t_bud_ii  (cost=0.00..1.06 rows=6 width=17) (actual time=0.008..0.014
rows=6 loops=1)

  -  Hash  (cost=12.20..12.20 rows=198 width=103)
(actual time=0.940..0.940 rows=198 loops=1)

-  Hash Left Join  (cost=4.50..12.20 rows=198
width=103) (actual time=0.255..0.698 rows=198 loops=1)

  Hash Cond: (casti_obci.obce_kod = obce.kod)

  -  Seq Scan on casti_obci
(cost=0.00..4.98 rows=198 width=58) (actual time=0.004..0.126 rows=198
loops=1)

  -  Hash  (cost=3.11..3.11 rows=111
width=53) (actual time=0.206..0.206 rows=111 loops=1)

-  Seq Scan on obce
(cost=0.00..3.11 rows=111 width=53) (actual time=0.010..0.105 rows=111
loops=1)

-  Hash  (cost=1.06..1.06 rows=6 width=17) (actual
time=0.019..0.019 rows=6 loops=1)