Synopsis: 8-table join with one "WHERE foo IN (...)" condition; works OK with fewer than 5 items in the IN list, but at N=5, the planner starts using a compound index for the first time that completely kills performance (5-6 minutes versus 0-12 seconds). I'm interested in learning what plays a role in this switch of plans (or the unanticipated relative slowness of the N=5 plan). TIA for any wisdom; I've finally made a commitment to really delve into PG. -Kevin 1. Queries and plans 2. Answers to standard questions as per http://wiki.postgresql.org/wiki/Guide_to_reporting_problems 3. Tables 1. Queries and plans The "fast" query, with 4 elements in the IN list. EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(DISTINCT "core_person"."id") FROM "core_person" INNER JOIN "core_sample" ON ("core_person"."id" = "core_sample"."person_id") INNER JOIN "sample" ON ("core_sample"."varify_sample_id" = "sample"."id") INNER JOIN "sample_result" ON ("sample"."id" = "sample_result"."sample_id") INNER JOIN "variant" ON ("sample_result"."variant_id" = "variant"."id") INNER JOIN "variant_effect" ON ("variant"."id" = "variant_effect"."variant_id") INNER JOIN "transcript" ON ("variant_effect"."transcript_id" = "transcript"."id") INNER JOIN "gene" ON ("transcript"."gene_id" = "gene"."id") WHERE "gene"."symbol" IN ('CFC1', 'PROSIT240', 'ZFPM2/FOG2', 'NKX2.5'); http://explain.depesz.com/s/Wul Aggregate (cost=287383.44..287383.45 rows=1 width=4) (actual time=674.434..674.434 rows=1 loops=1) Buffers: shared hit=908 read=412 -> Nested Loop (cost=3530.40..287383.44 rows=1 width=4) (actual time=674.414..674.414 rows=0 loops=1) Buffers: shared hit=908 read=412 -> Nested Loop (cost=3530.40..287379.14 rows=1 width=12) (actual time=674.413..674.413 rows=0 loops=1) Buffers: shared hit=908 read=412 -> Hash Join (cost=3530.40..287375.56 rows=1 width=12) (actual time=674.413..674.413 rows=0 loops=1) Hash Cond: (sample_result.sample_id = core_sample.varify_sample_id) Buffers: shared hit=908 read=412 -> Nested Loop (cost=4.32..283545.98 rows=80929 width=12) (actual time=163.609..571.237 rows=102 loops=1) Buffers: shared hit=419 read=63 -> Nested Loop (cost=4.32..3426.09 rows=471 width=4) (actual time=93.595..112.404 rows=85 loops=1) Buffers: shared hit=19 read=21 -> Nested Loop (cost=4.32..140.18 rows=17 width=4) (actual time=28.280..46.051 rows=4 loops=1) Buffers: shared hit=5 read=10 -> Index Scan using gene_symbol on gene (cost=0.00..30.79 rows=4 width=4) (actual time=28.210..45.938 rows=1 loops=1) Index Cond: ((symbol)::text = ANY ('{CFC1,PROSIT240,ZFPM2/FOG2,NKX2.5}'::text[])) Buffers: shared hit=3 read=7 -> Bitmap Heap Scan on transcript (cost=4.32..27.29 rows=6 width=8) (actual time=0.066..0.106 rows=4 loops=1) Recheck Cond: (gene_id = gene.id) Buffers: shared hit=2 read=3 -> Bitmap Index Scan on transcript_gene_id (cost=0.00..4.32 rows=6 width=0) (actual time=0.049..0.049 rows=4 loops=1) Index Cond: (gene_id = gene.id) Buffers: shared hit=2 read=1 -> Index Scan using variant_effect_transcript_id on variant_effect (cost=0.00..191.83 rows=146 width=8) (actual time=16.345..16.582 rows=21 loops=4) Index Cond: (transcript_id = transcript.id) Buffers: shared hit=14 read=11 -> Index Scan using sample_result_variant_id on sample_result (cost=0.00..593.01 rows=172 width=8) (actual time=5.147..5.397 rows=1 loops=85) Index Cond: (variant_id = variant_effect.variant_id) Buffers: shared hit=400 read=42 -> Hash (cost=3525.76..3525.76 rows=26 width=12) (actual time=103.125..103.125 rows=1129 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 49kB Buffers: shared hit=489 read=349 -> Merge Join (cost=228.11..3525.76 rows=26 width=12) (actual time=57.236..102.752 rows=1129 loops=1) Merge Cond: (core_sample.varify_sample_id = sample.id) Buffers: shared hit=489 read=349 -> Index Scan using core_sample_varify_sample_id on core_sample (cost=0.00..347661.45 rows=119344 width=8) (actual time=0.005..44.699 rows=1130 loops=1) Buffers: shared hit=484 read=312 -> Sort (cost=220.25..227.02 rows=2705 width=4) (actual time=56.997..57.214 rows=2701 loops=1) Sort Key: sample.id Sort Method: quicksort Memory: 223kB Buffers: shared hit=5 read=37 -> Seq Scan on sample (cost=0.00..66.05 rows=2705 width=4) (actual time=0.549..56.245 rows=2705 loops=1) Buffers: shared hit=2 read=37 -> Index Only Scan using core_person_pkey on core_person (cost=0.00..3.58 rows=1 width=4) (never executed) Index Cond: (id = core_sample.person_id) Heap Fetches: 0 -> Index Only Scan using variant_pkey on variant (cost=0.00..4.29 rows=1 width=4) (never executed) Index Cond: (id = sample_result.variant_id) Heap Fetches: 0 Total runtime: 674.797 ms The "slow" query with 5 elements in IN list: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(DISTINCT "core_person"."id") FROM "core_person" INNER JOIN "core_sample" ON ("core_person"."id" = "core_sample"."person_id") INNER JOIN "sample" ON ("core_sample"."varify_sample_id" = "sample"."id") INNER JOIN "sample_result" ON ("sample"."id" = "sample_result"."sample_id") INNER JOIN "variant" ON ("sample_result"."variant_id" = "variant"."id") INNER JOIN "variant_effect" ON ("variant"."id" = "variant_effect"."variant_id") INNER JOIN "transcript" ON ("variant_effect"."transcript_id" = "transcript"."id") INNER JOIN "gene" ON ("transcript"."gene_id" = "gene"."id") WHERE "gene"."symbol" IN ('CFC1', 'PROSIT240', 'ZFPM2/FOG2', 'NKX2.5', 'ZIC3'); http://explain.depesz.com/s/BikZ QUERY PLAN \ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\ --- Aggregate (cost=293669.97..293669.98 rows=1 width=4) (actual time=404443.253..404443.253 rows=1 loops=1) Buffers: shared hit=95972873 read=1888636 -> Nested Loop (cost=4341.32..293669.97 rows=1 width=4) (actual time=1270.642..404431.172 rows=19193 loops=1) Buffers: shared hit=95972867 read=1888636 -> Nested Loop (cost=4341.32..293665.67 rows=1 width=12) (actual time=1243.095..403775.844 rows=19193 loops=1) Buffers: shared hit=95915300 read=1888623 -> Hash Join (cost=4341.32..293662.08 rows=1 width=12) (actual time=1227.121..403667.061 rows=19193 loops=1) Hash Cond: (sample_result.variant_id = variant_effect.variant_id) Buffers: shared hit=95876819 read=1888598 -> Nested Loop (cost=99.86..289414.83 rows=1542 width=8) (actual time=94.839..340982.730 rows=690103508 loops=1) Buffers: shared hit=95876766 read=1888588 -> Hash Join (cost=99.86..3605.46 rows=26 width=12) (actual time=1.483..323.089 rows=1129 loops=1) Hash Cond: (core_sample.varify_sample_id = sample.id) Buffers: shared hit=351 read=1254 -> Seq Scan on core_sample (cost=0.00..2759.44 rows=119344 width=8) (actual time=0.009..309.402 rows=119344 loops=1) Buffers: shared hit=312 read=1254 -> Hash (cost=66.05..66.05 rows=2705 width=4) (actual time=1.227..1.227 rows=2705 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 96kB Buffers: shared hit=39 -> Seq Scan on sample (cost=0.00..66.05 rows=2705 width=4) (actual time=0.008..0.691 rows=2705 loops=1) Buffers: shared hit=39 -> Index Only Scan using sample_variant_idx on sample_result (cost=0.00..8220.58 rows=277209 width=8) (actual time=3.469..218.524 rows=611252 loops=112\ 9) Index Cond: (sample_id = core_sample.varify_sample_id) Heap Fetches: 0 Buffers: shared hit=95876415 read=1887334 -> Hash (cost=4234.10..4234.10 rows=589 width=4) (actual time=326.003..326.003 rows=140 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 5kB Buffers: shared hit=53 read=10 -> Nested Loop (cost=4.32..4234.10 rows=589 width=4) (actual time=0.083..325.953 rows=140 loops=1) Buffers: shared hit=53 read=10 -> Nested Loop (cost=4.32..175.03 rows=21 width=4) (actual time=0.052..234.362 rows=8 loops=1) Buffers: shared hit=18 read=5 -> Index Scan using gene_symbol on gene (cost=0.00..38.29 rows=5 width=4) (actual time=0.023..0.097 rows=2 loops=1) Index Cond: ((symbol)::text = ANY ('{CFC1,PROSIT240,ZFPM2/FOG2,NKX2.5,ZIC3}'::text[])) Buffers: shared hit=12 read=1 -> Bitmap Heap Scan on transcript (cost=4.32..27.29 rows=6 width=8) (actual time=106.303..117.126 rows=4 loops=2) Recheck Cond: (gene_id = gene.id) Buffers: shared hit=6 read=4 -> Bitmap Index Scan on transcript_gene_id (cost=0.00..4.32 rows=6 width=0) (actual time=93.564..93.564 rows=4 loops=2) Index Cond: (gene_id = gene.id) Buffers: shared hit=4 read=2 -> Index Scan using variant_effect_transcript_id on variant_effect (cost=0.00..191.83 rows=146 width=8) (actual time=7.285..11.445 rows=18 loops=\ 8) Index Cond: (transcript_id = transcript.id) Buffers: shared hit=35 read=5 -> Index Only Scan using core_person_pkey on core_person (cost=0.00..3.58 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=19193) Index Cond: (id = core_sample.person_id) Heap Fetches: 0 Buffers: shared hit=38481 read=25 -> Index Only Scan using variant_pkey on variant (cost=0.00..4.29 rows=1 width=4) (actual time=0.033..0.033 rows=1 loops=19193) Index Cond: (id = sample_result.variant_id) Heap Fetches: 0 Buffers: shared hit=57567 read=13 Total runtime: 404443.608 ms 2. Answers to standard questions as per http://wiki.postgresql.org/wiki/Guide_to_reporting_problems A description of what you are trying to achieve and what results you expect: Ideally, I'd like this query to be usable for a couple dozen terms. (This may not be realistic given the current table layout and hardware?) If I drop the problem index, the query finishes in 1.5 minutes for 17 gene symbols, which is ... better. FWIW, my observations: 1. The disk is slow on this system (60-75 MB/sec dd seq read time to /dev/null with bs=8k); I'm not sure if the cost constants need adjusting. 2. The plan changes at N=5 to introduce an index-only scan on sample_variant_idx which is 16 GB (box has 32 GB RAM). This index on the sample_result table is a compound index on foreign keys to the sample and variant tables that are often joined to the sample_result table (as in this query). 3. If I run the query in a transaction that drops the sample_variant_idx first, a fast plan is chosen. It's almost as if the planner is so pleased with itself for having noticed that it can use that compound index instead of the individual foreign key indexes that it throws caution to the winds ;-) 4. The sample_result table is large-ish (748M rows; 145 GB; 312 GB incl extras) and sits in the middle of this join. What I tried so far: 1. Changed statistics target. At first this query was unusable even for N=1 because n_distinct was 264,475 on an involved column when it should have been 4,356,805. I increased the statistics target from 1,000 to 5,000, which brought n_distinct for that column up to 653,662. (I understand that an overly large statistics target can negatively affect plan times, and those are indeed in the vicinity of 400 msec now for typical queries. I should probably decrease.) 2. Learned how to coerce n_distinct, at which point the query started running much faster. As an experiment, I have coerced n_distinct for all the foreign key columns involved in the join. 3. Increased effective_cache_size to larger than memory and decreased it to 12GB, neither of which caused a good plan to be used. 4. Tried GEQO, which never came up with the dud plan involving sample_variant_idx; it doesn't seem quite kosher to plan all queries with GEQO, though, and our queries are automatically constructed by a query builder, so at the moment I don't have the ability to apply custom tweaks for individual queries .... 5. Temporarily dropped the sample_variant_idx, as mentioned above. I'm not sure yet if it's a good idea to do away with this altogether. PostgreSQL version number you are running: PostgreSQL 9.2.7 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit How you installed PostgreSQL: PGDG yum repo Changes made to the settings in the postgresql.conf file: see Server Configuration for a quick way to list them all. checkpoint_segments | 32 | configuration file default_statistics_target | 5000 | configuration file effective_cache_size | 24GB | configuration file log_planner_stats | on | configuration file shared_buffers | 8GB | configuration file work_mem | 150MB | configuration file Operating system and version: RHEL 6.4 - VM with kind of crappy SAN disk storage Linux resrhvardb01d.research.chop.edu 2.6.32-358.6.2.el6.x86_64 #1 SMP Tue May 14 15:48:21 EDT 2013 x86_64 x86_64 x86_64 GNU/Linux What program you're using to connect to PostgreSQL: psql for my tests; psycopg2 Python driver for app Is there anything relevant or unusual in the PostgreSQL server logs?: No CPU manufacturer and model, eg "AMD Athlon X2" or "Intel Core 2 Duo" VM, but /proc/cpuinfo shows two Intel(R) Xeon(R) CPU X5670 @ 2.93GHz Amount and size of RAM installed, eg "2GB RAM" 32 GB RAM Storage details (important for performance and corruption questions) Don't know (yet). Some kind of SAN. Our IT people manage this VM. We will be getting dedicated hardware in the near future. Using dd with an 8k blocksize, I see sequential read performance on uncached files of typically 60-74 MB/s. 3. Tables gene table: 51,254 rows Table "public.gene" Column | Type | Modifiers | Storage | Stats target | Description ---------+------------------------+---------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('gene_id_seq'::regclass) | plain | | chr_id | integer | not null | plain | | symbol | character varying(255) | not null | extended | 10000 | name | text | not null | extended | | hgnc_id | integer | | plain | | Indexes: "gene_pkey" PRIMARY KEY, btree (id) "symbol_unique" UNIQUE CONSTRAINT, btree (symbol) "gene_chr_id" btree (chr_id) "gene_symbol" btree (symbol) "gene_symbol_like" btree (symbol varchar_pattern_ops) Foreign-key constraints: "gene_chr_id_fkey" FOREIGN KEY (chr_id) REFERENCES chromosome(id) DEFERRABLE INITIALLY DEFERRED Referenced by: TABLE "exon" CONSTRAINT "exon_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "gene_detail" CONSTRAINT "gene_detail_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "gene_phenotype" CONSTRAINT "gene_id_refs_id_1a19729a" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "gene_pubmed" CONSTRAINT "gene_id_refs_id_8e5839cd" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "gene_families" CONSTRAINT "gene_id_refs_id_9de0e4fb" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "gene_synonym" CONSTRAINT "gene_id_refs_id_b2bbb6ef" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "geneset_setobject" CONSTRAINT "geneset_setobject_object_id_fkey" FOREIGN KEY (object_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "transcript" CONSTRAINT "transcript_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "variant_effect" CONSTRAINT "variant_effect_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED TABLE "variant" CONSTRAINT "variant_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no transcript table: 215,533 rows Table "public.transcript" Column | Type | Modifiers | Storage | Stats target | Description ---------------------+------------------------+---------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('transcript_id_seq'::regclass) | plain | | strand | character varying(1) | | extended | | start | integer | | plain | | end | integer | | plain | | coding_start | integer | | plain | | coding_end | integer | | plain | | coding_start_status | character varying(20) | | extended | | coding_end_status | character varying(20) | | extended | | exon_count | integer | | plain | | refseq_id | character varying(100) | not null | extended | | gene_id | integer | | plain | | Indexes: "transcript_pkey" PRIMARY KEY, btree (id) "transcript_gene_id" btree (gene_id) "transcript_pkey_gene" btree (id, gene_id) "transcript_refseq_gene" btree (refseq_id, gene_id) Foreign-key constraints: "transcript_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED Referenced by: TABLE "transcript_exon" CONSTRAINT "transcript_id_refs_id_e2bf7f41" FOREIGN KEY (transcript_id) REFERENCES transcript(id) DEFERRABLE INITIALLY DEFERRED TABLE "variant_effect" CONSTRAINT "variant_effect_transcript_id_fkey" FOREIGN KEY (transcript_id) REFERENCES transcript(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no variant_effect table: 8,140,067 rows Table "public.variant_effect" Column | Type | Modifiers | Storage | Stats target | Description ---------------------+------------------------+-------------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('variant_effect_id_seq'::regclass) | plain | | variant_id | integer | | plain | | codon_change | text | | extended | | amino_acid_change | text | | extended | | exon_id | integer | | plain | | transcript_id | integer | | plain | | gene_id | integer | | plain | | effect_id | integer | | plain | | functional_class_id | integer | | plain | | hgvs_c | character varying(200) | | extended | | hgvs_p | character varying(200) | | extended | | segment | character varying(200) | | extended | | Indexes: "variant_effect_pkey" UNIQUE, btree (id) "variant_effect_effect_id" btree (effect_id) "variant_effect_exon_id" btree (exon_id) "variant_effect_functional_class_id" btree (functional_class_id) "variant_effect_gene_id" btree (gene_id) "variant_effect_hgvs_c" btree (hgvs_c) "variant_effect_hgvs_c_like" btree (hgvs_c varchar_pattern_ops) "variant_effect_hgvs_p" btree (hgvs_p) "variant_effect_hgvs_p_like" btree (hgvs_p varchar_pattern_ops) "variant_effect_transcript_id" btree (transcript_id) "variant_effect_variant_id" btree (variant_id) "variant_effect_variant_transcript" btree (variant_id, transcript_id) Foreign-key constraints: "variant_effect_effect_id_fkey" FOREIGN KEY (effect_id) REFERENCES effect(id) DEFERRABLE INITIALLY DEFERRED "variant_effect_exon_id_fkey" FOREIGN KEY (exon_id) REFERENCES exon(id) DEFERRABLE INITIALLY DEFERRED "variant_effect_functional_class_id_fkey" FOREIGN KEY (functional_class_id) REFERENCES variant_functional_class(id) DEFERRABLE INITIALLY DEFERRED "variant_effect_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED "variant_effect_transcript_id_fkey" FOREIGN KEY (transcript_id) REFERENCES transcript(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no variant table: 6,132,722 rows; actually not used because of variant_effect.variant_id and sample_result.variant_id Table "public.variant" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------+------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('variant_id_seq'::regclass) | plain | | chr_id | integer | not null | plain | | pos | integer | not null | plain | | ref | text | not null | extended | | alt | text | not null | extended | | md5 | character varying(32) | not null | extended | | rsid | text | | extended | | type_id | integer | | plain | | liftover | boolean | | plain | | gene_id | integer | | plain | | Indexes: "variant_chr_id_pos_ref_alt_key" UNIQUE, btree (chr_id, pos, ref, alt) "variant_pkey" UNIQUE, btree (id) "variant_alt" btree (alt) "variant_alt_like" btree (alt text_pattern_ops) "variant_chr_id" btree (chr_id) "variant_md5" btree (md5) "variant_ref" btree (ref) "variant_ref_like" btree (ref text_pattern_ops) "variant_rsid" btree (rsid) "variant_type_id" btree (type_id) Foreign-key constraints: "variant_chr_id_fkey" FOREIGN KEY (chr_id) REFERENCES chromosome(id) DEFERRABLE INITIALLY DEFERRED "variant_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES gene(id) DEFERRABLE INITIALLY DEFERRED "variant_type_id_fkey" FOREIGN KEY (type_id) REFERENCES variant_type(id) DEFERRABLE INITIALLY DEFERRED Referenced by: TABLE "sample_result" CONSTRAINT "variant_id_refs_id_313c30dea59a86e8" FOREIGN KEY (variant_id) REFERENCES variant(id) Has OIDs: no sample_result table: 748,183,031 rows; 145 GB; 312 GB incl indexes and toast) Table "public.sample_result" Column | Type | Modifiers | Storage | Stats target | Description -------------------------+--------------------------+------------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('sample_result_id_seq'::regclass) | plain | | notes | text | | extended | | created | timestamp with time zone | not null | plain | | modified | timestamp with time zone | not null | plain | | sample_id | integer | not null | plain | | variant_id | integer | not null | plain | | quality | double precision | | plain | | read_depth | integer | | plain | | genotype_id | integer | | plain | | coverage_ref | integer | | plain | | coverage_alt | integer | | plain | | phred_scaled_likelihood | text | | extended | | downsampling | boolean | | plain | | spanning_deletions | double precision | | plain | | mq | double precision | | plain | | mq0 | double precision | | plain | | baseq_rank_sum | double precision | | plain | | mq_rank_sum | double precision | | plain | | read_pos_rank_sum | double precision | | plain | | strand_bias | double precision | | plain | | homopolymer_run | integer | | plain | | haplotype_score | double precision | | plain | | quality_by_depth | double precision | | plain | | fisher_strand | double precision | | plain | | genotype_quality | double precision | | plain | | in_dbsnp | boolean | | plain | | base_counts | character varying(100) | | extended | | raw_read_depth | integer | | plain | | Indexes: "sample_result_pkey1" PRIMARY KEY, btree (id) "sample_result_pkey" UNIQUE, btree (id) "sample_result_genotype_id" btree (genotype_id) "sample_result_quality" btree (quality) "sample_result_raw_read_depth" btree (raw_read_depth) "sample_result_read_depth" btree (read_depth) "sample_result_sample_id" btree (sample_id) "sample_result_variant_id" btree (variant_id) "sample_variant_idx" btree (sample_id, variant_id) Foreign-key constraints: "sample_id_refs_id_6fa6b6cc5d0f2984" FOREIGN KEY (sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED "sample_result_genotype_id_fkey" FOREIGN KEY (genotype_id) REFERENCES genotype(id) DEFERRABLE INITIALLY DEFERRED "variant_id_refs_id_313c30dea59a86e8" FOREIGN KEY (variant_id) REFERENCES variant(id) Referenced by: TABLE "assessment" CONSTRAINT "sample_result_id_refs_id_5831a8ec3d1e4e0a" FOREIGN KEY (sample_result_id) REFERENCES sample_result(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no sample table: 2705 rows Table "public.sample" Column | Type | Modifiers | Storage | Stats target | Description -------------+--------------------------+-----------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('sample_id_seq'::regclass) | plain | | notes | text | | extended | | created | timestamp with time zone | not null | plain | | modified | timestamp with time zone | not null | plain | | label | character varying(100) | not null default 'placholder'::character varying | extended | | batch_id | integer | not null | plain | | version | integer | not null | plain | | person_id | integer | | plain | | count | integer | not null | plain | | bio_sample | integer | | plain | | published | boolean | not null | plain | | md5 | character varying(32) | | extended | | name | character varying(100) | not null default 'placeholder'::character varying | extended | | project_id | integer | not null | plain | | tissue_id | integer | | plain | | vcf_colname | character varying(200) | | extended | | Indexes: "sample_pkey" PRIMARY KEY, btree (id) "sample_version_c71a9c06ef358ed_uniq" UNIQUE CONSTRAINT, btree (version, batch_id, name) "sample_batch_id" btree (batch_id) "sample_cohort_id" btree (batch_id) "sample_label_like" btree (label varchar_pattern_ops) "sample_person_id" btree (person_id) "sample_project_id" btree (project_id) "sample_tissue_id" btree (tissue_id) Foreign-key constraints: "cohort_id_refs_id_6c74dcea40694064" FOREIGN KEY (batch_id) REFERENCES batch(id) DEFERRABLE INITIALLY DEFERRED "project_id_refs_id_78e0c8fcf52a265d" FOREIGN KEY (project_id) REFERENCES project(id) DEFERRABLE INITIALLY DEFERRED "sample_person_id_fkey" FOREIGN KEY (person_id) REFERENCES person(id) DEFERRABLE INITIALLY DEFERRED "tissue_id_refs_id_2f16a55811371f5a" FOREIGN KEY (tissue_id) REFERENCES tissue(id) DEFERRABLE INITIALLY DEFERRED Referenced by: TABLE "metrics_sample_load" CONSTRAINT "metrics_sample_load_sample_id_fkey" FOREIGN KEY (sample_id) REFERENCES sample(id) TABLE "sample_phenotype" CONSTRAINT "sample_id_refs_id_2723d8269859c3bc" FOREIGN KEY (sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "cohort_sample" CONSTRAINT "sample_id_refs_id_435beca7ea3fecae" FOREIGN KEY (sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "sample_qc" CONSTRAINT "sample_id_refs_id_437acf3032c46c2b" FOREIGN KEY (sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "sample_manifest" CONSTRAINT "sample_id_refs_id_6dad1d60e5a86f62" FOREIGN KEY (sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "sample_result" CONSTRAINT "sample_id_refs_id_6fa6b6cc5d0f2984" FOREIGN KEY (sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "sample_run" CONSTRAINT "sample_run_sample_id_fkey" FOREIGN KEY (sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "pcgc.core_sample" CONSTRAINT "varify_sample_id_refs_id_75c34db2" FOREIGN KEY (varify_sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "big_sample" CONSTRAINT "vsample_id_refs_id_3ad233dd6a3f695e" FOREIGN KEY (vsample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no core_sample table: 119,344 rows Table "pcgc.core_sample" Column | Type | Modifiers | Storage | Stats target | Description ------------------------+------------------------+----------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('core_sample_id_seq'::regclass) | plain | | sample_id | character varying(20) | | extended | | person_id | integer | | plain | | sample_type | character varying(11) | | extended | | source_type | character varying(100) | | extended | | status | character varying(100) | | extended | | disposal_status | character varying(100) | | extended | | dna_qc_status | character varying(100) | | extended | | sample_identifier_type | character varying(20) | | extended | | varify_sample_id | integer | | plain | 10000 | Indexes: "core_sample_pkey" PRIMARY KEY, btree (id) "core_sample_sample_id_uniq" UNIQUE CONSTRAINT, btree (sample_id) "core_sample_disposal_status" btree (disposal_status) "core_sample_disposal_status_like" btree (disposal_status varchar_pattern_ops) "core_sample_dna_qc_status" btree (dna_qc_status) "core_sample_dna_qc_status_like" btree (dna_qc_status varchar_pattern_ops) "core_sample_person_id" btree (person_id) "core_sample_sample_identifier_type" btree (sample_identifier_type) "core_sample_sample_identifier_type_like" btree (sample_identifier_type varchar_pattern_ops) "core_sample_sample_type" btree (sample_type) "core_sample_sample_type_like" btree (sample_type varchar_pattern_ops) "core_sample_source_type" btree (source_type) "core_sample_source_type_like" btree (source_type varchar_pattern_ops) "core_sample_status" btree (status) "core_sample_status_like" btree (status varchar_pattern_ops) "core_sample_varify_sample_id" btree (varify_sample_id) Foreign-key constraints: "person_id_refs_id_56d51ee2" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED "varify_sample_id_refs_id_75c34db2" FOREIGN KEY (varify_sample_id) REFERENCES sample(id) DEFERRABLE INITIALLY DEFERRED Referenced by: TABLE "core_samplefile" CONSTRAINT "sample_id_refs_id_185ff8c9" FOREIGN KEY (sample_id) REFERENCES core_sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_cnvconfirmation" CONSTRAINT "sample_id_refs_id_1c83b6a0" FOREIGN KEY (sample_id) REFERENCES core_sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_variantcallconfirmation" CONSTRAINT "sample_id_refs_id_3beffe04" FOREIGN KEY (sample_id) REFERENCES core_sample(id) DEFERRABLE INITIALLY DEFERRED TABLE "dashboard_request" CONSTRAINT "sample_id_refs_id_46e54337" FOREIGN KEY (sample_id) REFERENCES core_sample(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no core_person: 15,746 rows Table "pcgc.core_person" Column | Type | Modifiers | Storage | Stats target | Description -----------------------+-------------------------+----------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('core_person_id_seq'::regclass) | plain | | blinded_id | character varying(20) | not null | extended | | is_subject | boolean | not null | plain | | working_group_summary | character varying(100) | | extended | | consent_group | integer | | plain | | mendelian_consistent | boolean | not null | plain | | comments | character varying(100) | | extended | | relatives | character varying(1000) | | extended | | Indexes: "core_person_pkey" PRIMARY KEY, btree (id) "core_person_blinded_id_key" UNIQUE CONSTRAINT, btree (blinded_id) "core_person_comments" btree (comments) "core_person_comments_like" btree (comments varchar_pattern_ops) "core_person_consent_group" btree (consent_group) "core_person_is_subject" btree (is_subject) "core_person_mendelian_consistent" btree (mendelian_consistent) "core_person_working_group_summary" btree (working_group_summary) "core_person_working_group_summary_like" btree (working_group_summary varchar_pattern_ops) Referenced by: TABLE "core_familymember" CONSTRAINT "person_id_refs_id_1b8249c8" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_variantcallconfirmation" CONSTRAINT "person_id_refs_id_1b95d861" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_othergenetictestresults" CONSTRAINT "person_id_refs_id_3322dca8" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_karyotypeformula" CONSTRAINT "person_id_refs_id_4b0cf1ae" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_tissuesample" CONSTRAINT "person_id_refs_id_54349bf1" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_subject" CONSTRAINT "person_id_refs_id_55696453" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_sample" CONSTRAINT "person_id_refs_id_56d51ee2" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_guid" CONSTRAINT "person_id_refs_id_5c945e79" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_mutationresults" CONSTRAINT "person_id_refs_id_65047092" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_persondiagnosis" CONSTRAINT "person_id_refs_id_67b5236f" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_microarrayresults" CONSTRAINT "person_id_refs_id_6bf9527a" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_fishresults" CONSTRAINT "person_id_refs_id_a4734aaf" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_copynumberresults" CONSTRAINT "person_id_refs_id_ae0ddf0d" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_karyotypeabnormalitiesfather" CONSTRAINT "person_id_refs_id_b04014a" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_blindfile" CONSTRAINT "person_id_refs_id_b24509e6" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_probandformcompletion" CONSTRAINT "person_id_refs_id_b4ca4d2d" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_workinggroupmembership" CONSTRAINT "person_id_refs_id_da4a9bc6" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_karyotypeabnormalitiesproband" CONSTRAINT "person_id_refs_id_e3eb5c6b" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_genetictesting" CONSTRAINT "person_id_refs_id_ed9fd34b" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_karyotypeabnormalitiesmother" CONSTRAINT "person_id_refs_id_f6f61751" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED TABLE "core_cnvconfirmation" CONSTRAINT "person_id_refs_id_ff18d483" FOREIGN KEY (person_id) REFERENCES core_person(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
