Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Matthew Bellew
ited different values in > FIELD.FIELD_NAME > > Gerardo > > - Mensaje original - > > De: "Alessandro Ferrucci" <alessandroferru...@gmail.com> > > Para: pgsql-performance@postgresql.org > > Enviados: Miércoles, 26 de Abril 2017 0:19:37 >

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

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Gerardo Herzig
essandroferru...@gmail.com> > 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 ca

Re: [PERFORM] Slow query with 3 table joins

2017-04-26 Thread Gerardo Herzig
- Mensaje original - > De: "Alessandro Ferrucci" <alessandroferru...@gmail.com> > 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 4

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

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

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-26 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

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

[PERFORM] Slow query with 3 table joins

2017-04-25 Thread Alessandro Ferrucci
Hello - I am migrating a current system to PostgreSQL and I am having an issue with a relatively straightforward query being extremely slow. The following are the definitions of the tables: CREATE TABLE popt_2017.unit ( id serial NOT NULL, unit_id text, batch_id text, create_date