oscon=> SELECT * FROM tax LIMIT 1; -[ RECORD 1 ]----+------------------------------------ tsn | 50 unit_name1 | Bacteria unit_name2 | unit_name3 | unit_name4 | usage | invalid unaccept_reason | credibility_rtng | No review; untreated NODC data parent_tsn | 0 taxon_author_id | 0 kingdom_id | 1 rank_id | 10 update_date | 1996-07-29 numeyes | oscon=> SELECT * FROM tax WHERE unit_name2 = 'sapiens'; -[ RECORD 1 ]----+------------------------------------ tsn | 180092 unit_name1 | Homo unit_name2 | sapiens unit_name3 | unit_name4 | usage | valid unaccept_reason | credibility_rtng | TWG standards met parent_tsn | 180091 taxon_author_id | 21823 kingdom_id | 5 rank_id | 220 update_date | 2002-07-30 numeyes | 2
EXPLAIN ANALYZE
SELECT g.unit_name1 AS "Genus", s.unit_name1||' '|| s.unit_name2 AS "Species"
FROM tax g,
(SELECT * FROM tax WHERE unit_name2 = 'sapiens') AS s
WHERE s.parent_tsn = g.tsn;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (C=22.51..47.83 25) (A=5159.72..7745.31 1/1)
Hash Cond: ("outer".tsn = "inner".parent_tsn)
-> Seq Scan on tax g (C=0.00..20.00 1000) (A=0.20..3822.12 331154/1)
-> Hash (C=22.50..22.50 5) (A=2808.87..2808.87 0/1)
-> Seq Scan on tax (C=0.00..22.50 5) (A=2562.25..2808.84 1/1)
Filter: (unit_name2 = 'sapiens'::character varying)
Total runtime: 7745.68 msec
oscon=> SELECT reltuples, relpages FROM pg_class WHERE relname='tax';
reltuples | relpages
-----------+----------
1000 | 10
oscon=> SELECT COUNT(*) FROM pg_stats WHERE tablename='tax';
count
-------
0
oscon=> SELECT reltuples, relpages FROM pg_class WHERE relname='tax';
reltuples | relpages
-----------+----------
366663 | 5646
oscon=> SELECT COUNT(*) FROM pg_stats WHERE tablename='tax';
count
-------
14
QUERY PLAN
------------------------------------------------------------------------------------
Merge Join (C=61279.97..63093.15 35) (A=27972.32..27972.33 1/1)
Merge Cond: ("outer".tsn = "inner".parent_tsn)
-> Sort (C=51049.80..51966.46 366663) (A=23339.82..24324.02 180029/1)
Sort Key: g.tsn
-> Seq Scan on tax g (C=0.00..9312.63 366663) (A=0.24..5150.62 331154/1)
-> Sort (C=10230.17..10230.26 35) (A=3007.64..3007.64 1/1)
Sort Key: tax.parent_tsn
-> Seq Scan on tax (C=0.00..10229.29 35) (A=2696.46..3007.23 1/1)
Filter: (unit_name2 = 'sapiens'::character varying)
Total runtime: 27991.53 msec
EXPLAIN ANALYZE
SELECT k.nam AS "Kingdom", p.unit_name1 AS "Phylum", sp.unit_name1 AS "Subphylum",
c.unit_name1 AS "Class", sc.unit_name1 AS "Subclass", i.unit_name1 AS "Infraclass",
o.unit_name1 AS "Order", f.unit_name1 AS "Family", g.unit_name1 as "Genus",
s.unit_name1 ||' '|| s.unit_name2 AS "Species"
FROM kingdom k, tax p, tax sp, tax c, tax sc, tax i, tax o, tax f, tax g,
(SELECT * FROM tax WHERE unit_name2 = 'sapiens') AS s
WHERE s.parent_tsn = g.tsn
AND g.parent_tsn = f.tsn
AND f.parent_tsn = o.tsn
AND o.parent_tsn = i.tsn
AND i.parent_tsn = sc.tsn
AND sc.parent_tsn = c.tsn
AND c.parent_tsn = sp.tsn
AND sp.parent_tsn = p.tsn
AND s.kingdom_id = k.id;
Kingdom | Phylum | Subphylum | Class | Subclass | Infraclass | Order | Family | Genus | Species ---------+----------+------------+----------+----------+------------+----------+-----------+-------+-------------- Animalia | Chordata | Vertebrata | Mammalia | Theria | Eutheria | Primates | Hominidae | Homo | Homo sapiens
QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop (C=9787.31..11240.37 31) (A=2820.44..2820.62 1/1)
-> Nested Loop (C=9787.31..11058.81 31) (A=2819.75..2819.90 1/1)
-> Nested Loop (C=9787.31..10877.25 31) (A=2819.24..2819.37 1/1)
-> Nested Loop (C=9787.31..10695.69 31) (A=2818.91..2819.02 1/1)
-> Nested Loop (C=9787.31..10514.13 31) (A=2818.74..2818.83 1/1)
-> Nested Loop (C=9787.31..10332.57 31) (A=2818.24..2818.31 1/1)
-> Nested Loop (C=9787.31..10151.01 31) (A=2817.85..2817.91 1/1)
-> Nested Loop (C=9787.31..9969.45 31) (A=2817.55..2817.59 1/1)
-> Merge Join (C=9787.31..9787.89 31) (A=2816.53..2816.55 1/1)
Merge Cond: ("outer".id = "inner".kingdom_id)
-> Sort (C=1.11..1.12 5) (A=0.70..0.72 5/1)
Sort Key: k.id
-> Seq Scan on kingdom k (C=0.00..1.05 5) (A=0.22..0.27 5/1)
-> Sort (C=9786.20..9786.28 31) (A=2815.67..2815.68 1/1)
Sort Key: tax.kingdom_id
-> Seq Scan on tax (C=0.00..9785.42 31) (A=2560.56..2815.51 1/1)
Filter: (unit_name2 = 'sapiens'::character varying)
-> Index Scan using tax_tsn on tax g (C=0.00..5.79 1) (A=0.95..0.96 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..5.79 1) (A=0.24..0.25 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..5.79 1) (A=0.31..0.32 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..5.79 1) (A=0.43..0.44 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
-> Index Scan using tax_tsn on tax sc (C=0.00..5.79 1) (A=0.09..0.10 1/1)
Index Cond: ("outer".parent_tsn = sc.tsn)
-> Index Scan using tax_tsn on tax c (C=0.00..5.79 1) (A=0.23..0.24 1/1)
Index Cond: ("outer".parent_tsn = c.tsn)
-> Index Scan using tax_tsn on tax sp (C=0.00..5.79 1) (A=0.40..0.42 1/1)
Index Cond: ("outer".parent_tsn = sp.tsn)
-> Index Scan using tax_tsn on tax p (C=0.00..5.79 1) (A=0.58..0.60 1/1)
Index Cond: ("outer".parent_tsn = p.tsn)
Total runtime: 2823.85 msec
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (C=853.76..1580.57 31) (A=2.17..2.26 1/1)
...
-> Nested Loop (C=0.00..307.19 31) (A=0.40..0.43 1/1)
-> Index Scan using tax_unitname2 on tax (C=0.00..125.63 31) (A=0.23..0.24 1/1)
Index Cond: (unit_name2 = 'sapiens'::character varying)
-> Index Scan using tax_tsn on tax g (C=0.00..5.79 1) (A=0.08..0.09 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..5.79 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..5.79 1) (A=0.07..0.09 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
...
Total runtime: 5.55 msec
EXPLAIN ANALYZE
SELECT k.nam AS "Kingdom", p.unit_name1 AS "Phylum", sp.unit_name1 AS "Subphylum",
c.unit_name1 AS "Class", sc.unit_name1 AS "Subclass", i.unit_name1 AS "Infraclass",
o.unit_name1 AS "Order", f.unit_name1 AS "Family", g.unit_name1 as "Genus",
s.unit_name1 ||' '|| s.unit_name2 AS "Species"
FROM kingdom k, tax p, tax sp, tax c, tax sc, tax i, tax o, tax f, tax g,
(SELECT * FROM tax WHERE LOWER(unit_name2) = 'sapiens') AS s
WHERE s.parent_tsn = g.tsn
AND g.parent_tsn = f.tsn
AND f.parent_tsn = o.tsn
AND o.parent_tsn = i.tsn
AND i.parent_tsn = sc.tsn
AND sc.parent_tsn = c.tsn
AND c.parent_tsn = sp.tsn
AND sp.parent_tsn = p.tsn
AND s.kingdom_id = k.id;
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (C=10702.95..87621.25 1656) (A=3780.00..3780.18 1/1)
-> Nested Loop (C=10702.95..78010.08 1656) (A=3779.31..3779.46 1/1)
-> Nested Loop (C=10702.95..68398.92 1656) (A=3778.79..3778.92 1/1)
-> Nested Loop (C=10702.95..58787.76 1656) (A=3778.44..3778.55 1/1)
-> Nested Loop (C=10702.95..49176.60 1656) (A=3778.23..3778.32 1/1)
-> Nested Loop (C=10702.95..39565.43 1656) (A=3777.76..3777.83 1/1)
-> Nested Loop (C=10702.95..29954.27 1656) (A=3777.43..3777.48 1/1)
-> Nested Loop (C=10702.95..20343.11 1656) (A=3777.11..3777.15 1/1)
-> Merge Join (C=10702.95..10731.95 1656) (A=3776.12..3776.14 1/1)
Merge Cond: ("outer".id = "inner".kingdom_id)
-> Sort (C=1.11..1.12 5) (A=0.77..0.78 5/1)
Sort Key: k.id
-> Seq Scan on kingdom k (C=0.00..1.05 5) (A=0.23..0.33 5/1)
-> Sort (C=10701.84..10705.98 1656) (A=3775.19..3775.20 1/1)
Sort Key: tax.kingdom_id
-> Seq Scan on tax (C=0.00..10613.31 1656) (A=3452.50..3775.02 1/1)
Filter: (lower((unit_name2)::text) = 'sapiens'::text)
-> Index Scan using tax_tsn on tax g (C=0.00..5.79 1) (A=0.93..0.94 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..5.79 1) (A=0.25..0.26 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..5.79 1) (A=0.26..0.27 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..5.79 1) (A=0.39..0.40 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
-> Index Scan using tax_tsn on tax sc (C=0.00..5.79 1) (A=0.08..0.09 1/1)
Index Cond: ("outer".parent_tsn = sc.tsn)
-> Index Scan using tax_tsn on tax c (C=0.00..5.79 1) (A=0.25..0.26 1/1)
Index Cond: ("outer".parent_tsn = c.tsn)
-> Index Scan using tax_tsn on tax sp (C=0.00..5.79 1) (A=0.40..0.42 1/1)
Index Cond: ("outer".parent_tsn = sp.tsn)
-> Index Scan using tax_tsn on tax p (C=0.00..5.79 1) (A=0.56..0.58 1/1)
Index Cond: ("outer".parent_tsn = p.tsn)
Total runtime: 3783.46 msec
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (C=73239.94..82880.10 1656) (A=2.27..2.31 1/1)
-> Merge Join (C=73239.94..73268.94 1656) (A=2.10..2.11 1/1)
Merge Cond: ("outer".kingdom_id = "inner".id)
-> Sort (C=73238.83..73242.97 1656) (A=1.57..1.57 1/1)
Sort Key: tax.kingdom_id
-> Nested Loop (C=0.00..73150.30 1656) (A=1.23..1.37 1/1)
-> Nested Loop (C=0.00..63539.14 1656) (A=1.07..1.19 1/1)
-> Nested Loop (C=0.00..53927.98 1656) (A=0.93..1.03 1/1)
-> Nested Loop (C=0.00..44316.82 1656) (A=0.80..0.88 1/1)
-> Nested Loop (C=0.00..34705.65 1656) (A=0.65..0.72 1/1)
-> Nested Loop (C=0.00..25094.49 1656) (A=0.52..0.56 1/1)
-> Nested Loop (C=0.00..15483.33 1656) (A=0.37..0.40 1/1)
-> Index Scan using tax_lowerunitname2 on tax (C=0.00..5872.17 1656) (A=0.25..0.26 1/1)
Index Cond: (lower((unit_name2)::text) = 'sapiens'::text)
-> Index Scan using tax_tsn on tax g (C=0.00..5.79 1) (A=0.08..0.09 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..5.79 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..5.79 1) (A=0.08..0.09 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
-> Index Scan using tax_tsn on tax sc (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = sc.tsn)
-> Index Scan using tax_tsn on tax c (C=0.00..5.79 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = c.tsn)
-> Index Scan using tax_tsn on tax sp (C=0.00..5.79 1) (A=0.07..0.09 1/1)
Index Cond: ("outer".parent_tsn = sp.tsn)
-> Sort (C=1.11..1.12 5) (A=0.26..0.28 5/1)
Sort Key: k.id
-> Seq Scan on kingdom k (C=0.00..1.05 5) (A=0.03..0.08 5/1)
-> Index Scan using tax_tsn on tax p (C=0.00..5.79 1) (A=0.09..0.11 1/1)
Index Cond: ("outer".parent_tsn = p.tsn)
Total runtime: 5.75 msec
EXPLAIN ANALYZE
SELECT k.nam AS "Kingdom", p.unit_name1 AS "Phylum", sp.unit_name1 AS "Subphylum",
c.unit_name1 AS "Class", sc.unit_name1 AS "Subclass", i.unit_name1 AS "Infraclass",
o.unit_name1 AS "Order", f.unit_name1 AS "Family", g.unit_name1 as "Genus",
s.unit_name1 ||' '|| s.unit_name2 AS "Species"
FROM kingdom k, tax p, tax sp, tax c, tax sc, tax i, tax o, tax f, tax g,
(SELECT * FROM tax WHERE LOWER(unit_name2) = 'sapiens') AS s
WHERE s.parent_tsn = g.tsn
AND g.parent_tsn = f.tsn
AND f.parent_tsn = o.tsn
AND o.parent_tsn = i.tsn
AND i.parent_tsn = sc.tsn
AND sc.parent_tsn = c.tsn
AND c.parent_tsn = sp.tsn
AND sp.parent_tsn = p.tsn
AND s.kingdom_id = k.id;
AND s.numeyes = 2;
QUERY PLAN
------------------------------------------------------------------------
Merge Join (C=8202.59..8203.50 50) (A=2.16..2.17 1/1)
Merge Cond: ("outer".kingdom_id = "inner".id)
-> Sort (C=8201.48..8201.61 50) (A=1.71..1.71 1/1)
Sort Key: tax.kingdom_id
-> Nested Loop (C=0.00..8200.07 50) (A=1.36..1.52 1/1)
-> Nested Loop (C=0.00..7909.60 50) (A=1.16..1.30 1/1)
-> Nested Loop (C=0.00..7619.13 50) (A=1.01..1.13 1/1)
-> Nested Loop (C=0.00..7328.66 50) (A=0.88..0.99 1/1)
-> Nested Loop (C=0.00..7038.19 50) (A=0.75..0.84 1/1)
-> Nested Loop (C=0.00..6747.72 50) (A=0.62..0.69 1/1)
-> Nested Loop (C=0.00..6457.25 50) (A=0.50..0.55 1/1)
-> Nested Loop (C=0.00..6166.78 50) (A=0.39..0.43 1/1)
-> Index Scan using tax_lowerunitname2 on tax (C=0.00..5876.31 50) (A=0.27..0.28 1/1)
Index Cond: (lower((unit_name2)::text) = 'sapiens'::text)
Filter: (numeyes = 2)
-> Index Scan using tax_tsn on tax g (C=0.00..5.79 1) (A=0.08..0.09 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..5.79 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
-> Index Scan using tax_tsn on tax sc (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = sc.tsn)
-> Index Scan using tax_tsn on tax c (C=0.00..5.79 1) (A=0.06..0.08 1/1)
Index Cond: ("outer".parent_tsn = c.tsn)
-> Index Scan using tax_tsn on tax sp (C=0.00..5.79 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = sp.tsn)
-> Index Scan using tax_tsn on tax p (C=0.00..5.79 1) (A=0.07..0.09 1/1)
Index Cond: ("outer".parent_tsn = p.tsn)
-> Sort (C=1.11..1.12 5) (A=0.27..0.28 5/1)
Sort Key: k.id
-> Seq Scan on kingdom k (C=0.00..1.05 5) (A=0.03..0.08 5/1)
Total runtime: 5.51 msec
-[ RECORD 1 ]-----+------------ schemaname | public tablename | tax attname | numeyes null_frac | 0.969778 avg_width | 2 n_distinct | 1 most_common_vals | {2} most_common_freqs | {0.0302222} histogram_bounds | correlation | 1
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop (C=1199.65..2362.44 50) (A=2.08..2.17 1/1)
-> Nested Loop (C=1199.65..2071.97 50) (A=1.92..1.98 1/1)
-> Nested Loop (C=1199.65..1781.49 50) (A=1.75..1.79 1/1)
-> Nested Loop (C=1199.65..1491.02 50) (A=1.61..1.64 1/1)
-> Merge Join (C=1199.65..1200.55 50) (A=1.45..1.46 1/1)
Merge Cond: ("outer".kingdom_id = "inner".id)
-> Sort (C=1198.54..1198.66 50) (A=0.95..0.95 1/1)
Sort Key: tax.kingdom_id
-> Nested Loop (C=0.00..1197.13 50) (A=0.69..0.76 1/1)
-> Nested Loop (C=0.00..906.66 50) (A=0.57..0.62 1/1)
-> Nested Loop (C=0.00..616.19 50) (A=0.45..0.49 1/1)
-> Nested Loop (C=0.00..325.71 50) (A=0.35..0.38 1/1)
-> Index Scan using tax_numeyes2 on tax (C=0.00..35.24 50) (A=0.23..0.24 1/1)
Index Cond: (lower((unit_name2)::text) = 'sapiens'::text)
Filter: (numeyes = 2)
-> Index Scan using tax_tsn on tax g (C=0.00..5.79 1) (A=0.09..0.09 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..5.79 1) (A=0.06..0.08 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
-> Sort (C=1.11..1.12 5) (A=0.30..0.32 5/1)
Sort Key: k.id
-> Seq Scan on kingdom k (C=0.00..1.05 5) (A=0.03..0.08 5/1)
-> Index Scan using tax_tsn on tax sc (C=0.00..5.79 1) (A=0.09..0.10 1/1)
Index Cond: ("outer".parent_tsn = sc.tsn)
-> Index Scan using tax_tsn on tax c (C=0.00..5.79 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = c.tsn)
-> Index Scan using tax_tsn on tax sp (C=0.00..5.79 1) (A=0.08..0.09 1/1)
Index Cond: ("outer".parent_tsn = sp.tsn)
-> Index Scan using tax_tsn on tax p (C=0.00..5.79 1) (A=0.07..0.09 1/1)
Index Cond: ("outer".parent_tsn = p.tsn)
Total runtime: 5.39 msec
QUERY PLAN
----------------------------------------------------------------------------------------------
Nested Loop (C=38.22..3054.63 63) (A=1.93..2.08 1/1)
-> Nested Loop (C=38.22..2677.72 63) (A=1.74..1.86 1/1)
-> Nested Loop (C=38.22..2300.81 63) (A=1.57..1.69 1/1)
-> Nested Loop (C=38.22..1923.90 63) (A=1.44..1.53 1/1)
-> Nested Loop (C=38.22..1546.99 63) (A=1.30..1.38 1/1)
-> Nested Loop (C=38.22..1170.08 63) (A=1.18..1.24 1/1)
-> Nested Loop (C=38.22..793.16 63) (A=1.07..1.11 1/1)
-> Nested Loop (C=38.22..416.25 63) (A=0.96..0.99 1/1)
-> Merge Join (C=38.22..39.34 63) (A=0.83..0.85 1/1)
Merge Cond: ("outer".id = "inner".kingdom_id)
-> Sort (C=1.11..1.12 5) (A=0.32..0.33 5/1)
Sort Key: k.id
-> Seq Scan on kingdom k (C=0.00..1.05 5) (A=0.04..0.09 5/1)
-> Sort (C=37.11..37.27 63) (A=0.40..0.41 1/1)
Sort Key: tax.kingdom_id
-> Index Scan using tax_numeyes2 on tax (C=0.00..35.24 63) (A=0.26..0.28 1/1)
Index Cond: (lower((unit_name2)::text) = 'sapiens'::text)
Filter: (numeyes = 2)
-> Index Scan using tax_tsn on tax g (C=0.00..6.01 1) (A=0.08..0.09 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..6.01 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..6.01 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..6.01 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
-> Index Scan using tax_tsn on tax sc (C=0.00..6.01 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = sc.tsn)
-> Index Scan using tax_tsn on tax c (C=0.00..6.01 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = c.tsn)
-> Index Scan using tax_tsn on tax sp (C=0.00..6.01 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = sp.tsn)
-> Index Scan using tax_tsn on tax p (C=0.00..6.01 1) (A=0.07..0.09 1/1)
Index Cond: ("outer".parent_tsn = p.tsn)
Total runtime: 5.02 msec
EXPLAIN ANALYZE
SELECT crypt(s.unit_name1, gen_salt('bf',10)) AS hash,
k.nam AS "Kingdom", p.unit_name1 AS "Phylum", sp.unit_name1 AS "Subphylum",
c.unit_name1 AS "Class", sc.unit_name1 AS "Subclass", i.unit_name1 AS "Infraclass",
o.unit_name1 AS "Order", f.unit_name1 AS "Family", g.unit_name1 as "Genus",
s.unit_name1 ||' '|| s.unit_name2 AS "Species"
FROM kingdom k, tax p, tax sp, tax c, tax sc, tax i, tax o, tax f, tax g,
(SELECT * FROM tax WHERE LOWER(unit_name2) = 'sapiens') AS s
WHERE s.parent_tsn = g.tsn
AND g.parent_tsn = f.tsn
AND f.parent_tsn = o.tsn
AND o.parent_tsn = i.tsn
AND i.parent_tsn = sc.tsn
AND sc.parent_tsn = c.tsn
AND c.parent_tsn = sp.tsn
AND sp.parent_tsn = p.tsn
AND s.kingdom_id = k.id
AND s.numeyes = 2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (C=38.22..3054.63 63) (A=730.72..730.91 1/1)
-> Nested Loop (C=38.22..2677.72 63) (A=1.81..1.97 1/1)
-> Nested Loop (C=38.22..2300.81 63) (A=1.66..1.79 1/1)
-> Nested Loop (C=38.22..1923.90 63) (A=1.51..1.62 1/1)
-> Nested Loop (C=38.22..1546.99 63) (A=1.37..1.46 1/1)
-> Nested Loop (C=38.22..1170.08 63) (A=1.25..1.31 1/1)
-> Nested Loop (C=38.22..793.16 63) (A=1.13..1.18 1/1)
-> Nested Loop (C=38.22..416.25 63) (A=1.02..1.06 1/1)
-> Merge Join (C=38.22..39.34 63) (A=0.89..0.90 1/1)
Merge Cond: ("outer".id = "inner".kingdom_id)
-> Sort (C=1.11..1.12 5) (A=0.31..0.32 5/1)
Sort Key: k.id
-> Seq Scan on kingdom k (C=0.00..1.05 5) (A=0.04..0.10 5/1)
-> Sort (C=37.11..37.27 63) (A=0.44..0.44 1/1)
Sort Key: tax.kingdom_id
-> Index Scan using tax_numeyes2 on tax (C=0.00..35.24 63) (A=0.27..0.32 1/1)
Index Cond: (lower((unit_name2)::text) = 'sapiens'::text)
Filter: (numeyes = 2)
-> Index Scan using tax_tsn on tax g (C=0.00..6.01 1) (A=0.09..0.10 1/1)
Index Cond: ("outer".parent_tsn = g.tsn)
-> Index Scan using tax_tsn on tax f (C=0.00..6.01 1) (A=0.06..0.07 1/1)
Index Cond: ("outer".parent_tsn = f.tsn)
-> Index Scan using tax_tsn on tax o (C=0.00..6.01 1) (A=0.06..0.08 1/1)
Index Cond: ("outer".parent_tsn = o.tsn)
-> Index Scan using tax_tsn on tax i (C=0.00..6.01 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = i.tsn)
-> Index Scan using tax_tsn on tax sc (C=0.00..6.01 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = sc.tsn)
-> Index Scan using tax_tsn on tax c (C=0.00..6.01 1) (A=0.07..0.08 1/1)
Index Cond: ("outer".parent_tsn = c.tsn)
-> Index Scan using tax_tsn on tax sp (C=0.00..6.01 1) (A=0.07..0.09 1/1)
Index Cond: ("outer".parent_tsn = sp.tsn)
-> Index Scan using tax_tsn on tax p (C=0.00..6.01 1) (A=0.07..0.11 1/1)
Index Cond: ("outer".parent_tsn = p.tsn)
Total runtime: 734.01 msec
CREATE TABLE unit_hash (
unit_name VARCHAR(35),
hash_value CHAR(60)
);
CREATE OR REPLACE FUNCTION get_unithash(varchar) RETURNS varchar AS '
DECLARE
lookup_name ALIAS FOR $1;
myhash CHAR(60);
BEGIN
SELECT hash_value FROM unit_hash WHERE unit_name = lookup_name INTO myhash;
IF myhash IS NULL THEN
SELECT crypt(lookup_name, gen_salt(\'bf\',10)) INTO myhash;
INSERT INTO unit_hash (unit_name, hash_value) VALUES (lookup_name, myhash);
END IF;
RETURN myhash;
END;
' LANGUAGE 'PLPGSQL';
#!perl
use strict;
my $num= shift or 10000;
print "COPY unit_hash (unit_name, hash_value) FROM stdin;\n";
my $val = '$2a$10$O6TOcuOP/cYhSrPnIv3WZuDvDNSs02EqeyNd7hChwgeT8jgXga9ba';
my @chars = ("A".."Z","a".."z",0..9);
for (1..$num) {
my $name = join"",@chars[map{rand@chars}(1..rand(20)+10)];
print "$name\t$val\n";
}
print "\\.\n";
VACUUM ANALYZE unit_hash;
SELECT reltuples FROM pg_class WHERE relname='unit_hash';
reltuples
-----------
12487
CREATE OR REPLACE FUNCTION clean_unithash() RETURNS TRIGGER AS '
BEGIN
IF OLD.unit_name1 != NEW.unit_name1 THEN
DELETE FROM unit_hash WHERE unit_name = OLD.unit_name1;
END IF;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER clean_unithash AFTER UPDATE OR DELETE ON tax
FOR EACH ROW EXECUTE PROCEDURE clean_unithash();
oscon=> SELECT COUNT(*) FROM unit_hash; count ------- 12488 oscon=> UPDATE tax SET unit_name3 ='Yelnats' WHERE unit_name2 = 'sapiens'; UPDATE 1 oscon=> SELECT COUNT(*) FROM unit_hash; count ------- 12488 oscon=> UPDATE tax SET unit_name1 ='Stanley' WHERE unit_name2 = 'sapiens'; UPDATE 1 oscon=> SELECT COUNT(*) FROM unit_hash; count ------- 12487
CREATE OR REPLACE FUNCTION clean_create_unithash() RETURNS TRIGGER AS '
BEGIN
IF OLD.unit_name1 != NEW.unit_name1 THEN
DELETE FROM unit_hash WHERE unit_name = OLD.unit_name1;
PERFORM get_unithash(NEW.unit_name1);
END IF;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER clean_create_unithash AFTER UPDATE OR INSERT ON tax
FOR EACH ROW EXECUTE PROCEDURE clean_create_unithash();
DROP TRIGGER clean_unithash on tax;
CREATE TRIGGER clean_unithash AFTER DELETE ON tax
FOR EACH ROW EXECUTE PROCEDURE clean_unithash();
DELETE FROM unit_hash; UPDATE tax SET unit_name1 ='Tux' WHERE unit_name2 = 'sapiens'; UPDATE 1 oscon=> SELECT * FROM unit_hash WHERE unit_name = 'Tux'; unit_name | hash_value -----------+-------------------------------------------------------------- Tux | $2a$10$zJYgUUNeXGcgTBxHdZwmbu4Bx7cuzYWnSOh7h5G5Kq.uEcfx/75z6 UPDATE tax SET unit_name1 ='Slonik' WHERE unit_name2 = 'sapiens'; oscon=> SELECT * FROM unit_hash WHERE unit_name = 'Tux' OR unit_name = 'Slonik'; unit_name | hash_value -----------+-------------------------------------------------------------- Slonik | $2a$10$lCugA1/JYwMacQkVgsOZVeFNN5UGmAkv.ys1L4ILWQLA4zN7Z10t
Previous: Test Schema [Table of Contents] Next: 'Child and Parent' Query