By the way, if you set the documenttype with an unexisted value,
the query would check all the records, and it would run slower than the
original one.
It depends on the values in your table.
It seems that the documenttype of all the records with the smallest 1000
ids is all 4.
So, the query ends after doing nest-loop 1000 times.
2015-09-01 18:51 GMT+09:00 twoflower :
> 林士博 wrote
> > I think you should try putting the precomputed boolean temp_
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 1000 records.
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
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
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.
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
