Postgresql optimize query with ‘where not in’ clause

insertperformancepostgresqlpostgresql-performance

I have query to select all rows from table1 that they are not in tabl2 then insert them in table2.
This query is taking log time to execute since I have millions of rows in table2.

Is there better way to optimize or rewrite this query to make it execute faster.

WITH t1 AS (
select *
from table1
where id  not in (select id table2))
insert into table2
select * from t1 ;

Best Answer

First you may try two things:

Do not use WITH because it's an optimization fence. As of PostgreSQL 10, the result of the CTE query will be materialized even if it would be better not to.
Use NOT EXISTS instead of NOT IN. Long explanation of why on stackoverflow: https://stackoverflow.com/questions/173041/not-in-vs-not-exists

 INSERT INTO table2 
 SELECT * FROM table1
 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id=table1.id);

Also, please study and share the result of an EXPLAIN of the query or if possible EXPLAIN ANALYZE.

PostgreSQL doesn't care what order you write things in

PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone.
The order in which joins are written is also ignored up to the configured join_collapse_limit; if there are more joins than that, it'll execute them in the order they're written.
Subqueries can be executed before or after the query that contains them, depending on what's fastest, so long as the subquery is executed before the outer query actually needs the information. Often in reality the subquery gets executed kind-of in the middle, or interleaved with the outer query.
There's no guarantee PostgreSQL will actually execute parts of the query at all. They can be completely optimized away. This is important if you call functions with side-effects.

PostgreSQL will transform your query

PostgreSQL will heavily transform queries while retaining the exact same effects, in order to make them run faster while not changing the results.

Terms outside a subquery can get pushed down into the subquery so they execute as part of the subquery not where you wrote them in the outer query
Terms in the subquery can be pulled up to the outer query so their execution is done as part of the outer query, not where you wrote them in the subquery
The subquery can, and often is, flattened into a join on the outer table. The same is true of things like EXISTS and NOT EXISTS queries.
Views get flattened into the query that uses the view
SQL functions often get inlined into the calling query
... and there are numerous other transformations made to queries, such as constant expression pre-evaluation, de-correlation of some subqueries, and all sorts of other planner/optimizer tricks.

In general PostgreSQL can massively transform and rewrite your query, to the point where each of these queries:

select my_table.*
from my_table
left join other_table on (my_table.id = other_table.my_table_id)
where other_table.id is null;

select *
from my_table
where not exists (
  select 1
  from other_table
  where other_table.my_table_id = my_table.id
);

select *
from my_table
where my_table.id not in (
  select my_table_id
  from other_table
  where my_table_id is not null
);

will usually all produce exactly the same query plan. (Assuming I didn't make any dumb mistakes in the above anyway).

It's not uncommon to try to optimize a query only to find that the query planner has already figured out the tricks you're trying and applied them automatically, so the hand-optimized version is no better than the original.

Limitations

The planner/optimizer is far from omnicient, and is limited by the requirement to be absolutely certain it can't change the effects of the query, the available data to make decisions with, the rules that've been implemented, and the CPU time it can afford to spend pondering the optimizations. For example:

The planner relies on statistics kept by ANALYZE (usually via autovacuum). If these are outdated, the plan choice can be bad.
The statistics are only a sample, so they can be misleading due to sampling effects, especially if too small a sample is taken. Bad plan choices can result.
The statistics don't keep track of some kinds of data about the table, like correlations between columns. This can lead the planner to make bad decisions when it assumes things are independent when they aren't.
The planner relies on cost parameters like random_page_cost to tell it the relative speed of various operations on the particular system it's installed on. These are only guides. If they're badly wrong they can lead to poor plan choices.
Any subquery with a LIMIT or OFFSET cannot be flattened or be subject to pullup / pushdown. This doesn't mean it'll execute before all parts of the outer query, though, or even that it'll execute at all.
CTE terms (the clauses in a WITH query) are always executed in their entirety, if they're executed at all. They can't be flattened, and terms can't be pushed up or pulled down across the CTE term barrier. CTE terms are always executed before the final query. This is non-SQL-standard behaviour, but it's documented as how PostgreSQL does things.
PostgreSQL has a limited ability to optimize across queries on foreign tables, security_barrier views, and certain other special kinds of relation
PostgreSQL won't inline a function written in anything except plain SQL, nor do pullup/pushdown between it and the outer query.
The planner/optimizer is really dumb about selecting expression indexes, and about trivial data type differences between index and expression.

Tons more, too.

Your query

In the case of your query:

select 1 
from workdays day
where day.date_day >= '2014-10-01' 
    and day.date_day <= '2015-09-30' 
    and day.offer_id in (
        select offer.offer_day 
        from offer  
        inner join province on offer.id_province = province.id_province  
        inner join center cr on cr.id_cr = province.id_cr 
        where upper(offer.code_status) <> 'A' 
            and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557') 
            and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
    )

nothing stops it from being flattened into a simpler query with an extra set of joins, and it very likely will be.

It'll probably turn out something like (untested, obviously):

select 1 
from workdays day
inner join offer on day.offer_id = offer.offer_day
inner join province on offer.id_province = province.id_province  
inner join center cr on cr.id_cr = province.id_cr 
where upper(offer.code_status) <> 'A' 
   and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557') 
   and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
   and day.date_day >= '2014-10-01' 
   and day.date_day <= '2015-09-30';

PostgreSQL will then optimise the join order and join methods based on its selectivity and row count estimates and the available indexes. If these reasonably reflect reality then it'll do the joins and run the where clause entries in whatever order is best - often mixing them together, so it does a bit of this, then a bit of that, then comes back to the first part, etc.

How to see what the optimizer did

You can't see the SQL that PostgreSQL optimizes your query into, because it converts the SQL to an internal query tree representation then modifies that. You can dump the query plan and compare it to other queries.

There's no way to "deparse" that query plan or the internal plan tree back to SQL.

http://explain.depesz.com/ has a decent query plan helper. If you're totally new to query plans etc (in which case I'm amazed you made it this far through this post) then PgAdmin has a graphical query plan viewer that provides much less information but is simpler.

Best Answer

Related Solutions

MySQL Insert into two tables using new IDs

Postgresql – Are WHERE clauses applied in the order they are written

PostgreSQL doesn't care what order you write things in

PostgreSQL will transform your query

Limitations

Your query

How to see what the optimizer did

Related reading:

Related Question