Re: Very slow query - Mailing list pgsql-general

Sorry for replying to my own post, but I'm anxious for an answer. Should
I provide other information?

Thanks
Rory

On 10/05/04, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
> The following query on some small datasets takes over a second to run.
> I'd be grateful for some help in understanding the explain output, and
> to remake the code.
>
> Looks like the sort is using up quite a bit of resources. I made an
> index on boards using columns "b.n_type, b.n_id, b.t_name" but the index
> was not recorded in explain analyze. (see "testindex" below).
>
> I am using PostgreSQL 7.4.2 on i386-pc-linux-gnu (Debian). The query is
> in a psql function (I've changed variables to hard coded integers
> below). The db is vacuumed every night.
>
> Thanks for any help;
> Rory
>
>
> SELECT DISTINCT
>         b.n_id                           as id,
>         b.n_type,
>         CASE b.n_type WHEN 0 THEN 'personal'
>                       WHEN 1 THEN 'private'
>                       WHEN 2 THEN 'blog'
>                       ELSE 'public'
>                       END                as type,
>         b.t_name                         as title
>     FROM
>         boards b, people p, objects o
>     WHERE
>          b.b_hidden = 'f'
>          AND
>          (
>             b.n_type = 3
>             OR
>             b.n_creator = 71
>             OR
>             (   b.n_id = o.n_board_id
>                 AND
>                 o.n_creator = 71
>                 AND
>                 o.n_joined > 0
>             )
>          )
>     ORDER BY
>         b.n_type, b.n_id ASC, b.t_name;
>
> trial=> \d boards
>                                          Table "public.boards"
>     Column     |            Type             |                        Modifiers
> ---------------+-----------------------------+----------------------------------------------------------
>  n_id          | integer                     | not null default nextval('public.boards_n_id_seq'::text)
>  b_hidden      | boolean                     | default false
>  dt_created    | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
>  dt_modified   | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
>  t_mobile      | character varying(15)       |
>  t_email       | character varying(50)       |
>  n_creator     | integer                     | not null
>  n_type        | smallint                    | not null default 0
>  t_name        | character varying(100)      | not null
>  t_description | character varying(500)      |
>  n_id_photo    | integer                     |
>  n_bg_colour   | integer                     | default 0
> Indexes:
>     "boards_pkey" primary key, btree (n_id)
>     "boards_t_email_key" unique, btree (t_email)
>     "boards_t_mobile_key" unique, btree (t_mobile)
>     "testindex" btree (n_type, n_id, t_name)
> Foreign-key constraints:
>     "$1" FOREIGN KEY (n_creator) REFERENCES people(n_id) ON UPDATE CASCADE ON DELETE CASCADE
>     "$2" FOREIGN KEY (n_id_photo) REFERENCES photo(n_id) ON UPDATE CASCADE ON DELETE SET NULL
> Triggers:
>     tr_update_modified_time BEFORE UPDATE ON boards FOR EACH ROW EXECUTE PROCEDURE fn_update_modified_time()
>
> trial=> \d people
>                                            Table "public.people"
>       Column       |            Type             |                        Modifiers
> -------------------+-----------------------------+----------------------------------------------------------
>  n_id              | integer                     | not null default nextval('public.people_n_id_seq'::text)
>  n_object_id       | integer                     |
>  n_objects_counter | integer                     | default 0
>  b_hidden          | boolean                     | default false
>  dt_created        | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
>  dt_modified       | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
>  t_nickname        | character varying(20)       | not null
>  t_firstname       | character varying(20)       | not null
>  t_surname         | character varying(25)       | not null
>  t_mobile          | character varying(15)       |
>  t_email           | character varying(50)       |
>  t_password        | character varying(15)       | not null
>  b_registered      | boolean                     | default false
>  n_email_status    | smallint                    | default 0
>  n_txt_status      | smallint                    | default 0
>  b_work_hours      | boolean                     | default false
>  b_confirmations   | boolean                     | default true
> Indexes:
>     "people_pkey" primary key, btree (n_id)
>     "people_t_email_key" unique, btree (t_email)
>     "people_t_mobile_key" unique, btree (t_mobile)
>     "people_t_nickname_key" unique, btree (t_nickname)
> Foreign-key constraints:
>     "object_chk" FOREIGN KEY (n_object_id) REFERENCES objects(n_id) ON UPDATE CASCADE ON DELETE SET NULL
> Triggers:
>     tr_update_modified_time BEFORE UPDATE ON people FOR EACH ROW EXECUTE PROCEDURE fn_update_modified_time()
>
> trial=> \d objects
>                                          Table "public.objects"
>     Column     |            Type             |                         Modifiers
> ---------------+-----------------------------+-----------------------------------------------------------
>  n_id          | integer                     | not null default nextval('public.objects_n_id_seq'::text)
>  t_text_id     | character varying(25)       | not null
>  b_hidden      | boolean                     | default false
>  dt_created    | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
>  dt_modified   | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
>  n_creator     | integer                     | not null
>  n_type        | smallint                    | not null default 0
>  t_name        | character varying(100)      | not null
>  t_description | text                        |
>  t_postcode    | character varying(10)       |
>  n_id_photo    | integer                     |
>  n_board_id    | integer                     | not null
>  n_joined      | smallint                    |
>  b_trigger     | boolean                     | default true
> Indexes:
>     "objects_pkey" primary key, btree (n_id)
> Foreign-key constraints:
>     "$1" FOREIGN KEY (n_creator) REFERENCES people(n_id) ON UPDATE CASCADE ON DELETE CASCADE
>     "$2" FOREIGN KEY (n_id_photo) REFERENCES photo(n_id) ON UPDATE CASCADE ON DELETE SET NULL
>     "$3" FOREIGN KEY (n_board_id) REFERENCES boards(n_id) ON UPDATE CASCADE ON DELETE CASCADE
>
>
>
>  Unique  (cost=3677.26..3711.48 rows=102 width=18) (actual time=12566.422..13045.404 rows=4 loops=1)
>    ->  Sort  (cost=3677.26..3684.10 rows=2738 width=18) (actual time=12566.413..12809.928 rows=158254 loops=1)
>          Sort Key: b.n_type, b.n_id, b.t_name, CASE WHEN (b.n_type = 0) THEN 'personal'::text WHEN (b.n_type = 1)
THEN'private'::te 
> xt WHEN (b.n_type = 2) THEN 'blog'::text ELSE 'public'::text END
>          ->  Nested Loop  (cost=3442.79..3520.93 rows=2738 width=18) (actual time=0.244..1052.180 rows=158254
loops=1)
>                ->  Seq Scan on people p  (cost=0.00..2.67 rows=67 width=0) (actual time=0.012..0.709 rows=67 loops=1)
>                ->  Materialize  (cost=3442.79..3443.20 rows=41 width=18) (actual time=0.005..6.528 rows=2362
loops=67)
>                      ->  Nested Loop  (cost=3.27..3442.79 rows=41 width=18) (actual time=0.216..273.709 rows=2362
loops=1)
>                            Join Filter: ((("inner".n_id = "outer".n_board_id) OR ("inner".n_type = 3) OR
("inner".n_creator= 71)) A 
> ND (("outer".n_creator = 71) OR ("inner".n_type = 3) OR ("inner".n_creator = 71)) AND (("outer".n_joined > 0) OR
("inner".n_type= 3 
> ) OR ("inner".n_creator = 71)))
>                            ->  Seq Scan on objects o  (cost=0.00..27.87 rows=787 width=10) (actual time=0.005..1.372
rows=787loops= 
> 1)
>                            ->  Materialize  (cost=3.27..4.29 rows=102 width=22) (actual time=0.001..0.105 rows=102
loops=787)
>                                  ->  Seq Scan on boards b  (cost=0.00..3.27 rows=102 width=22) (actual
time=0.017..0.352rows=102 lo 
> ops=1)
>                                        Filter: (b_hidden = false)
>  Total runtime: 13051.065 ms
>
>
>
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>