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