Re: Slow query - lots of temporary files.

On 10 June 2015 at 15:02, Claudio Freire <klaussfreire(at)gmail(dot)com> 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)