2017-02-16 5:38 GMT+01:00 Teddy Schmitz <teddy.schm...@actuallywedo.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@postgresql.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. > > >
