Postgresql – Best way of finding rows referencing a given id on PostgreSQL

join;performancepostgresqlpostgresql-performanceunion

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE)

For that, I have a piece of code that will give me a list of all (table, column) referencing that row's id. Then all I want to know is if any of those (table, column) have my row's id there, which would prevent its deletion.

I found 3 ways to do that, and all producing similar results on EXPLAIN ANALYZE on a very large database. I'll paste here some real examples:

The first one is to make a UNION of each (table, column) and check if the id is there (if it's null at the end, there's no references):

  (SELECT client_category_price.sellable_id
   FROM client_category_price
   WHERE client_category_price.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT commission_source.sellable_id
   FROM commission_source
   WHERE commission_source.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT loan_item.sellable_id
   FROM loan_item
   WHERE loan_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT product.sellable_id
   FROM product
   WHERE product.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT product_history.sellable_id
   FROM product_history
   WHERE product_history.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT purchase_item.sellable_id
   FROM purchase_item
   WHERE purchase_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT receiving_order_item.sellable_id
   FROM receiving_order_item
   WHERE receiving_order_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT returned_sale_item.sellable_id
   FROM returned_sale_item
   WHERE returned_sale_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT sale_item.sellable_id
   FROM sale_item
   WHERE sale_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT service.sellable_id
   FROM service
   WHERE service.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT stock_decrease_item.sellable_id
   FROM stock_decrease_item
   WHERE stock_decrease_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT transfer_order_item.sellable_id
   FROM transfer_order_item
   WHERE transfer_order_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT work_order.sellable_id
   FROM work_order
   WHERE work_order.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)
UNION
  (SELECT work_order_item.sellable_id
   FROM work_order_item
   WHERE work_order_item.sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1)

The second one is to make a cross join and return a "big tuple" containing those same ids, and then test if all of them are null:

SELECT client_category_price.sellable_id, commission_source.sellable_id, loan_item.sellable_id, product.sellable_id, product_history.sellable_id, purchase_item.sellable_id,
       receiving_order_item.sellable_id, returned_sale_item.sellable_id, sale_item.sellable_id, service.sellable_id, stock_decrease_item.sellable_id, transfer_order_item.sellable_id,
       work_order.sellable_id, work_order_item.sellable_id
FROM sellable
LEFT JOIN client_category_price ON client_category_price.sellable_id = sellable.id
LEFT JOIN commission_source ON commission_source.sellable_id = sellable.id
LEFT JOIN loan_item ON loan_item.sellable_id = sellable.id
LEFT JOIN product ON product.sellable_id = sellable.id
LEFT JOIN product_history ON product_history.sellable_id = sellable.id
LEFT JOIN purchase_item ON purchase_item.sellable_id = sellable.id
LEFT JOIN receiving_order_item ON receiving_order_item.sellable_id = sellable.id
LEFT JOIN returned_sale_item ON returned_sale_item.sellable_id = sellable.id
LEFT JOIN sale_item ON sale_item.sellable_id = sellable.id
LEFT JOIN service ON service.sellable_id = sellable.id
LEFT JOIN stock_decrease_item ON stock_decrease_item.sellable_id = sellable.id
LEFT JOIN transfer_order_item ON transfer_order_item.sellable_id = sellable.id
LEFT JOIN work_order ON work_order.sellable_id = sellable.id
LEFT JOIN work_order_item ON work_order_item.sellable_id = sellable.id
WHERE sellable.id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1

Note that the database is so big that if I don't put the LIMIT 1 at the end, the query would take a very long time to execute, but with the limit, it take as long as the UNION above.

And there's the third option: I'm actually executing this from python. So I could execute a COUNT on each of those UNION queries and if one of them returns something > 0, I already know there exists a reference for the row. On the best case I would execute one COUNT and at the worst case all of those COUNTS.

Since all those options produce very insignificant differences (both take +/- 0.300ms with about 1 million rows divided between those related tables), which one would be better to use? I mean, which would scale better?

Best Answer

I suggest your first option, with two improvements and some simplifications.

(
SELECT 1      -- irrelevant what you select here
FROM   client_category_price
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1      -- may be redundant
)
UNION ALL     -- not just UNION

  ...

UNION ALL
(
SELECT 1
FROM   work_order_item
WHERE  sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460'
LIMIT  1
)
LIMIT  1;      -- this one is crucial

Given that all you want to know is

if any of those (table, column) have my row's id there, which would prevent its deletion.

You don't need a full list of violating rows. Stop searching at the first one. All you need to do is add another LIMIT 1 at the end of the query. This way, Postgres skips rest of the query as soon as the first row is found. You probably don't need LIMIT 1 for each SELECT, just the one at the end. Test without, it may produce different query plans.
Use UNION ALL instead of UNION. Faster.
Some other simplifications.

Related Solutions

Mysql – get column from too many tables in thesql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

The best way to query a partitioned table in Oracle

Oracle will take care of partition pruning when using ** making one query: select * from table where field in ('A','B')

Here is an example:

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

explain plan for
select * from q1_sales_by_region
where state IN ('OR','FL')
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2523828506

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |    41 |     2   (0)| 00:00:01 |       |    |
|   1 |  PARTITION LIST INLIST|                    |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL   | Q1_SALES_BY_REGION |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATE"='NY' OR "STATE"='OR')

The KEY(INLIST) designation for the partition start and stop keys specifies that an IN list predicate.

Best Answer

Related Solutions

Mysql – get column from too many tables in thesql

The best way to query a partitioned table in Oracle

Related Question