Created
January 26, 2017 11:07
-
-
Save timrobertson100/e6079e972df35ba993275f94a611483c to your computer and use it in GitHub Desktop.
A slow PG query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
u.id, u.name_fk, rel.nub_fk, u.dataset_key, u.constituent_key, | |
CASE WHEN u.is_synonym THEN par.parent_fk ELSE u.parent_fk END as parentKey, | |
CASE WHEN u.is_synonym THEN coalesce(parn2.canonical_name, parn2.scientific_name) ELSE coalesce(parn.canonical_name, parn.scientific_name) END as parent, | |
CASE WHEN u.is_synonym THEN u.parent_fk ELSE null END as acceptedKey, | |
CASE WHEN u.is_synonym THEN parn.scientific_name ELSE null END as accepted, | |
u.basionym_fk, basn.scientific_name as basionym,u.is_synonym, u.pp_synonym_fk, | |
u.origin,u.status,u.nom_status,u.rank,u.references,n.scientific_name, n.canonical_name, | |
n.type as nameType, | |
authorship_full(n.authorship,n.year,n.bracket_authorship, n.bracket_year) as authorship, | |
pub.citation as publishedIn, accto.citation as accordingTo,u.taxon_id, | |
u.source_taxon_key, u."references",ku.id as kid, | |
coalesce(kn.canonical_name,kn.scientific_name) as kingdom,pu.id as pid, | |
coalesce(pn.canonical_name,pn.scientific_name) as phylum,cu.id as cid, | |
coalesce(cn.canonical_name,cn.scientific_name) as class,ou.id as oid, | |
coalesce(onn.canonical_name,onn.scientific_name) as order,fu.id as fid, | |
coalesce(fn.canonical_name,fn.scientific_name) as family,gu.id as gid, | |
coalesce(gn.canonical_name,gn.scientific_name) as genus,sgu.id as sgid, | |
coalesce(sgn.canonical_name,sgn.scientific_name) as subgenus,su.id as sid, | |
coalesce(sn.canonical_name,sn.scientific_name) as species,u.num_descendants, | |
u.modified, u.last_interpreted, m.downloaded as last_crawled, u.deleted, | |
u.remarks, u.issues | |
FROM | |
name_usage u | |
JOIN name n ON u.name_fk=n.id | |
LEFT JOIN dataset_metrics m ON m.dataset_key=u.dataset_key AND m.latest | |
LEFT JOIN nub_rel rel ON u.id=rel.usage_fk | |
LEFT JOIN citation pub on u.name_published_in_fk=pub.id | |
LEFT JOIN citation accto on u.according_to_fk=accto.id | |
LEFT JOIN name_usage ku on u.kingdom_fk=ku.id | |
LEFT JOIN name kn on ku.name_fk=kn.id | |
LEFT JOIN name_usage pu on u.phylum_fk=pu.id | |
LEFT JOIN name pn on pu.name_fk=pn.id | |
LEFT JOIN name_usage cu on u.class_fk=cu.id | |
LEFT JOIN name cn on cu.name_fk=cn.id | |
LEFT JOIN name_usage ou on u.order_fk=ou.id | |
LEFT JOIN name onn on ou.name_fk=onn.id | |
LEFT JOIN name_usage fu on u.family_fk=fu.id | |
LEFT JOIN name fn on fu.name_fk=fn.id | |
LEFT JOIN name_usage gu on u.genus_fk=gu.id | |
LEFT JOIN name gn on gu.name_fk=gn.id | |
LEFT JOIN name_usage sgu on u.subgenus_fk=sgu.id | |
LEFT JOIN name sgn on sgu.name_fk=sgn.id | |
LEFT JOIN name_usage su on u.species_fk=su.id | |
LEFT JOIN name sn on su.name_fk=sn.id | |
LEFT JOIN name_usage par on u.parent_fk=par.id | |
LEFT JOIN name parn on par.name_fk=parn.id | |
LEFT JOIN name_usage par2 on par.parent_fk=par2.id | |
LEFT JOIN name parn2 on par2.name_fk=parn2.id | |
LEFT JOIN name_usage bas on u.basionym_fk=bas.id | |
LEFT JOIN name basn on bas.name_fk=basn.id | |
WHERE | |
rel.nub_fk=7011685 | |
ORDER BY u.dataset_key, u.id | |
LIMIT 10 OFFSET 0; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment