Re: Inheritance & Indexes

On Tue, 24 Jun 2003, Alan Williams wrote:

>  hs.exon.2=> \d ga_psr_transcript_1
>         Table "public.ga_psr_transcript_1"
>     Column    |          Type          | Modifiers
> --------------+------------------------+-----------
>  id           | integer                | not null
>  parent       | integer                |
>  seqname      | character varying(100) | not null
>  source_type  | smallint               | not null
>  feature_type | smallint               | not null
>  start        | integer                | not null
>  stop         | integer                | not null
>  strand       | character(1)           | not null
>  annot_name   | character varying(100) | not null
>  depth        | integer                | not null
> Indexes: ga_psr_transcript_1_pkey primary key btree (id),
>          ga_psr_transcript_1_start_stop btree ("start", stop),
>          ga_psr_transcript_1_stop btree (stop)
> Check constraints: "aw_psr_transcript_1_strand" (((strand = '+'::bpchar) OR (strand = '-'::bpchar)) OR (strand =
'.'::bpchar))
> Triggers: RI_ConstraintTrigger_1412526244,
>           RI_ConstraintTrigger_1412526245
>
> hs.exon.2=> \d ga_psr_exon_1
>                 Table "public.ga_psr_exon_1"
>         Column         |          Type          | Modifiers
> -----------------------+------------------------+-----------
>  id                    | integer                | not null
>  parent                | integer                |
>  seqname               | character varying(100) | not null
>  source_type           | smallint               | not null
>  feature_type          | smallint               | not null
>  start                 | integer                | not null
>  stop                  | integer                | not null
>  strand                | character(1)           | not null
>  annot_name            | character varying(100) | not null
>  transcript_cluster_id | integer                | not null
>  depth                 | integer                | not null
> Indexes: ga_psr_exon_1_pkey primary key btree (id),
>          ga_psr_exon_1_parent btree (parent),
>          ga_psr_exon_1_start_stop btree ("start", stop),
>          ga_psr_exon_1_stop btree (stop)
> Check constraints: "aw_psr_exon_1_strand" (((strand = '+'::bpchar) OR (strand = '-'::bpchar)) OR (strand =
'.'::bpchar))
> Triggers: RI_ConstraintTrigger_1412526088,
>           RI_ConstraintTrigger_1412526089
>
> hs.exon.2=> select count(*) from ga_psr_transcript_1;
>  count
> -------
>  43398
> (1 row)
>
> hs.exon.2=> select count(*) from ga_psr_exon_1;
>  count
> --------
>  176908
> (1 row)
>
> Now if I do a join on the "leaf" tables everything looks good:
>
>  hs.exon.2=> explain select * from ga_psr_transcript_1 t, ga_psr_exon_1e where e.parent = t.id;
>                                                      QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------
>  Merge Join  (cost=0.00..9087.71 rows=176908 width=98)
>    Merge Cond: ("outer".id = "inner".parent)
>    ->  Index Scan using ga_psr_transcript_1_pkey on ga_psr_transcript_1 t  (cost=0.00..1066.17 rows=43398 width=47)
>    ->  Index Scan using ga_psr_exon_1_parent on ga_psr_exon_1 e (cost=0.00..5259.52 rows=176908 width=51)
> (4 rows)
>
> If I do a join on the parent table, the optimizer refuses to use the
> indicies:
>
> hs.exon.2=> explain select * from ga_psr_transcript t, ga_psr_exon e where e.parent = t.id;

In this case, you can't use a single index scan to get the rows in order
so the part that makes the above a nice plan doesn't really apply.  If
you're getting all the rows and sorting them, index scans are probably a
waste of time unless you have alot of dead space.  If we supported
multi-table indexes, that'd potentially let you get a plan like
the above.

> -----------------------------------------------------------------------------------------------
>  Merge Join  (cost=1239155.37..70188119.40 rows=5514877218 width=334)
>    Merge Cond: ("outer".id = "inner".parent)
>    ->  Sort  (cost=243481.37..244816.14 rows=533908 width=165)
>          Sort Key: t.id
>          ->  Append  (cost=0.00..10980.08 rows=533908 width=165)
[lots of seqscans snipped]
>    ->  Sort  (cost=995674.00..1000838.64 rows=2065853 width=169)
>          Sort Key: e.parent
>          ->  Append  (cost=0.00..43563.52 rows=2065853 width=169)
[more seqscans snipped]

> Same thing even if I'm querying for a specific tuple:
>
> hs.exon.2=> explain select * from ga_psr_transcript t, ga_psr_exon e
> where e.parent = t.id and t.id = 123;

ISTM it's willing to use an index scan on at least some of t's subtables.
Does explicitly saying e.parent=123 help?

>                                                         QUERY PLAN
> ------------------------------------------------------------------------
> ---------------------------------------------------
>  Hash Join  (cost=99.06..73488.33 rows=320207 width=334)
>    Hash Cond: ("outer".parent = "inner".id)
>    ->  Append  (cost=0.00..43563.52 rows=2065853 width=169)
[lots of seqscans snipped again]
>    ->  Hash  (cost=98.98..98.98 rows=31 width=165)
>          ->  Append  (cost=0.00..98.98 rows=31 width=165)
>                ->  Seq Scan on ga_psr_transcript t  (cost=0.00..22.50 rows=5 width=165)
>                      Filter: (id = 123)
[snipping a bunch of index scans]
>                ->  Seq Scan on ga_psr_transcript_m t  (cost=0.00..1.12 rows=1 width=47)
>                      Filter: (id = 123)
> (86 rows)