From: | Denis Perchine <dyp(at)perchine(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problems with order by, limit, and indices |
Date: | 2001-01-08 06:24:47 |
Message-ID: | 01010812244700.00613@dyp.perchine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> > slygreetings=> explain select * from users where variant_id=5 AND
> > active='f' order by rcptdate,variant_id limit 60;
> > NOTICE: QUERY PLAN:
> >
> > Limit (cost=13005.10..13005.10 rows=60 width=145)
> > -> Sort (cost=13005.10..13005.10 rows=3445 width=145)
> > -> Index Scan using users_rcptdate_vid_key on users
> > (cost=0.00..12658.35 rows=3445 width=145)
>
> Now, that's not what I told you to do, is it? It works fine for me:
>
> regression=# create table users (variant_id int , active bool, rcptdate
> date); CREATE
> regression=# create index usersind on users( variant_id,rcptdate,active);
> CREATE
> regression=# explain select * from users where variant_id=5 AND active='f'
> regression-# order by rcptdate limit 1;
> NOTICE: QUERY PLAN:
>
> Limit (cost=8.22..8.22 rows=1 width=9)
> -> Sort (cost=8.22..8.22 rows=5 width=9)
> -> Index Scan using usersind on users (cost=0.00..8.16 rows=5
> width=9)
>
> EXPLAIN
> regression=# explain select * from users where variant_id=5 AND active='f'
> regression-# order by variant_id,rcptdate limit 1;
> NOTICE: QUERY PLAN:
>
> Limit (cost=0.00..1.63 rows=1 width=9)
> -> Index Scan using usersind on users (cost=0.00..8.16 rows=5 width=9)
>
> EXPLAIN
>
> The specified sort order has to match the index if you hope to avoid
> a sort step.
Dummy me... My aplogies for the time I stole...
--
Sincerely Yours,
Denis Perchine
----------------------------------
E-Mail: dyp(at)perchine(dot)com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas T. Thai | 2001-01-08 07:08:40 | mnogosearch 3.1.8 & duplicate keys |
Previous Message | Adam Haberlach | 2001-01-08 03:47:03 | Re: PHP and PostgreSQL |