This looks like the same optimizer problem that occasionally plagues our
customers. Whenever the estimated rows of a join==1, but the actual rows
is higher, the optimizer may choose very poor plans. I made some attempts
to fix. The very simple fix is to never estimate 1 for a join result.
Even u
Dave -
I had re-ran ANALYZE and VACUUM on all the tables and that did not seem to
have fixed the issue (the query still took a long time, however I did not
let it finish to produce a full EXPLAIN plan.
However - after creating an index on FIELD(FIELD_NAME,UNIT_ID) and now the
query runs very fast
Some other approaches you could try:
1) What about an hashed index? You could make
CREATE INDEX ON FIELD (unit_id, hashtext(field_name))
and changing your query accordingly
"where hashtext(FIELD.FIELD_NAME)=hashtext('SHEETS_PRESENT') "
2) Partitioning (not native yet, but can be si
- Mensaje original -
> De: "Alessandro Ferrucci"
> Para: pgsql-performance@postgresql.org
> Enviados: Miércoles, 26 de Abril 2017 0:19:37
> Asunto: Re: [PERFORM] Slow query with 3 table joins
>
>
>
> After about 40 inutes the slow query finally finished and the result
> of the EXPLAIN
> Hi Eskil -
>
>
> The I believe the id-field you're referring to is the UNIT.UNIT_ID, I
> could change this to a varchar, however that column is not used in the
> query in question, so that wouldn't have any effect on the query's
> performance.
Sorry, I did not notice that the column "unit_id
Hi Dave -
thank you very much for all this advice! I will try each of these and post
back results (some of this stuff, like creating the index, which is
happening now, takes a very long time).
Thanks again for all these pointers.
Cheers,
Alessandro
On Wed, Apr 26, 2017 at 12:12 AM, David Rowle
Hi Eskil -
The I believe the id-field you're referring to is the UNIT.UNIT_ID, I could
change this to a varchar, however that column is not used in the query in
question, so that wouldn't have any effect on the query's performance.
Just for curiosity - I have changed the ANSWER.ANS datatype to a
