0

We have an online platform where we sell eBooks. In the database, we have a "books" table, which contains the books' information. It also contains the book file, stored as BYTEA. In various pages, I need to check if a book's file exists in the database. But I have noticed that the pages load slowly, so I was wondering if it is being caused by the manner in which I am checking.

Currently, I am checking if "book_file IS NOT NULL". Does PostgreSQL read the whole file, in order to determine whether it is not null, which could be slowing things down? If so, is there another way to check if the file exists, without having to read the whole file?

I read up on some binary string functions on the PostgreSQL documentation and was wondering if functions like octet_length(string), substring(string [from int] [for int]) or get_bit(string, offset) might perform better?

Below is an example of a simplified query I am using to check for the existence of the file.

SELECT books.book_title
FROM books
WHERE books.book_file_pdf IS NOT NULL
ORDER BY books.book_id DESC

Does this kind of condition slow things down? If so, can this query be optimized, to check solely if the file column is not empty, without reading the file?

1 Answer 1

0

IS NULL / IS NOT NULL is the most efficient check for NULLness. You can speed it up with an index, if you really need to have it fast:

CREATE INDEX ON books (book_id) WHERE book_file_pdf IS NOT NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.