Adding nextval() to a select caused hang/very slow execution

Hello (TL;DR):

Noob here, so please bear with me. The SQL I'm presenting is part of a
larger PL/PGSQL script that generates generic "counts" from tables in our
database. This is code converted from an Oracle database that we recently
migrated from.

I have a strange situation where a base query completes in about 30 seconds
but if I add a nextval() call to the select it never completes. There are
other processes running that are accessing the same sequence, but I thought
that concurrency was not an issue for sequences (other than skipped
values).

We are running on Google Cloud SQL v12 (I believe it is currently 12.3).
We are configured with a failover replica. The VM configured is 8 vCPUs
and 16GB of RAM. PgAdmin shows that our cache hit rates are around 99%, as
does this SQL:

SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;

heap_read | heap_hit | ratio
------------+--------------+------------------------
1558247211 | 156357754256 | 0.99013242992145017164
(1 row)

We run autovacuum. work_mem = 131072.

The base SQL without the nextval() call and plan are at:
https://explain.depesz.com/s/T3Gn

While the performance is not the fastest, 30 seconds for the execution is
acceptable in our application. We only run this once a month. I am not
looking to optimize the query as it stands (yet). The only change that
causes it to be extremely slow or hang (can't tell which) is that I changed
the select from:

select unnest(....

to

select nextval('sbowner.idgen'), unnest(....

Here are all the tables/views involved, as requested in the "Slow Query
Questions" FAQ.

I am aware that the structure of these views leaves A LOT to be desired,
but I don't think they have a bearing on this issue, since the addition of
nextval() is the problem. We are going to restructure all of this and
remove many layers eventually. Before subjecting the reader to this long
list of views, here's the theory. We have customers and orders for various
products. Those products are grouped together into "lists" that can be
selected. Some products could be in more than one list. At the same time,
some products are "pre-release", so they are only reported internally and
omitted from these counts. Next, some of the orders are "autoship",
meaning that the customer has subscribed to receive the product
automatically. Any customer with an autoship in the same category of
product is to be omitted from these counts.

As there are many of these "lists", there was a naming scheme created in
the Oracle database we converted from. Oracle allows synonyms so that we
could create one view and rename it to match the naming scheme. PostgreSQL
does not allow that, so instead we had to create views on views to keep the
names intact.

The views/tables involved are:

Table "lruser.count_tempcols"
Column | Type | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+---------
typecode | character(1) | | |
disporder | smallint | | |
mindate | timestamp without time zone | | |
maxdate | timestamp without time zone | | |
fmtdate | character varying(10) | | |

This table holds generated date ranges for the counts to be generated by
the main query. Records are inserted once at the start of execution of the
script, followed by creating an index:

create index count_tempcols_ndx on count_tempcols(mindate, maxdate,
fmtdate, disporder, typecode);
analyze count_tempcols;

It is actually created as a temporary table, but that makes it hard to
present here. ;-)

Here is fortherb_indcounts and the entire view chain:

fortherb_indcounts - an extract of the data that we actually generate the
counts from

View "lruser.fortherb_indcounts"
Column | Type | Collation | Nullable | Default |
Storage | Description
----------+-----------------------------+-----------+----------+---------+----------+-------------
id | bigint | | | |
plain |
state | character varying(2) | | | |
extended |
zip | character varying(6) | | | |
extended |
rtype | bpchar | | | |
extended |
sexcode | character(1) | | | |
extended |
origdate | timestamp without time zone | | | |
plain |
hotline | timestamp without time zone | | | |
plain |
numpurch | bigint | | | |
plain |
scf | text | | | |
extended |
phone | character varying(16) | | | |
extended |
paymeth | character varying(4) | | | |
extended |
email | character varying(40) | | | |
extended |
itemcode | character varying(10) | | | |
extended |
View definition:
SELECT c.id,
c.state,
c.zip,
c.rtype,
c.sexcode,
c.origdate,
date_trunc('day'::text, t.hotlinedate) AS hotline,
c.numpurch,
substr(c.zip::text, 1, 3) AS scf,
c.phone,
t.paymeth,
c.email,
t.itemcode
FROM fortherb_ind c,
"fortherb_ind$rent$tracking" t
WHERE c.id = t.pasid;

This next table is a list of "rentable" transactions - those transactions
that we want to actually count (omitting pre-release and autoships).

View "lruser.fortherb_ind$rent$tracking"
Column | Type | Collation | Nullable | Default
| Storage | Description
-------------+-----------------------------+-----------+----------+---------+----------+-------------
pasid | bigint | | |
| plain |
jobid | bigint | | |
| plain |
itemcode | character varying(10) | | |
| extended |
hotlinedate | timestamp without time zone | | |
| plain |
updatedate | timestamp without time zone | | |
| plain |
rectype | character(1) | | |
| extended |
autoship | character(1) | | |
| extended |
subid | character varying(20) | | |
| extended |
amount | numeric(10,2) | | |
| main |
sourcecode | character varying(20) | | |
| extended |
ordernum | character varying(20) | | |
| extended |
paymeth | character varying(4) | | |
| extended |
View definition:
SELECT "fortherb$rent$i_tracking".pasid,
"fortherb$rent$i_tracking".jobid,
"fortherb$rent$i_tracking".itemcode,
"fortherb$rent$i_tracking".hotlinedate,
"fortherb$rent$i_tracking".updatedate,
"fortherb$rent$i_tracking".rectype,
"fortherb$rent$i_tracking".autoship,
"fortherb$rent$i_tracking".subid,
"fortherb$rent$i_tracking".amount,
"fortherb$rent$i_tracking".sourcecode,
"fortherb$rent$i_tracking".ordernum,
"fortherb$rent$i_tracking".paymeth
FROM "fortherb$rent$i_tracking";

This is the same table as the previous, just with a different name (Oracle
synonym simulation):

View "lruser.fortherb$rent$i_tracking"
Column | Type | Collation | Nullable | Default
| Storage | Description
-------------+-----------------------------+-----------+----------+---------+----------+-------------
pasid | bigint | | |
| plain |
jobid | bigint | | |
| plain |
itemcode | character varying(10) | | |
| extended |
hotlinedate | timestamp without time zone | | |
| plain |
updatedate | timestamp without time zone | | |
| plain |
rectype | character(1) | | |
| extended |
autoship | character(1) | | |
| extended |
subid | character varying(20) | | |
| extended |
amount | numeric(10,2) | | |
| main |
sourcecode | character varying(20) | | |
| extended |
ordernum | character varying(20) | | |
| extended |
paymeth | character varying(4) | | |
| extended |
View definition:
SELECT i.pasid,
i.jobid,
i.itemcode,
i.hotlinedate,
i.updatedate,
i.rectype,
i.autoship,
i.subid,
i.amount,
i.sourcecode,
i.ordernum,
i.paymeth
FROM glm.glmitems i
WHERE (i.prodtable::text = ANY (ARRAY['fortherb'::character
varying::text, 'fortherb2'::character varying::text])) AND NOT (EXISTS (
SELECT NULL::text AS text
FROM glmprods
WHERE glmprods.prerelease IS NOT NULL AND
glmprods.prerelease::text <> ''::text AND glmprods.prodcode::text =
i.itemcode::text)) AND (i.rectype = ANY (ARRAY['2'::bpchar, '3'::bpchar]))
AND NOT (EXISTS ( SELECT NULL::text AS text
FROM "fortherb$rent$i_track_as" a
WHERE a.pasid = i.pasid));

This is a view of all transactions with the "list" they belong to appended,
as well as a pre-release code.

View "glm.glmitems"
Column | Type | Collation | Nullable | Default
| Storage | Description
-------------+-----------------------------+-----------+----------+---------+----------+-------------
pasid | bigint | | |
| plain |
jobid | bigint | | |
| plain |
itemcode | character varying(10) | | |
| extended |
hotlinedate | timestamp without time zone | | |
| plain |
updatedate | timestamp without time zone | | |
| plain |
rectype | character(1) | | |
| extended |
autoship | character(1) | | |
| extended |
subid | character varying(20) | | |
| extended |
amount | numeric(10,2) | | |
| main |
sourcecode | character varying(20) | | |
| extended |
ordernum | character varying(20) | | |
| extended |
paymeth | character varying(4) | | |
| extended |
itemid | bigint | | |
| plain |
prodtable | character varying | | |
| extended |
category | character varying(50) | | |
| extended |
subcategory | character varying(15) | | |
| extended |
prerelease | character(1) | | |
| extended |
View definition:
SELECT t.pasid,
t.jobid,
t.itemcode,
t.hotlinedate,
t.updatedate,
t.rectype,
t.autoship,
t.subid,
t.amount,
t.sourcecode,
t.ordernum,
t.paymeth,
t.itemid,
CASE
WHEN t.hotlinedate >= p.changedate THEN p.prodtable
ELSE p.prodtable_old
END AS prodtable,
p.category,
p.subcategory,
p.prerelease
FROM "glm$tracking" t
JOIN glm.glmproducts p ON t.itemcode::text = p.prodcode::text;

This is the base table of transactions:

Table "lruser.glm$tracking"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
pasid | bigint | | |
jobid | bigint | | |
itemcode | character varying(10) | | |
hotlinedate | timestamp without time zone | | |
updatedate | timestamp without time zone | | |
rectype | character(1) | | |
autoship | character(1) | | |
subid | character varying(20) | | |
amount | numeric(10,2) | | |
sourcecode | character varying(20) | | |
ordernum | character varying(20) | | |
paymeth | character varying(4) | | |
itemid | bigint | | |
Indexes:
"glm$tracking$countndx" btree (itemcode, pasid, rectype, hotlinedate)
"glm$tracking$ndx" btree (itemcode, hotlinedate, rectype, pasid)
"glm$tracking$prodndx" btree (itemcode, pasid, rectype, hotlinedate)
"glm$tracking$rent$ndx" btree (pasid, hotlinedate, itemcode) INCLUDE
(rectype)
Foreign-key constraints:
"glm$autoship$fk" FOREIGN KEY (subid) REFERENCES "glm$autoship"(subid)
"glm$cust$fk" FOREIGN KEY (pasid) REFERENCES glm(id)
"glm$tracking$prod$fk" FOREIGN KEY (itemcode) REFERENCES
glm.glmproducts(prodcode)
Triggers:
"glm$tracking$itemid" BEFORE INSERT OR UPDATE ON "glm$tracking" FOR
EACH ROW EXECUTE FUNCTION "trigger_fct_glm$tracking$itemid"()

This is an old version of our table of products sold - it should be
replaced by our newer "glmproducts" table.

Table "lruser.glmprods" *(OLD
VERSION OF GLMPRODUCTS - SHOULD BE REMOVED!)*
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
prodcode | character varying(10) | | not null | |
extended | |
prodtable | character varying(30) | | | |
extended | |
prerelease | character(1) | | | |
extended | |
category | character varying(50) | | | |
extended | |
Indexes:
"glmprods$pk" PRIMARY KEY, btree (prodcode)
Access method: heap

This is a view that identifies customers with autoships and the category
the product belongs to. Note that subscriptions can end, hence the
canceldate. We only want to omit customers with *active* subscription in
products of the same category.

View "lruser.category_autoship"
Column | Type | Collation | Nullable | Default
| Storage | Description
------------+-----------------------------+-----------+----------+---------+----------+-------------
category | character varying(50) | | |
| extended |
prodtable | character varying(30) | | |
| extended |
subid | character varying(20) | | |
| extended |
pasid | bigint | | |
| plain |
jobid | bigint | | |
| plain |
updatedate | timestamp without time zone | | |
| plain |
startdate | timestamp without time zone | | |
| plain |
canceldate | timestamp without time zone | | |
| plain |
itemcode | character varying(10) | | |
| extended |
View definition:
SELECT p.category,
p.prodtable,
a.subid,
a.pasid,
a.jobid,
a.updatedate,
a.startdate,
a.canceldate,
a.itemcode
FROM "glm$autoship" a,
glmprods p
WHERE a.itemcode::text = p.prodcode::text;

This is the base table of the actual autoship subscriptions.

Table "lruser.glm$autoship"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
subid | character varying(20) | | not null |
pasid | bigint | | |
jobid | bigint | | |
updatedate | timestamp without time zone | | |
startdate | timestamp without time zone | | |
canceldate | timestamp without time zone | | |
itemcode | character varying(10) | | |
Indexes:
"glm$autoship$pk" PRIMARY KEY, btree (subid)
"glm$autoship$catndx" btree (pasid, itemcode, canceldate)
"glm$autoship$prodndx" btree (itemcode, pasid, canceldate)
Foreign-key constraints:
"glm$autoship$prodfk" FOREIGN KEY (itemcode) REFERENCES
glm.glmproducts(prodcode)
Referenced by:
TABLE ""glm$tracking"" CONSTRAINT "glm$autoship$fk" FOREIGN KEY (subid)
REFERENCES "glm$autoship"(subid)

This is the new table of products.

Table "glm.glmproducts"
Column | Type | Collation | Nullable |
Default
---------------+-----------------------------+-----------+----------+---------
id | bigint | | |
prodcode | character varying(8) | | not null |
prodtable | character varying(20) | | not null |
category | character varying(50) | | |
prodtable_old | character varying(30) | | |
category_old | character varying(50) | | |
prodname | character varying(30) | | |
broker | character varying(20) | | |
prerelease | character(1) | | |
exclude | character(1) | | |
changedate | timestamp without time zone | | |
subcategory | character varying(15) | | |
changedate_ih | timestamp without time zone | | |
Indexes:
"glmproducts$pk" PRIMARY KEY, btree (prodcode)
Referenced by:
TABLE ""glm$autoship"" CONSTRAINT "glm$autoship$prodfk" FOREIGN KEY
(itemcode) REFERENCES glm.glmproducts(prodcode)
TABLE ""glm$tracking"" CONSTRAINT "glm$tracking$prod$fk" FOREIGN KEY
(itemcode) REFERENCES glm.glmproducts(prodcode)
Triggers:
"aur$glmproducts" AFTER UPDATE ON glm.glmproducts FOR EACH ROW EXECUTE
FUNCTION glm."trigger_fct_aur$glmproducts"()
"bdr$glmproducts" BEFORE DELETE ON glm.glmproducts FOR EACH ROW EXECUTE
FUNCTION glm."trigger_fct_bdr$glmproducts"()
"biur$glmproducts" BEFORE INSERT OR UPDATE ON glm.glmproducts FOR EACH
ROW EXECUTE FUNCTION glm."trigger_fct_biur$glmproducts"()

I believe that's the entire list of tables/views involved. Again, my
apologies for the long post. I presented all of this for completeness,
although I don't believe it has anything to do with the actual problem. :-/

Thanks in advance for any advice!

Eric Raskin

--

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

Eric H. Raskin
914-765-0500 x120 or *315-338-4461
(direct)*

Professional Advertising Systems Inc.
fax: 914-765-0500 or *315-338-4461 (direct)*

3 Morgan Drive #310
eraskin(at)paslists(dot)com

Mt Kisco, NY 10549
http://www.paslists.com