1

Background

We're running this query:

SELECT COUNT(*) AS "__count"
FROM "xfiler_document"
INNER JOIN "xfiler_tx" ON ("xfiler_document"."tx_id" = "xfiler_tx"."id")
WHERE (
    "xfiler_document"."added_on" <= '2015-06-28T23:59:59.999999-04:00'::timestamptz
    AND "xfiler_tx"."company_id" = 1
    AND "xfiler_document"."added_on" >= '2015-06-01T00:00:00-04:00'::timestamptz
)

The result of EXPLAIN ANALYZE is @ https://explain.depesz.com/s/voMo

The table is large (+30 million records), so we've run into a performance problem with a report we're running (this can be seen at the URL above; +25 second query).

Question

It seems to me, based on the analyze results, that the performance issue here is due to the fact that Postgres is comparing the added_on values (step 4), and then re-checking them for some reason (step 3) for every row from the query's added_on time frame, rather than only checking the 18,515 rows that apparently match the company_id comparison in step 7.

Firstly, am I understanding / explaining the performance issue correctly?

Secondly, is there a way to solve this (other than denormalizing company_id)?

Database

  • Postgres 9.6.1

xfiler_document

       Column        |            Type             |                          Modifiers                           
---------------------+-----------------------------+--------------------------------------------------------------
 id                  | integer                     | not null default nextval('xfiler_document_id_seq'::regclass)
 tx_id               | integer                     | not null
 doc                 | character varying(255)      | 
 added_on            | timestamp with time zone    | not null

Indexes:
    "xfiler_document_pkey" PRIMARY KEY, btree (id)
    "xfiler_document_tx_id" btree (tx_id)
    "xfiler_document_added_on" btree (added_on)
Foreign-key constraints:
    "xfiler_document_tx_id_fkey" FOREIGN KEY (tx_id) REFERENCES xfiler_tx(id) DEFERRABLE INITIALLY DEFERRED

xfiler_tx

           Column           |           Type           |                       Modifiers                        
----------------------------+--------------------------+--------------------------------------------------------
 id                         | integer                  | not null default nextval('xfiler_tx_id_seq'::regclass)
 name                       | character varying(255)   | not null
 company_id                 | integer                  | not null

Indexes:
    "xfiler_tx_pkey" PRIMARY KEY, btree (id)
    "xfiler_tx_company_id" btree (company_id)
    "xfiler_tx_name" btree (name)
Foreign-key constraints:
    "xfiler_tx_company_id_fkey" FOREIGN KEY (company_id) REFERENCES company_company(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "xfiler_document" CONSTRAINT "xfiler_document_tx_id_fkey" FOREIGN KEY (tx_id) REFERENCES xfiler_tx(id) DEFERRABLE INITIALLY DEFERRED
10
  • I suggest you try an index on (tx_id, added_on) Commented Nov 15, 2016 at 16:03
  • Your description about the constraints/indexes is a bit confusing. Unless I'm reading something wrong, the query should return always a count of 1 (or 0). Yet you say that there are 18K rows involved. Please add the CREATE TABLE statements in the question. Commented Nov 15, 2016 at 16:05
  • The 18,515 rows in step 7 are rows of the xfiler_tx table, right? And the rows that it is finding by applying the added_on predicate are in the xfiler_document table. Then it matches the remaining rows from the two tables together after applying an initial filter to each of them.
    – mendosi
    Commented Nov 15, 2016 at 16:12
  • @ypercubeᵀᴹ I removed the errroneous UNIQUE CONSTRAINTs
    – orokusaki
    Commented Nov 15, 2016 at 16:13
  • Yes, the UNIQUE document (tx_id) seemed fishy. I suggest you edit the q and add the CREATE TABLE statements, including the indexes or the output of \d tablename, for both tables. And the version of Postgres. Commented Nov 15, 2016 at 16:14

1 Answer 1

1

The plan entry Recheck Cond: indicates what conditions will be used for the recheck, should a recheck be necessary. This will always be listed, even if no recheck is actually necessary at run time. (You can tell this because the Recheck Cond shows up even if you do a regular EXPLAIN, not an EXPLAIN ANALYZE, so this is part of the planning process, not the execution process.)

In your situation, all Heap Blocks were exact, meaning no recheck was necessary.

The real explanation for the slowness is probably that the Bitmap Heap Scan needs to read a lot of data from disk in order to find the rows it needs. You could check this by turning track_io_timing on and running EXPLAIN (ANALYZE, BUFFERS).

If that is indeed the issue, one solution would be to CLUSTER xfiler_document ON xfiler_document_added_on so that the data for a given date range are closer together on disk.

If your data is on a RAID, then increasing effective_io_concurrency could help.

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.