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

Eric Raskin <eraskin@xxxxxxxxxxxx> · Wed, 4 Nov 2020 12:10:21 -0500

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@xxxxxxxxxxxx
Mt Kisco, NY 10549                                                                                              http://www.paslists.com