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

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 temporary (I'd say derived) column WHERE

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

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

2015-08-31 21:46 GMT+02:00 twoflower wrote: > 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 > ... ... and you've put an index on that new boolean column (say "updated")? CREATE INDEX index

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: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

On Mon, Aug 31, 2015 at 3:19 PM, twoflower wrote: > 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.

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

On Mon, Aug 31, 2015 at 3:03 PM, twoflower wrote: > 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" confi

[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] Re: [PERFORM] Query > 1000× slowdown after adding datetime comparison

On Mon, Aug 31, 2015 at 12:09 PM, twoflower wrote: > 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)

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

On 08/31/2015 06:09 PM, twoflower wrote: I have the following three tables: ... This is the query plan: If I remove the WHERE condition, it returns immediately. Am I doing something obviously wrong? Please share explain plans for both

[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

Re: [PERFORM] is there any way we can push join predicate into inner table

"=?gb18030?B?sKTM38jL?=" <2363541...@qq.com> writes: > So, the question is, is there any way we can push join predicate into inner > table ( we can disable merge join and hash join to get NL Join, but join > predicate is not able to push into inner table )? You probably need to turn on use_remo