Thread: Order by and index

Dear all,

    I am a new user of postgresql and got a question of using index.

    I have a table with the folloing definition

    CREATE TABLE t_post
    (
        post_id serial NOT NULL,
        ownerid int4,
        t_stamp int4,
        CONSTRAINT t_post_pkey PRIMARY KEY (post_id)
    )
    WITH OIDS;

    When I create an index for ownerid and t_stamp

    CREATE INDEX ownerid_ts_idx
    ON t_post
    USING btree
    (ownerid, t_stamp);

    and execute explain to this query:

    select * from t_post where ownerid = 2 and t_stamp = 1128487197

    the query plan said the following :

Index Scan using ownerid_ts_idx on t_post  (cost=0.00..17.97 rows=4
width=463)
  Index Cond: ((ownerid = 4) AND (t_stamp = 1128487197))

    It is good. However, if I execute the following query:

    select * from t_post where ownerid = 2
    order by t_stamp

 Sort  (cost=2998.31..3001.79 rows=1392 width=463)
  Sort Key: t_stamp
  ->  Seq Scan on t_post  (cost=0.00..2925.62 rows=1392 width=463)
        Filter: (ownerid = 4)

    The query planner doesn't use the index for sorting. Is it normal
and is there any index strategy that I can employ to optimize this
query? Thanks a lot.

Best regards,
Jonathan Tse

Jonathan Tse <dev@tsez.net> writes:
>     It is good. However, if I execute the following query:

>     select * from t_post where ownerid = 2
>     order by t_stamp

>     The query planner doesn't use the index for sorting.

Try "order by ownerid, t_stamp".

Postgres 8.1 will recognize that the where clause makes it OK to
consider the ORDER BY as matching the index order, but existing
releases do not know that.

            regards, tom lane

Hi Tom,

    Thanks for your reply, making us aware of the way that postgresql's
query plan.

    One more question, I got an index on ownerid , but postgresql 8.03
still cannot use index to execute the following sql:

    select ownerid,count(ownerid) from t_post group by ownerid order by
ownerid limit 100

    Why is that? Is 8.1 more capable to use the index? Thanks a lot.

Best regards,
Jonathan Tse

Tom Lane wrote:

>Jonathan Tse <dev@tsez.net> writes:
>
>
>>    It is good. However, if I execute the following query:
>>
>>
>
>
>
>>    select * from t_post where ownerid = 2
>>    order by t_stamp
>>
>>
>
>
>
>>    The query planner doesn't use the index for sorting.
>>
>>
>
>Try "order by ownerid, t_stamp".
>
>Postgres 8.1 will recognize that the where clause makes it OK to
>consider the ORDER BY as matching the index order, but existing
>releases do not know that.
>
>            regards, tom lane
>
>

Jonathan Tse <dev@tsez.net> writes:
>     One more question, I got an index on ownerid , but postgresql 8.03
> still cannot use index to execute the following sql:

>     select ownerid,count(ownerid) from t_post group by ownerid order by
> ownerid limit 100

"Cannot"?  Or "chooses not to"?  And are you sure the choice is wrong?

A simple test case for me says that a seqscan/hashaggregate plan is
faster than an indexscan/groupaggregate plan for this sort of problem.
It'll depend on many factors, but for a query that necessarily involves
visiting every row of the table, you're seriously in error to think that
an indexscan plan must be a win.

Try playing around with enable_seqscan and related settings to see what
the alternatives really    are.

            regards, tom lane