Performance dramatically slower on left join

[kensystem]

Hi, I am seeing slow speeds on the following LEFT JOIN:

SELECT a.objId
FROM om AS a
JOIN om AS b ON a.objId=b.objId AND a.attribid=28
LEFT JOIN om AS own ON a.objId=own.objId AND own.attribid=83
WHERE b.attribid=25 AND b.objValue BETWEEN '8000012b6f634c01' AND
'8000012b7489a7ff'
AND (own.objValue='8000000000000002' OR own.objValue IS NULL)
GROUP BY a.objid

On H2 this query runs on 36000ms;

Same data and indexes in PG on same hardware: <10ms.

In H2, this query also runs <10ms if the LEFT JOIN is changed into a
inner join (removing the 'LEFT' keyword).

On the surface it seems that this is a just a query planner issue in
H2; is this a known issue??

Thanks,
Ken

[Thomas Mueller]

Hi,

Could you provide more data (the tables, the indexes, did you run
analyze, how many rows per table, sample data, what does ANALYZE
EXPLAIN SELECT return)?

Regards,
Thomas

[kensystem]

Hi Thomas,

The table contains about 1,500,00 rows.

CREATE TABLE om (
omattribid integer DEFAULT 0 NOT NULL,
objid bigint,
ownerid integer,
objvalue text,
listorder integer,
omid bigint DEFAULT nextval('om_omid_seq') NOT NULL
);
CREATE INDEX om_attrval_id ON om (omattribid, objvalue);
CREATE INDEX om_objid_idx ON om (objid);
CREATE INDEX om_objid_omattribid ON om (objid, omattribid);
CREATE INDEX om_objvalue_idx ON om (objvalue);
CREATE INDEX om_omschemaid_idx ON om (omattribid);

EXPLAIN ANALYZE:
'SELECT A.OBJID FROM PUBLIC.OM A /* PUBLIC.OM_ATTRVAL_ID: OMATTRIBID =
28 */ /* WHERE A.OMATTRIBID = 28 */ /* scanCount: 49695 */ INNER JOIN
PUBLIC.OM B /* PUBLIC.OM_ATTRVAL_ID: OMATTRIBID = 25 AND OBJVALUE >=
'8000012b6f634c02' AND OBJVALUE <= '8000012b7489a7ff' */ ON 1=1 /*
WHERE (B.OBJVALUE <= '8000012b7489a7ff') AND ((B.OBJVALUE >=
'8000012b6f634c02') AND ((B.OMATTRIBID = 25) AND (A.OBJID = B.OBJID)))
*/ /* scanCount: 3130722 */ LEFT OUTER JOIN PUBLIC.OM OWN /*
PUBLIC.OM_ATTRVAL_ID: OMATTRIBID = 83 AND OBJVALUE =
'8000000000000002' */ ON (OWN.OMATTRIBID = 83) AND (A.OBJID =
OWN.OBJID) /* scanCount: 1173908 */ WHERE ((A.OMATTRIBID = 28) AND
(A.OBJID = B.OBJID)) AND ((OWN.OBJVALUE = '8000000000000002') AND
((B.OMATTRIBID = 25) AND ((B.OBJVALUE >= '8000012b6f634c02') AND
(B.OBJVALUE <= '8000012b7489a7ff')))) GROUP BY A.OBJID /* total:
1006423 OM.OM_ATTRVAL_ID read: 24575 (2%) OM.OM_DATA read: 981848
(97%) */'

H2 is started with:
java -Xmx384M -Dh2.returnLobObjects=false -Dh2.objectCacheSize=50000 -
jar h2-1.2.143.jar org.h2.tools.Server -webAllowOthers -tcpAllowOthers
-tcpPort 9101 &
(and other variations on Xmx and objectCacheSize)

The hardware is a 1Ghz CPU 1GB RAM (50% free).

Sorry I did not provide more info sooner, please let me know if more
is needed.

ken

[kensystem]

For clarity I should point out that the GROUP BY is superfluous, both
with and without the group by, the my resultset size in my case is 64
rows.

Also, the following subselect (I believe is near identical to the LEFT
JOIN) runs much faster; about 2500ms.

SELECT a.objId
FROM om AS a

LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE a.omattribid=28 AND a.objId IN (SELECT objId FROM om WHERE
omattribid=25 AND objValue BETWEEN '8000012b70e3d182' AND
'8000012b760a2d7f')

AND (own.objValue='8000000000000002' OR own.objValue IS NULL)
GROUP BY a.objid

'SELECT A.OBJID FROM PUBLIC.OM A /* PUBLIC.OM_OBJID_OMATTRIBID:
OMATTRIBID = 28 AND OBJID IN(SELECT DISTINCT OBJID FROM PUBLIC.OM /++

PUBLIC.OM_ATTRVAL_ID: OMATTRIBID = 25 AND OBJVALUE >=

'8000012b70e3d182' AND OBJVALUE <= '8000012b760a2d7f' ++/ /++
scanCount: 63 ++/ WHERE (OMATTRIBID = 25) AND ((OBJVALUE >=
'8000012b70e3d182') AND (OBJVALUE <= '8000012b760a2d7f'))) */ /* WHERE
(A.OMATTRIBID = 28) AND (A.OBJID IN(SELECT DISTINCT OBJID FROM
PUBLIC.OM /++ PUBLIC.OM_ATTRVAL_ID: OMATTRIBID = 25 AND OBJVALUE >=
'8000012b70e3d182' AND OBJVALUE <= '8000012b760a2d7f' ++/ /++
scanCount: 63 ++/ WHERE (OMATTRIBID = 25) AND ((OBJVALUE >=
'8000012b70e3d182') AND (OBJVALUE <= '8000012b760a2d7f')))) */ /*
scanCount: 1518817 */ LEFT OUTER JOIN PUBLIC.OM OWN /*
PUBLIC.OM_OBJID_OMATTRIBID: OBJID = A.OBJID AND OMATTRIBID = 83 */ ON
(OWN.OMATTRIBID = 83) AND (A.OBJID = OWN.OBJID) /* scanCount: 124 */
WHERE ((OWN.OBJVALUE = '8000000000000002') OR (OWN.OBJVALUE IS NULL))
AND ((A.OMATTRIBID = 28) AND (A.OBJID IN(SELECT DISTINCT OBJID FROM
PUBLIC.OM /* PUBLIC.OM_ATTRVAL_ID: OMATTRIBID = 25 AND OBJVALUE >=
'8000012b70e3d182' AND OBJVALUE <= '8000012b760a2d7f' */ /* scanCount:
63 */ WHERE (OMATTRIBID = 25) AND ((OBJVALUE >= '8000012b70e3d182')
AND (OBJVALUE <= '8000012b760a2d7f'))))) GROUP BY A.OBJID'

[Thomas Mueller]

Hi,

I don't understand yet what the problem could be... Did you run
ANALYZE before running the query?

What is the selectivity of the columns? To get this data, you need to
run ANALYZE and then SCRIPT NODATA. This will get you the CREATE TABLE
statements with the column selectivity. Could you then post those
CREATE TABLE statements?

Regards,
Thomas

[kensystem]

Yes, I am running ANALYZE. My previous test time of 30+second is
reduced now, to 5 seconds (both the join and subselect version)...
perhaps I did not run ANALYZE before the first test.

Here is the CREATE TABLE script for the (one) table, after running
ANALYZE:
'CREATE CACHED TABLE PUBLIC.OM( OMATTRIBID INTEGER DEFAULT 0 NOT NULL
SELECTIVITY 1, OBJID BIGINT SELECTIVITY 5, OWNERID INTEGER SELECTIVITY
1, OBJVALUE VARCHAR(100000) SELECTIVITY 12, LISTORDER INTEGER
SELECTIVITY 1, OMID BIGINT DEFAULT (NEXT VALUE FOR
PUBLIC.SYSTEM_SEQUENCE_A36C4B13_1590_4A80_A90E_C08DC91F28F2) NOT NULL
NULL_TO_DEFAULT SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_A36C4B13_1590_4A80_A90E_C08DC91F28F2
SELECTIVITY 100 );'

Thomas if you wish I can email you a link to the .db file file
itself, or the INSERT statements, in 7z or zip form. Please let me
know which you prefer.

Thanks,
ken

[Thomas Mueller]

Hi,

> Thomas if you wish I can email you a link to  the .db file file
> itself, or the INSERT statements, in 7z or zip form. Please let me
> know which you prefer.

Whatever is the smallest. The database file would be a bit simpler I guess.

Regards,
Thomas

[kensystem]

Hi Thomas, just wondering if you were able to verify this using the
file I provided...

Thanks,
Ken

On Oct 30, 10:47 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

For me, the query took less than 2 seconds. Does it really take 36
seconds for you? I don't really understand the query, could you try to
explain what it does and why do you think it should be fast? I don't
know exactly how PostgreSQL manages to be faster for this query, maybe
it uses a merge join which is not implemented yet in H2. But I don't
see any obvious bug in H2. The indexes have a very bad selectivity.
Some indexes are not needed, for example om_objid_idx (because
om_objid_omattribid contains the same data) and om_omschemaid_idx
(because om_attrval_id contains the same data).

Regards,
Thomas

[kensystem]

Thomas, I think the speed difference could be due to your using a (I
presume) relatively modern machine (fast CPU and RAM); my machine is
about 10 years old; a 1Ghz i386 CPU and slow RAM.

Anyway were you able to see much faster execution, if you changed the
query to an inner-join? (I saw ~1000 times faster execution with the
inner join). And if so, what could cause that? (I presume if the inner
join runs fast without merge join, should a OUTER not be similar?)

As to what the table design does, its more complicated than deserves
mention here; perhaps it would suffice to say, it is a way to allow
arbitrary data sets (similar to LDAP schema/hierarchy and data), as
opposed to familiar 2-dimensional relational database tables..

Anyway all I'd ask is that we find a way to make the out join execute-
time be similar to the inner join....

Thanks as always,
-Ken

On Nov 3, 11:23 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[kensystem]

Let me correct my last statement; it WAS running in ~36 seconds, until
(mentioned in a later post) I ran ANALYZE per your suggestion, after
which it now runs in between 5-8 seconds... though that is still not
near as fast as the inner join:

SELECT a.objId
FROM om AS a

JOIN om AS b ON a.objId=b.objId AND a.omattribid=28
LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE b.omattribid=25 AND (b.objValue BETWEEN '8000012b6f634c01' AND
'8000012b7489a7ff')

AND (own.objValue='8000000000000002' OR own.objValue IS NULL)

(62 rows, 8719ms)

SELECT a.objId
FROM om AS a

JOIN om AS b ON a.objId=b.objId AND a.omattribid=28
JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE b.omattribid=25 AND (b.objValue BETWEEN '8000012b6f634c01' AND
'8000012b7489a7ff')

AND (own.objValue='8000000000000002' OR own.objValue IS NULL)

(62 rows, 15ms)

[kensystem]

Thomas, using the database I sent you, do you also see these
variations in execution for the following queries?

SELECT a.objId
FROM om AS a

JOIN om AS b ON a.objId=b.objId AND b.omattribid=25
JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE a.omattribid=28 AND b.objValue = '8000012b73035e97'

AND (own.objValue='8000000000000002' OR own.objValue IS NULL)

62 rows, 10ms

then remove the OR:

SELECT a.objId
FROM om AS a

JOIN om AS b ON a.objId=b.objId AND b.omattribid=25
JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE a.omattribid=28 AND b.objValue = '8000012b73035e97'
AND (own.objValue='8000000000000002')
62 rows, 159833ms

Just inner joins, but seems odd that the second one without the OR is
much slower..

Regards,
Ken

[kensystem]

Hi Thomas, just wondering if you were able to find a reason why the
LEFT JOIN exec time would be so different from the INNER JOIN, using
the db I sent you. Inner joins appear to be more than 100 times faster
that the outer join. In searching it appears other users noticed this
also.

Is it due to the OR optimization issue you mentioned before (since
LEFT JOINs are similar to ORs; ".....FROM a JOIN b ON b.row=a.row OR b
IS NULL")

Thanks,
Ken

[Thomas Mueller]

Hi,

You are right, I think it's a bug. There is a workaround, instead of:

SELECT count(*)
FROM om AS a INNER JOIN om AS b ON a.objId=b.objId AND a.omattribid=28
LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE b.omattribid=25
AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff';

use:

SELECT count(*)
FROM om AS b INNER JOIN om AS a ON a.objId=b.objId AND a.omattribid=28
LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
WHERE b.omattribid=25
AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff';

So, basically swap the joins manually. Of course it should be done
automatically, and I will try to fix it.

Regards,
Thomas

[Wildam Martin]

On Wed, Nov 10, 2010 at 08:16, Thomas Mueller
<thomas.to...@gmail.com> wrote:
> You are right, I think it's a bug. There is a workaround, instead of:

I am not the OP, but in the long discussion you showed the will to
examine and really understand - to finally identify a bug which you
also admited. From the developers I know, you are one of those who do
not primarily assume a user fault. - To show my respect, 20 Euros have
been donated.

> SELECT count(*)
> FROM om AS a INNER JOIN om AS b ON a.objId=b.objId AND a.omattribid=28
> LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
> WHERE b.omattribid=25
> AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff';
>
> use:
>
> SELECT count(*)
> FROM om AS b INNER JOIN om AS a ON a.objId=b.objId AND a.omattribid=28
> LEFT JOIN om AS own ON a.objId=own.objId AND own.omattribid=83
> WHERE b.omattribid=25
> AND b.objValue BETWEEN '8000012b6f634c01' AND '8000012b7489a7ff';

I don't really understand why this makes a difference, it's all the same table.

> So, basically swap the joins manually. Of course it should be done
> automatically, and I will try to fix it.

As a workaround, when is it recommended to do it the other way round?
I ask because I assume that such an issue is noticed often only in
live systems when tables get large enough.
So I would like to know the circumstances I need to be aware of to be
careful with.

Best regards,

Martin.
--
Martin Wildam

http://www.google.com/profiles/mwildam

[kensystem]

Ah, that did it! Much, much faster execution times! It is revealing to
how much underlying optimization must be made under the hood; in all
my years I took for granted that RDBMSs managed this automatically,
and I didn't stop to think that presentation order of the tables could
potentially be the issue. The query now appears to even run a little
faster than PG!

Many thanks again, Thomas, for an amazing product. I hope this
uncovers and helps to solve some of the other issues related to OR.

Ken

On Nov 10, 12:16 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Kerry Sainsbury]

"Back in the day" with Informix we were always rearranging the order
in which tables were listed, and I remember repeating certain columns
in WHERE clauses as a way to "encourage" the optimiser to use a
particular index.

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

[Rami]

I second to that.
Optimizer is the most crucial and difficult thing to do in a dbms.
The more simple and logical the language, the better the optimizer.

- Rami

[Dario Fassi]

BTW , on oldies DB2 we often used a trick to optimize complex or problematic queries doing:
1) Set optimization degree to max value
2) See how resolution plan and optimized sql change
3) Rewrite the original query in regards of (2) and try with normal optimization degree.
loop.

Very often this process ends with astronomical differences after:
1) Changing the first table or view in the FROM list
2) Reordering  the tables/views/joins in the FROM list
3) Factorizing big queries in re-utilized Common Expression tables
4) Replacing unnecessary outer joins with where constraints (inner joins)
5) Reordering where constraints list

regards
Dario

El 10/11/10 23:37, Kerry Sainsbury escribió:

[kensystem]

Hi Thomas,

Do you have an estimate of when this might ready for testing? (I
noticed it was not in h2-1.2.147) I would be willing to do some early
testing if you like..

Thank you,
Ken

On Nov 10, 12:16 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> Do you have an estimate of when this might ready for testing?

Sorry, I don't have an estimate right now. It looks like it's a bigger
change, sorry.

Regards,
Thomas

[kensystem]

Hi Thomas,

I hope you are well.

I thought I would ask since it's been a while; would you be able to give a timeframe for this fix?

I have some middleware software that dynamically builds the queries, and getting the author to reverse the tables in a join (for h2's sake) might be met with resistance. Also it seems likely that other users are encountering this and (silently) concluding h2 is not a viable alternative to their other database.. I hope it deserve a top spot on the todo list(?)

Regards,
Ken

[kensystem]

Thomas, I saw some performance improvements listed in the h2-1.3.152 changelog, so I tried running this query with that version to see if the automatic swapping happens.. it appears to be the same: 60 ms vs 90 seconds..

Again let me know if I can help in some way with testing builds, or re-sending that hi row count test case.

Thanks,
Ken

[Thomas Mueller]

Hi,

> I thought I would ask since it's been a while; would you be able to give a
> timeframe for this fix?

I'm sorry, I can't tell you right now. It's a relatively big change I
believe, and currently it's not a very high priority for me. I suggest
to re-write the query, and if you are using a tool such as Hibernate
then run the problematic query manually (that is, not using
Hibernate). I know it's not a nice solution. Maybe there is a way to
specify the join order within the tool?

> getting the author to reverse the tables in a join (for h2's sake) might be
> met with resistance.

I know, but I'm afraid that's the only solution I have.

> Also it seems likely that other users are encountering
> this and (silently) concluding h2 is not a viable alternative to their other
> database.. I hope it deserve a top spot on the todo list(?)

Well, other things that are important as well. There are a lot of
possible optimizations in H2, but I also believe tools should have an
option to override the generated statement in some way, specially for
complex statements.

Regards,
Thomas

[kensystem]

OK, thank you. I will investigate some of the workarounds you suggest.
I took the liberty of creating a issue for this since I didn't notice
one, and helping to keep it from getting lost in the shuffle.

I thank you for your time, and a providing a truly *outstanding* piece
of software!!!

Ken

On Mar 11, 1:53 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[tuc70899]

Hello,

I was interested in reproducing this problem and designing a new test
case with LEFT JOIN vs. INNER JOIN. Would someone be able to send me
out the file for creation of your database?

Thanks,
Michael

On Mar 15, 2:16 am, kensystem <kensys...@gmail.com> wrote:
> OK, thank you. I will investigate some of the workarounds you suggest.
> I took the liberty of creating a issue for this since I didn't notice
> one, and helping to keep it from getting lost in the shuffle.
>
> I thank you for your time, and a providing a truly *outstanding* piece
> of software!!!
>
> Ken
>
> On Mar 11, 1:53 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
> wrote:
>
> > Hi,
>
> > > I thought I would ask since it's been a while; would you be able to give a
> > > timeframe for this fix?
>
> > I'm sorry, I can't tell you right now. It's a relatively big change I
> > believe, and currently it's not a very high priority for me. I suggest
> > to re-write the query, and if you are using a tool such as Hibernate
> > then run the problematic query manually (that is, not using
> > Hibernate). I know it's not a nice solution. Maybe there is a way to

> > specify thejoinorder within the tool?
>
> > > getting the author to reverse the tables in ajoin(for h2's sake) might be

[Mike Goodwin]

Hi,

We also have an issue with slow left joins. For example a query which takes less than 1 second on average using an inner join will take consistently around 150 seconds when using a left join.

In some cases for the time being it is going to be preferable to just use an inner join to overcome the speed issues - since I cannot see how to rewrite the query to produce exactly the same results. We can do this because in these cases, generally speaking the 'right' part of the join is usually present. However since we also generate queries the issue will be deciding when we can do this ...

Anyway, h2 is of course generally great (I could write pages on this, but like the news, you don't get to hear much about what went well), however by its own high standards this seems to me like quite a serious deficiency, because these really are common types of queries.

For example in one of our cases we have a bunch of customers and we want to report sales figures against these customers. Ideally we would like to display all the customers along side various totals, (or 0 if no sales are present). I have pasted the output from the console below, showing the two versions of the queries (one fast, one slow) and the 'explains'.


Questions would be
- Is there an alternative way to rewrite the left join version of the query so that it is faster?
- Is there a timescale for a fix for this situation? What about sql hints?
- What would be the fix, or fixes in case I find the time to have a go myself? Intuitively it would seem that internally it should just do two queries - one an inner join report, the second essentially just listing the main table, and then merge the results to get the left join effect.

regards,

Mike

-------------------------------------------------------------------------------------------


EXPLAIN SELECT
  t.id AS vc0_0,
  CASE WHEN (t.id) IS NULL THEN null ELSE t."name" END AS vc0_1,
  ifnull(vn0_1.vc0_0,0) AS vc0_2
FROM "customer" AS t
INNER JOIN (
  SELECT
    sum(t."sale_quantity" * t."sale_price") as vc0_0,
    IFNULL(t_a1."customer",cast(0 as NUMERIC)) AS GROUPBY
  FROM "sales_transaction" AS t
    LEFT JOIN "customer_department" AS t_a1 ON t."customer_department"=t_a1.id
    LEFT JOIN "customer" AS t_a1_a0 ON t_a1."customer"=t_a1_a0.id
  WHERE (true)
  GROUP BY groupby
) AS vn0_1 ON t.id=vn0_1.groupby ;
PLAN 
SELECT
    T.ID AS VC0_0,
    CASEWHEN((T.ID IS NULL), NULL, T."name") AS VC0_1,
    IFNULL(VN0_1.VC0_0, 0) AS VC0_2
FROM (
    SELECT
        SUM(T."sale_quantity" * T."sale_price") AS VC0_0,
        IFNULL(T_A1."customer", 0) AS GROUPBY
    FROM PUBLIC."sales_transaction" T
        /* PUBLIC."sales_transaction".tableScan */
        /* WHERE TRUE
        */
    LEFT OUTER JOIN PUBLIC."customer_department" T_A1
        /* PUBLIC.PRIMARY_KEY_E: ID = T."customer_department" */
        ON T."customer_department" = T_A1.ID
    LEFT OUTER JOIN PUBLIC."customer" T_A1_A0
        /* PUBLIC.PRIMARY_KEY_24: ID = T_A1."customer" */
        ON T_A1."customer" = T_A1_A0.ID
    WHERE TRUE
    GROUP BY IFNULL(T_A1."customer", 0)
) VN0_1
    /* SELECT
        SUM(T."sale_quantity" * T."sale_price") AS VC0_0,
        IFNULL(T_A1."customer", 0) AS GROUPBY
    FROM PUBLIC."sales_transaction" T
        /++ PUBLIC."sales_transaction".tableScan ++/
        /++ WHERE TRUE
        ++/
    LEFT OUTER JOIN PUBLIC."customer_department" T_A1
        /++ PUBLIC.PRIMARY_KEY_E: ID = T."customer_department" ++/
        ON T."customer_department" = T_A1.ID
    LEFT OUTER JOIN PUBLIC."customer" T_A1_A0
        /++ PUBLIC.PRIMARY_KEY_24: ID = T_A1."customer" ++/
        ON T_A1."customer" = T_A1_A0.ID
    WHERE TRUE
    GROUP BY IFNULL(T_A1."customer", 0)
     */
INNER JOIN PUBLIC."customer" T
    /* PUBLIC.PRIMARY_KEY_24: ID = VN0_1.GROUPBY */
    ON 1=1
WHERE T.ID = VN0_1.GROUPBY
(1 row, 24 ms)


SELECT
  t.id AS vc0_0,
  CASE WHEN (t.id) IS NULL THEN null ELSE t."name" END AS vc0_1,
  ifnull(vn0_1.vc0_0,0) AS vc0_2
FROM "customer" AS t
INNER JOIN (
  SELECT
    sum(t."sale_quantity" * t."sale_price") as vc0_0,
    IFNULL(t_a1."customer",cast(0 as NUMERIC)) AS GROUPBY
  FROM "sales_transaction" AS t
    LEFT JOIN "customer_department" AS t_a1 ON t."customer_department"=t_a1.id
    LEFT JOIN "customer" AS t_a1_a0 ON t_a1."customer"=t_a1_a0.id
  WHERE (true)
  GROUP BY groupby
) AS vn0_1 ON t.id=vn0_1.groupby ;


VC0_0      VC0_1      VC0_2 
1894467330751070213    Customer 1    4080.16
1894467330751070214    Customer 2    1049.12
   ...
1894467330751070487    Customer 274    107.82
1894467330751070488    Customer 275    306.32
(275 rows, 2085 ms)




EXPLAIN SELECT
  t.id AS vc0_0,
  CASE WHEN (t.id) IS NULL THEN null ELSE t."name" END AS vc0_1,
  ifnull(vn0_1.vc0_0,0) AS vc0_2
FROM "customer" AS t
LEFT JOIN (
  SELECT
    sum(t."sale_quantity" * t."sale_price") as vc0_0,
    IFNULL(t_a1."customer",cast(0 as NUMERIC)) AS GROUPBY
  FROM "sales_transaction" AS t
    LEFT JOIN "customer_department" AS t_a1 ON t."customer_department"=t_a1.id
    LEFT JOIN "customer" AS t_a1_a0 ON t_a1."customer"=t_a1_a0.id
  WHERE (true)
  GROUP BY groupby
) AS vn0_1 ON t.id=vn0_1.groupby ;
PLAN 
SELECT
    T.ID AS VC0_0,
    CASEWHEN((T.ID IS NULL), NULL, T."name") AS VC0_1,
    IFNULL(VN0_1.VC0_0, 0) AS VC0_2
FROM PUBLIC."customer" T
    /* PUBLIC."customer".tableScan */
LEFT OUTER JOIN (
    SELECT
        SUM(T."sale_quantity" * T."sale_price") AS VC0_0,
        IFNULL(T_A1."customer", 0) AS GROUPBY
    FROM PUBLIC."sales_transaction" T
        /* PUBLIC."sales_transaction".tableScan */
        /* WHERE TRUE
        */
    LEFT OUTER JOIN PUBLIC."customer_department" T_A1
        /* PUBLIC.PRIMARY_KEY_E: ID = T."customer_department" */
        ON T."customer_department" = T_A1.ID
    LEFT OUTER JOIN PUBLIC."customer" T_A1_A0
        /* PUBLIC.PRIMARY_KEY_24: ID = T_A1."customer" */
        ON T_A1."customer" = T_A1_A0.ID
    WHERE TRUE
    GROUP BY IFNULL(T_A1."customer", 0)
) VN0_1
    /* SELECT
        SUM(T."sale_quantity" * T."sale_price") AS VC0_0,
        IFNULL(T_A1."customer", 0) AS GROUPBY
    FROM PUBLIC."sales_transaction" T
        /++ PUBLIC."sales_transaction".tableScan ++/
    LEFT OUTER JOIN PUBLIC."customer_department" T_A1
        /++ PUBLIC.PRIMARY_KEY_E: ID = T."customer_department" ++/
        ON T."customer_department" = T_A1.ID
    LEFT OUTER JOIN PUBLIC."customer" T_A1_A0
        /++ PUBLIC.PRIMARY_KEY_24: ID = T_A1."customer" ++/
        ON T_A1."customer" = T_A1_A0.ID
    WHERE IFNULL(T_A1."customer", 0) IS ?1
    GROUP BY IFNULL(T_A1."customer", 0): GROUPBY = T.ID
     */
    ON T.ID = VN0_1.GROUPBY
(1 row, 12 ms)




SELECT
  t.id AS vc0_0,
  CASE WHEN (t.id) IS NULL THEN null ELSE t."name" END AS vc0_1,
  ifnull(vn0_1.vc0_0,0) AS vc0_2
FROM "customer" AS t
LEFT JOIN (
  SELECT
    sum(t."sale_quantity" * t."sale_price") as vc0_0,
    IFNULL(t_a1."customer",cast(0 as NUMERIC)) AS GROUPBY
  FROM "sales_transaction" AS t
    LEFT JOIN "customer_department" AS t_a1 ON t."customer_department"=t_a1.id
    LEFT JOIN "customer" AS t_a1_a0 ON t_a1."customer"=t_a1_a0.id
  WHERE (true)
  GROUP BY groupby
) AS vn0_1 ON t.id=vn0_1.groupby ;


VC0_0      VC0_1      VC0_2 
1894467330751070213    Customer 1    4080.16
1894467330751070214    Customer 2    1049.12
   ....
1894467330751070208    Customer (No Data)    0
   ...
1894467330751070487    Customer 274    107.82
1894467330751070488    Customer 275    306.32
(281 rows, 163188 ms)