Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    16

    Unanswered: how to create index?

    i don't know how to create the index about this sql?

    SELECT b.screen_id AS screen_id, b.name AS screen_name
    FROM tbl_showlog AS a CROSS JOIN
    tbl_screen AS b CROSS JOIN
    tbl_company AS c
    WHERE a.screen_id = b.screen_id
    AND b.company_id = c.company_id
    AND c.company_id = 1
    AND c.is_active = 1
    GROUP BY b.screen_id, b.name
    ORDER BY b.screen_id

    many thx!

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Index

    Where do you want to create an index

    psql template1
    \h CREATE INDEX

    then you see the syntax
    or do you want to create a primarty key for that table

    \h CREATE TABLE

    I you want do define an index you can define indizes on almost every field in the table
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Nov 2002
    Posts
    16

    Re: Index

    i tried the index statement as the following.
    but the sql is still to be used the table scan for execution.

    CREATE INDEX tbl_showlog_screen_id_idx ON tbl_showlog USING btree (screen_id);

    CREATE UNIQUE INDEX tbl_screen_screen_id_idx ON tbl_screen USING btree (screen_id);

    CREATE INDEX tbl_screen_company_id_idx ON tbl_screen USING btree (company_id);

    CREATE UNIQUE INDEX tbl_company_company_id_idx ON tbl_company USING btree (company_id);

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    question

    type in \d table
    if the indices are displayed they are right

    If they are defined please post the SELECT statement
    sorry did not looked above :-)

    On what fiueld is the seq scan
    can you post the execution plan?
    Last edited by eperich; 11-19-02 at 05:27.
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    Nov 2002
    Posts
    16

    Smile Re: question

    Group (cost=136.64..141.01 rows=87 width=28)
    -> Sort (cost=136.64..136.64 rows=874 width=28)
    -> Hash Join (cost=2.13..93.91 rows=874 width=28)
    -> Hash Join (cost=1.10..73.20 rows=1749 width=24)
    -> Seq Scan on tbl_showlog a (cost=0.00..41.49 rows=1749 width=4)
    -> Hash (cost=1.08..1.08 rows=8 width=20)
    -> Seq Scan on tbl_screen b (cost=0.00..1.08 rows=8 width=20)
    -> Hash (cost=1.03..1.03 rows=1 width=4)
    -> Seq Scan on tbl_company c (cost=0.00..1.03 rows=1 width=4)

    there is the query :>

  6. #6
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    one more question

    can you post the result of \d of the 3 tables???
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  7. #7
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Hints

    2 hints:

    In postgresql.conf is a flag.
    there you can turn off the seq scan. so he always uses indices

    Also in your sql-statement always use company_id='1'
    with single quotes also if this is int.

    The optimizer analyzes this better.

    if this is not working:
    Trial and error. Set an index on each field and test if there is a seq scan :-)
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  8. #8
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    forgotten

    you can also test this the fast way

    SET enable_seq_scan=off;
    SELECT ....;
    SET enable_seq_scan=on;


    I think this is the setting
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  9. #9
    Join Date
    Nov 2002
    Posts
    16

    Talking Re: Hints

    change the conf file
    use
    CREATE INDEX tbl_showlog_screen_id_idx ON tbl_showlog USING btree (screen_id);

    done!

    thx!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •