43438

PostgreSQL query is slow when using NOT IN

I have a PostgreSQL function that returns a query result to pgadmin results grid REALLY FAST. Internally, this is a simple function that uses a dblink to connect to another database and does a query return so that I can simply run

SELECT * FROM get_customer_trans();

And it runs just like a basic table query.

The issue is when I use the NOT IN clause. So I want to run the following query, but it takes forever:

SELECT * FROM get_customer_trans() WHERE user_email NOT IN (SELECT do_not_email_address FROM do_not_email_tbl);

How can I speed this up? Anything faster than a NOT IN clause for this scenario?

Answer1:

get_customer_trans() is not a table - probably some stored procedure, so query is not really trivial. You'd need to look at what this stored procedure really does to understand why it might work slow.

However, regardless of stored procedure behavior, adding following index should help a lot:

CREATE INDEX do_not_email_tbl_idx1 ON do_not_email_tbl(do_not_email_address);

This index lets NOT IN query to quickly return answer. However, NOT IN is known to have issues in older PostgreSQL versions - so make sure that you are running at least PostgreSQL 9.1 or later.

<strong>UPDATE</strong>. Try to change your query to:

SELECT t.* FROM get_customer_trans() AS t WHERE NOT EXISTS ( SELECT 1 FROM do_not_email_tbl WHERE do_not_email_address = t.user_email LIMIT 1 )

This query does not use NOT IN, and should work fast. I think that in PostgreSQL 9.2 this query should work as fast as one with NOT IN though.

Answer2:

Just do it this way:

SELECT * FROM get_customer_trans() as t1 left join do_not_email_tbl as t2 on user_email = do_not_email_address where t2.do_not_email_address is null

Recommend

  • Avoiding virtual functions on embedded target
  • Download data first then show it on TableViewcontroller
  • multiple colors on beanplot in R
  • jQuery UI .sortable() call is slow when applies to thousands of elements
  • how to handle large size of update query in mysql with laravel
  • Seamless scrolling text
  • How does CoreLocation locate the device?
  • Collect HostCPU utilisation and disk I/O speed using XenServer Java API
  • Merging a large number of files from one directory into a data frame in R
  • Pandas v0.20 returns NotImplemented when multiplying dataframe columns
  • Indexing datetime in MySQL
  • Unity 5.1 Animator Controller not transitioning
  • How to get file download speed (transfer rate) with php?
  • command line of process by name
  • SQL Server re-calculate or not?
  • Does the MySQL IN clause execute the subquery multiple times?
  • CodeIgniter URI Parameter is partially bypassing an “if” statement
  • Which open source license has no forking [closed]
  • Azure webjobs output logs indexing taking very long
  • opencv display image without x server
  • ViewController With Transparent Background Entering Current ViewController With Push Transition
  • ADO and msqli connections very slow
  • Marklogic : Query response time is very high
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • Display issues when we change from one jquery mobile page to another in firefox
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Different response to non-authenticated users and AJAX calls
  • TFS: Get latest causes slow project reloading
  • Menu Color Fade on Hover with Jquery
  • Perl system calls when running as another user using sudo
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • ActionScript 2 vs ActionScript 3 performance
  • SVN: Merging two branches together
  • Hibernate gives error error as “Access to DialectResolutionInfo cannot be null when 'hibernate.
  • Data Validation Drop Down Box Arrow Disappearing
  • Comma separated Values
  • Trying to get generic when generic is not available
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?