Re: [PERFORM] Huge difference between ASC and DESC ordering

Thank you Jeff. Jeff Janes wrote > Did you force PostgreSQL to stop using the index on s.id? If not, do > that. If so, please post the EXPLAIN (analyze) of the plan it does switch > to. Yes, this finishes in 20 seconds, which is two times faster than *order by id asc*. Query plan: Jeff

Re: [PERFORM] Huge difference between ASC and DESC ordering

Thank you Jeff. There are 7 million rows satisfying fk_id_client = 20045. There is an index on fk_id_client, now I added a composite (fk_id_client, id) index but that did not help. I see the point of what you are saying, but still don't understand how these two situations (*asc* vs. *desc*) are n

[PERFORM] Huge difference between ASC and DESC ordering

I have the following query which takes 90 seconds to finish. *JOB_MEMORY* has 45 million rows, *JOB_MEMORY_STORAGE* has 50 000 rows. Query plan: As you can see, it is indeed using an index *JOB_MEMORY_id_desc* in a backward direction, but it is very slow. When I change ordering to *desc* in the

Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

林士博 wrote > I think you should try putting the precomputed boolean temp_eval column > to "EXTERNAL_TRANSLATION" r table. > > And if possible, try creating a conditional index on id where temp_eval is > true, > on "EXTERNAL_TRANSLATION" r table. > > So that, only check this index can get the top 1

Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

Stefan Keller wrote > So, if I'm understanding you correctly, we're talking solely about > following clause in the query you gave initially: > > WHERE doc.date_last_updated >= date(now() - '171:00:00'::interval) > which initially was > WHERE documenttype = 4 > and now is being replaced by a tempor

Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

I did not. I wanted to compare this query to the one I tried before, having *documenttype = 4* as the sole condition. That one was very fast and the *documenttype* was not indexed either. But this query, using the new temporary column, still runs, after 48 minutes... -- View this message in co

Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

David G Johnston wrote > What happens if you pre-compute the date condition and hard code it? I created a new boolean column and filled it for every row in DOCUMENT with *(doc.date_last_updated >= date(now() - '171:00:00'::interval))*, reanalyzed the table and modified the query to just compare th

[PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

And another thing which comes out as a little surprising to me - if I replace the *date_last_updated* condition with another one, say *doc.documenttype = 4*, the query finishes immediately. *documenttype* is an unindexed integer column. Here's the query plan:

[PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

Tomas Vondra-4 wrote > Please share explain plans for both the slow and the fast query. That > makes it easier to spot the difference, and possibly identify the cause. > > Also, what PostgreSQL version is this, and what are "basic" config > parameters (shared buffers, work mem)? I am running 9.

[PERFORM] Query > 1000× slowdown after adding datetime comparison

I have the following three tables: DOCUMENT id (index) documenttype date_last_updated: timestamp(6) (indexed) EXTERNAL_TRANSLATION_UNIT id (indexed) fk_id_document (indexed) EXTERNAL_TRANSLATION id (indexed) fk_id_translation_unit (indexed) Table sizes: DOCUMENT: 381 000 EXTERNA