Re: Peculiar performance observation....

Something even more peculiar (at least it seems to me..)...

If I drop the index table1_category_gist_idx, I get this:

jobs=> explain analyze select id from table1 where category <@ 'a.b'
ORDER BY category;
QUERY PLAN

-------------------------------------
------------------------------------------------------------------------------
Sort (cost=7568.55..7568.62 rows=28 width=52) (actual
time=4842.691..4854.468 rows=1943 loops=1)
Sort Key: category
-> Seq Scan on jobdata (cost=0.00..7567.88 rows=28 width=52) (actual
time=11.498..4800.907 rows=1943 loops=1)
Filter: (category <@ 'a.b'::ltree)
Total runtime: 4871.076 ms
(5 rows)

.. no disk thrashing all over the place..

I'm really perplexed about this one..;-(

- Greg

>I have a rather peculiar performance observation and would welcome any
>feedback on this.....
>
>First off, the main table (well, part of it.. it is quite large..):
>
>
> Table "table1"
> Column | Type |
> Modifiers
>--------------------+--------------------------
>+-----------------------------------------------------------------
> id | integer | not null default
>nextval('master.id_seq'::text)
> user_id | integer |
> ... (skipping about 20 columns)
> category | ltree[] |
> somedata | text | not null
>
>
>
>Indexes:
> "table1_pkey" primary key, btree (id)
> "table1_category_full_gist_idx" gist (category)
> "table1_id_idx" btree (id)
> "table1_fti_idx" gist (fti) WHERE ((status)::text = 'open'::text)
> "table1_user_id_idx" btree (user_id)
>
>
>database=> explain analyze select id from table1 where category <@ 'a.b';
> QUERY
>PLAN
>-------------------------------------
>-------------------------------------
>-------------------------------------------------------------------------
> Index Scan using table1_category_full_gist_idx on jobdata
>(cost=0.00..113.48 rows=28 width=4) (actual time=43.814..12201.528
>rows=1943 loops=1)
> Index Cond: (category <@ 'a.b'::ltree)
> Filter: (category <@ 'a.b'::ltree)
> Total runtime: 12222.258 ms
>
>
>If I do this:
>
>create table yuck (id integer, category ltree[]);
>insert into yuck select id, category from table1;
>create index category_idx on yuck using gist(category);
>vacuum analyze yuck;
>jobs=> explain analyze select id from table1 where id in (select id from
>yuck where category <@ 'a.b');
> QUERY PLAN
>
>-------------------------------------
>-------------------------------------
>-------------------------------------------------------------
> Nested Loop (cost=108.64..114.28 rows=1 width=52) (actual
>time=654.645..1245.212 rows=1943 loops=1)
> -> HashAggregate (cost=108.64..108.64 rows=1 width=4) (actual
>time=654.202..690.709 rows=1943 loops=1)
> -> Index Scan using category_idx on yuck (cost=0.00..108.57
>rows=28 width=4) (actual time=2.046..623.436 rows=1943 loops=1)
> Index Cond: (category <@ 'a.b'::ltree)
> Filter: (category <@ 'a.b'::ltree)
> -> Index Scan using table1_pkey on jobdata (cost=0.00..5.64 rows=1
>width=52) (actual time=0.219..0.235 rows=1 loops=1943)
> Index Cond: (table1.id = "outer".id)
> Total runtime: 1261.551 ms
>(8 rows)
>
>
>In the first query, my hard disk trashes audibly the entire 12 seconds
>(this is actually the best run I could get, it is usually closer to 20
>seconds), the second query runs almost effortlessly.. I've tried
>reindexing, even dropping the index and recreating it but nothing I do
>helps at all.
>
>Now keep in mind that I do all of my development on painfully slow
>hardware in order to make any performance issues really stand out. But,
>I've done this on production servers too with an equal performance
>improvement noticed.
>
>I just can't figure out why this second query is so much faster, I feel
>like I must have done something very wrong in my schema design or
>something to be suffering this sort of a performance loss. Any idea
>what I can do about this?
>
>Thanks as always!
>
>- Greg