Re: Slow query - lots of temporary files.

[Johann Spies <johann.spies@xxxxxxxxx>]

On 10 June 2015 at 15:02, Claudio Freire <klaussfreire@xxxxxxxxx> wrote:

The joins are different on both versions, and the most likely culprit
is the join against D. It's probably wrong, and the first query is
building a cartesian product.

Without more information about the schema it's difficult to be sure though.

Thanks for your  reply.  I will experiment futher with different joins.

Here is the schema of the involved tables:

nkb=# \d isi.funding_text
                                    Table "isi.funding_text"
 Column |         Type          |                           Modifiers                          
--------+-----------------------+---------------------------------------------------------------
 id     | integer               | not null default nextval('isi.funding_text_id_seq'::regclass)
 ut     | character varying(15) |
 gt     | citext                |
Indexes:
    "funding_text_pkey" PRIMARY KEY, btree (id)
    "funding_text_ut_idx" btree (ut)
Foreign-key constraints:
    "funding_text_ut_fkey" FOREIGN KEY (ut) REFERENCES isi.ritem(ut)

nkb=# \d isi.funding_org
                                    Table "isi.funding_org"
 Column |         Type          |                          Modifiers                          
--------+-----------------------+--------------------------------------------------------------
 id     | integer               | not null default nextval('isi.funding_org_id_seq'::regclass)
 ut     | character varying(15) |
 go     | citext                |
 gn     | character varying     |
Indexes:
    "funding_org_pkey" PRIMARY KEY, btree (id)
    "funding_org_ut_idx" btree (ut)
Foreign-key constraints:
    "funding_org_ut_fkey" FOREIGN KEY (ut) REFERENCES isi.ritem(ut)


                                    Table "isi.africa_uts"
 Column |         Type          |                          Modifiers                         
--------+-----------------------+-------------------------------------------------------------
 ut     | character varying(15) |
 id     | integer               | not null default nextval('isi.africa_uts_id_seq'::regclass)
Indexes:
    "africa_uts_pkey" PRIMARY KEY, btree (id)
    "africa_ut_idx" btree (ut)
Foreign-key constraints:
    "africa_uts_ut_fkey" FOREIGN KEY (ut) REFERENCES isi.ritem(ut)

                                     Table "isi.rauthor"
 Column  |          Type          |                        Modifiers                        
---------+------------------------+----------------------------------------------------------
 id      | integer                | not null default nextval('isi.rauthor_id_seq'::regclass)
 rart_id | character varying(15)  |
 au      | character varying(75)  |
 ro      | character varying(30)  |
 ln      | character varying(200) |
 af      | character varying(200) |
 ras     | character varying(4)   |
 ad      | integer                |
 aa      | text                   |
 em      | character varying(250) |
 ag      | character varying(75)  |
 tsv     | tsvector               |
Indexes:
    "rauthor_pkey" PRIMARY KEY, btree (id) CLUSTER
    "rauthor_ad_idx" btree (ad)
    "rauthor_au_idx" btree (au)
    "rauthor_lower_idx" btree (lower(au::text))
    "rauthor_lower_lower1_idx" btree (lower(ln::text), lower(af::text))
    "rauthor_rart_id_idx" btree (rart_id)
    "rauthor_tsv_idx" gin (tsv)
Referenced by:
    TABLE "level1.person" CONSTRAINT "person_auth_id_fkey" FOREIGN KEY (auth_id) REFERENCES isi.rauthor(id) ON DELETE CASCADE
Triggers:
    tsvectorupdate_for_rauthor BEFORE INSERT OR UPDATE ON isi.rauthor FOR EACH ROW EXECUTE PROCEDURE isi.update_rauthor_tsv()

Regards

Johann

--

Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)