Re: Problems with Greatest

2017-02-16 5:38 GMT+01:00 Teddy Schmitz <teddy(dot)schmitz(at)actuallywedo(dot)com>:

> As a quick follow up I just did an explain on the query,
>
>
> Aggregate (cost=258007258.87..258007258.88 rows=1 width=8)
> -> Nested Loop (cost=0.00..184292254.83 rows=14743000807 width=16)
> -> Seq Scan on t1 (cost=0.00..3796.41 rows=263141 width=8)
> -> Materialize (cost=0.00..1088.40 rows=56027 width=8)
> -> Seq Scan on t2 (cost=0.00..808.27 rows=56027 width=8)
>
>
> It seems it has to do a loop on 14 billion rows? Can someone explain why
> this would happen?
>

sure - you did Cartesian Product
https://www.tutorialspoint.com/sql/sql-cartesian-joins.htm

for bigger tables it should be pretty slow and it is expected behave

Regards

Pavel

>
> Thanks,
>
>
> Teddy
> ------------------------------
> *From:* Teddy Schmitz
> *Sent:* Thursday, February 16, 2017 12:32:41 PM
> *To:* pgsql-general(at)postgresql(dot)org
> *Subject:* Problems with Greatest
>
>
> Hello,
>
>
> I have a query using Greatest that hangs and never returns when called
> with two tables.
>
>
> Postgres Version: 9.6
>
>
> Tables
>
> t1{ id bigint }
>
>
> t2 { id bigint }
>
>
> they are sharing a sequence
>
>
> the query
>
> select greatest(max(t1.id), max(t2.id)) from t1, t2;
>
> The purpose was to call setval on the sequence after doing a bulk data load into the database. But this query never returns. I have tried it with various combinations,
>
> select greatest(max(t1.id), 6) from t1; -> This returns
>
> select greatest(max(t1.id), 6) from t1, t2; -> This never returns.
>
> The query does work if there is only a few hundred items between the tables but I'm importing about ~300,000 rows between the two tables. I looked at pg_stat_activity and it says the query is active
>
> I worked around this problem using a union all query but I'm wondering if this is a bug or I am just using greatest wrong.
>
>
>