The Heap™ – Consultancy ©®

good morning

Tom V

7:02 AM

morning

ConcernedOfTunbridgeWells

7:51 AM

@JamesLupolt No, they didn't say much.

But I found this, from last year: slideshare.net/EnterpriseDB/…

which says that it's going to be processes, not threads. But not through forking.

And this is a report on some of the work, done till now: Parallel Sequential Scan for PostgreSQL 9.5

8:46 AM

Morning

ConcernedOfTunbridgeWells

8:58 AM

@ypercube Shiny things!

Jack Douglas

9:15 AM

@ypercube Next time you go to one of these I'd be glad to tag along

ConcernedOfTunbridgeWells

@JackDouglas Sure, there may be other meetings: meetup.com/London-PostgreSQL-Meetup-Group

James Lupolt

@ypercube Thanks, yes, I was reading those earlier but couldn't find any updates. It seems like getting parallel scan is the first step, then hopefully parallel hash & loop join, parallel sorts, etc

You probably need accurate statistics and reliable statistics updates to get even distribution between threads (er, processes) in a parallel query. Does that tend to be much of a problem in Postgres?

@JamesLupolt These are updated by vacuum IIRC.

Jack Douglas

10:02 AM

@ypercube thanks, signed up :)

10:43 AM

Morning

Sorry to be a bother, but I want to check back whether I understood Joins correctly...

I have some fairly complex query, and upon adding a table to it with a LEFT OUTER JOIN the result count went down....

did I misunderstand about how left outer join works, or should this not happen?

@Vogel612 show us the whole query

you probably use the table in the WHERE clause as well.

checked back, and diffing against a previous version says no...

If you only add a LEFT JOIN table in the FROM, the result should have the same or more rows.

but this is for the FROM of the main query. If you make changes in the FROM of a subqueriy, the results may change either up or down.

I'm sligthly oversimplifying here, but.. this "query" is basically a badly written view in form of a postgres-function...

You can share the query with pastebin.com

10:52 AM

Confidential company code and lots of german.. I'd like to prevent that.

so there will be something reducing the results in another place...

last question: does it make a difference when the query is comma-style joins?

Yes

How do you combine LEFT joins with comma style?

Or no. Depending on what you mean by difference

Hm, yeah, Andriy is right. It probably does not matter.

Where do the table columns appear? Only in the ON clause of this LEFT join and in the SELECT list?

from table1, table2, table3, table4
   where table3.table2_fid = table2.pid and table2.table1_fid = table1.pid and table3.status = 1
   and table1.table4_fid = table4.pid

this doesn't work out nicely...

the from clause changed to:

from table1, table2 LEFT OUTER JOIN table5 ON table2.table5_fid = table5.pid, table3, table 4

If table5 isn't referenced in WHERE, the row count should not decrease after adding the left join.

11:00 AM

okay then something that I joined into there interferes with the loop body somehow..

thanks for the input :)

Lamak

11:41 AM

@swasheck I am now, sorry

@AndriyM I think their problem is that they use SUM(DISTINCT ..): dba.stackexchange.com/questions/106239/…

I can think of no reason for using SUM of distinct values.

@ypercube Yes, but they kind of have to. They are grouping by a column taken from the child table while aggregating results taking from the parent table.

Ah, I see the data now.

@ypercube Neither can I.

Lamak

11:57 AM

you guys just don't have imagination

@Lamak Enlighten us then :)

Lamak

I never said I had imagination either