Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Have you run an explain on this query?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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
  8. #5
  9. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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
    PostgreSQL, it's what's for dinner...
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    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.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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 |
    +------------------+----------------------------------+----------+
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Is upgrading your version a possibility? What did the project DBA say? Have the table been optimized? What does the cardinality look like?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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"
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    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.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    13
    hello,

    i think you dont need a subquery in you query.

    you can try this :

    Code:
    SELECT * 
    FROM mediabarcodes 
    WHERE
    ((media_type <> 4 AND media_type <> 5) 
    OR (media_size <> 15 AND media_size <> 10) 
    OR (sheets_per_media_cassette <> 100 AND sheets_per_media_cassette <> 20))
    OR
    (media_type <> 3 
    OR (media_size <> 15 AND media_size <> 1 AND media_size <> 2) 
    OR (sheets_per_media_cassette <> 80 AND sheets_per_media_cassette <> 20))
    OR
    (media_type <> 1 
    OR (media_size <> 1 AND media_size <> 2) 
    OR sheets_per_media_cassette <> 80)
    ...realy not sure but i think you can keep this way
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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...
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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!
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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.

    Code:
    SELECT bar_code, media_type, media_size, sheets_per_media_cassette 
    FROM mediabarcodes
    WHERE (media_type = 4 OR media_type = 5
            AND (media_size <> 15 AND  media_size <> 10)
            AND (sheets_per_media_cassette <> 100 AND sheets_per_media_cassette <> 20)  )
    OR (media_type = 3
            AND (media_size <> 15 AND  media_size <> 1 AND media_size <> 2)
            AND (sheets_per_media_cassette <> 100 AND sheets_per_media_cassette <> 20))
    OR (media_type = 1
            AND (media_size <> 1 AND media_size <> 2)
            AND sheets_per_media_cassette <> 80)
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo