Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Matthew Bellew
erent values in > FIELD.FIELD_NAME > > Gerardo > > - 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 ta

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Alessandro Ferrucci
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

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Gerardo Herzig
" > 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 plan can be found here: > > &

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Gerardo Herzig
- 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 finall

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Johan Fredriksson
> 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

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Alessandro Ferrucci
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

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Alessandro Ferrucci
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

Re: [PERFORM] Slow query with 3 table joins

2017-04-25 Thread Johan Fredriksson
tis 2017-04-25 klockan 23:19 -0400 skrev Alessandro Ferrucci: > After about 40 inutes the slow query finally finished and the result > of the EXPLAIN plan can be found here: > > > https://explain.depesz.com/s/BX22 > > > Thanks, > Alessandro Ferrucci I'm not so familiar with the index implement

Re: [PERFORM] Slow query with 3 table joins

2017-04-25 Thread David Rowley
On 26 April 2017 at 15:19, Alessandro Ferrucci wrote: > After about 40 inutes the slow query finally finished and the result of the > EXPLAIN plan can be found here: > > https://explain.depesz.com/s/BX22 > Index Scan using field_unit_id_idx on field (cost=0.00..8746678.52 > rows=850149 width=8)

Re: [PERFORM] Slow query with 3 table joins

2017-04-25 Thread Alessandro Ferrucci
After about 40 inutes the slow query finally finished and the result of the EXPLAIN plan can be found here: https://explain.depesz.com/s/BX22 Thanks, Alessandro Ferrucci On Tue, Apr 25, 2017 at 11:10 PM, Alessandro Ferrucci < alessandroferru...@gmail.com> wrote: > Hello - I am migrating a curre