NOT IN syntax, query hangs when run

does postgreSQL not support the NOT IN syntax?

I tried to write this query and it totally hangs pgaccess:

SELECT *
FROM mediabarcodes
WHERE bar_code NOT IN (
SELECT bar_code
FROM mediabarcodes
WHERE (media_type = 4 OR media_type = 5)
AND (media_size = 15 OR media_size = 10)
AND (sheets_per_media_cassette = 100 OR sheets_per_media_cassette = 20)
)
OR bar_code NOT IN (
SELECT bar_code
FROM mediabarcodes
WHERE media_type = 3
AND (media_size = 15 OR media_size = 1 OR media_size = 2)
AND (sheets_per_media_cassette = 80 OR sheets_per_media_cassette = 20)
)
OR bar_code NOT IN (
SELECT bar_code
FROM mediabarcodes
WHERE media_type = 1
AND (media_size = 1 OR media_size = 2)
AND sheets_per_media_cassette = 80
)

did I do something wrong with this query? I don't think so

Have you run an explain on this query?

do you just run explain <query_name> ???

It actually does run but it takes about 10 minutes. That's not so bad I don't think, I am going to ask the DBA of this one if he could build an index on this table because that should help the performance a lot, I just dont' konw what to ask for index on because top-level query returns all columns.

also: I changed the query to be AND NOT IN in stead of OR NOT IN, which is now correct; it quadrupled the runtime though

What version of Postgres are you using? Unless you're using the latest development versions 7.4+ (CVS) the performance of IN is notoriously slow. This should be fixed in the latest version coming out soon.

However, unless you have an enormous amount of records that seems extroardinarily long. How are the tables indexed?

Also, try checking the PG mailing list archives for workarounds for using IN /NOT IN, or post there directly. If you do end up posting directly to the PG mailing list and get an answer make sure to post here as well so others can learn from this too.

-b

If you have a DBA available you should ask him to explain the query, breaking it down to see where the bottleneck is and verify that the query has indexes avaialbe to use.

should I be able to see its use of indices in the explain output?? I'm def. using an older version because on the web it says I should be able to write explain ANALYZE VERBOSE query
but mine doesn't support ANALYZE

there are indices on the table but they're certainly not helping! here's what it lists as the current indices on the table:

wpd_jregan=> \di mediabarcodes

Database = wpd_jregan
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
| jregan | pk_activationkeys | index |
| jregan | pk_customercontactinformation | index |
| jregan | pk_id | index |
| jregan | pk_last_bar_code_id | index |
| jregan | pk_media_bar_codes_id | index |
| jregan | pk_media_cassette_id | index |
| jregan | pk_media_lot_code_id | index |
| jregan | pk_media_size_id | index |
| jregan | pk_media_type_id | index |
| jregan | pk_media_vendor_id | index |
| jregan | pk_media_vendor_lot_id | index |
| jregan | pk_media_vendor_package_id | index |
| jregan | pk_shipping_carton_id | index |
| jregan | pk_size_type_and_number_cassett | index |
| jregan | pk_vendor_and_catalog_number | index |
+------------------+----------------------------------+----------+

it won't let me post the output of the explain plan because it has too many [] areas or something, but it's not very readable or helpful from what I can tell. From what I understand I should be building an index solely on bar_codes because that's the only field included in the select.

Is upgrading your version a possibility? What did the project DBA say? Have the table been optimized? What does the cardinality look like?

the DBA is not as familiar with DBMS's as I am, I am trying to find out about how he has structured the database overall, like all integrity constraints--it worries me when I suggest triggers to alleviate this problem of invalid records being entered, but he said we don't want to actually change or check, we just want a count of the invalid records in the table...

from what I can see in the explain plan output, the indices are not being used. This is the first issue I am going to find out about with the DBA. I don't believe upgrading is an option, but I will try...

The table does not even have over 150,000 records--10 minute queries seem VERY unreasonable to me, I've written queries on 15 million records in 4 tables that took less than 1 minute; I am unsure however what you mean by the "cardinality"

Since you have determined that indices are not being used that can be your first path to follow. It may be possible that the optimizer will not use indices due to the number of `OR` statements involved.

hello,

i think you dont need a subquery in you query.

you can try this :



...realy not sure but i think you can keep this way

I think yours would exclude everything that doesn't have the type or size that I wish, but I have to only exclude already-known valid combinations of type, size, and sheets_per_media_cassette...

I've made an index called types_sizes_sheets on those three columns, should I make three seperate indices? also, I re-ran the EXPLAIN with the index, and it is now using it but still the query takes a really long time to run!

trying to do it the most obvious way I guess was just not meant to be ... thx niico for making me look at the problem differently.