Planning time grows exponentially with levels of nested views
Hi, I assumed the cost for each nested VIEW layer would grow linear, but my testing shows it appears to grow exponentially: CREATE TABLE foo (bar int); INSERT INTO foo (bar) VALUES (123); DO $_$ DECLARE BEGIN CREATE OR REPLACE VIEW v1 AS SELECT * FROM foo; FOR i IN 1..256 LOOP EXECUTE format ( $$ CREATE OR REPLACE VIEW v%s AS SELECT * FROM v%s $$, i+1, i ); END LOOP; END $_$; EXPLAIN ANALYZE SELECT * FROM foo; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.004..0.004 rows=1 loops=1) Planning Time: 0.117 ms Execution Time: 0.011 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v1; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.003 rows=1 loops=1) Planning Time: 0.019 ms Execution Time: 0.015 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v2; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.002 rows=1 loops=1) Planning Time: 0.018 ms Execution Time: 0.011 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v4; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.002 rows=1 loops=1) Planning Time: 0.030 ms Execution Time: 0.013 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v8; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.002 rows=1 loops=1) Planning Time: 0.061 ms Execution Time: 0.016 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v16; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.003 rows=1 loops=1) Planning Time: 0.347 ms Execution Time: 0.027 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v32; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.003 rows=1 loops=1) Planning Time: 2.096 ms Execution Time: 0.044 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v64; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.004..0.005 rows=1 loops=1) Planning Time: 14.981 ms Execution Time: 0.119 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v128; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.004..0.004 rows=1 loops=1) Planning Time: 109.407 ms Execution Time: 0.187 ms (3 rows) EXPLAIN ANALYZE SELECT * FROM v256; QUERY PLAN - Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.006..0.007 rows=1 loops=1) Planning Time: 1594.809 ms Execution Time: 0.531 ms (3 rows)
Re: Vulnerability PostgreSQL 11.2
On Fri, 2021-04-16 at 10:57 -0700, Moris Rumenov Vrachovski wrote: > Thank you Laurenz! I have a second question. I downloaded postgreSQl 11.11 to > upgrade from 11.2. > I am having trouble upgrading postgresql, it is trying to install a new > postgreSQL instead of upgrading. That sounds right. A minor upgrade consists of installing the new files and replacing the old ones, then restarting the server. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Storing state machine
Hello, We have a workflow when we receive events into the service. But we don't have a way to choreograph or orchestrate the workflow. The services are all independent and receive and respond to events. Since there is no order imposed by the event queues I was thinking of storing a simple state machine in the table. 1. Order PENDING 2. Order line 1 PENDING 3. Order line 2 PENDING 4. Order line 1 PROCESSED 5. Order line 2 PROCESSED 6. Order PROCESSED Order and Order lines can be PROCESSED in any order. But at the end all lines should be PROCESSED and then the order is also PROCESSED. I won't be able to use any PostgreSql functions because we don't depend on those features. Are there any PostgreSql features that could support this pattern ? Is it just like any other eventually consistent pattern ? Thanks
Re: Storing state machine
Mohan Radhakrishnan writes: > Hello, >We have a workflow when we receive events into the service. But we > don't have a way to choreograph or orchestrate the workflow. The > services are all independent and receive and respond to events. > > Since there is no order imposed by the event queues I was thinking of storing > a simple state machine in the table. > > 1. Order PENDING > 2. Order line 1 PENDING > 3. Order line 2 PENDING > 4. Order line 1 PROCESSED > 5. Order line 2 PROCESSED > 6. Order PROCESSED > > Order and Order lines can be PROCESSED in any order. But at the end all lines > should be PROCESSED and then the order is also PROCESSED. > I won't be able to use any PostgreSql functions because we don't depend on > those features. > > Are there any PostgreSql features that could support this pattern ? Is it > just like any other eventually consistent pattern ? > What you appear to have here is two entities - orders and order items. An order entity has a 'state' (pending/processed) and is linked to one or more order items which in turn have a state. The information about order state could be derived rather than actually stored i.e. an order is pending if any of its order items are pending and is processed if all of its order items are processed. At a minimum, storing the order item state would be sufficient and a basic sql statement would be able to tell you what the state of an order is. In general, you don't want to store duplicate or redundant information as this can be a source of anomalies. (e.g. order state is not updated to 'processed' when all items are processed or is updated to processed, but then another item is added and for some reason, the state is not switched back to pending etc). in general, it is usually a mistake or poor design to use one table to represent different 'entities'. That is a 'bit bucket' approach which really degrades the ability of the database to do what it is good at - managing entities and their relationships. >From the description you have provided, everything you need can be easily managed with basic SQL statements - no need for functions or stored procedures. All you would need is an SQL statement to insert a new order item, an SQL statement to update the state of an item and a SQL statement to report on the state of an order. Your requirement statement is extremely simple and I suspect you have glossed over some of the constraints/requirements, but based on what you have written, your requirement seems to be trivial and easily satisfied with basic database facilities. -- Tim Cross
