Yves Vindevogel wrote:

> Hi,
>
> I have a very simple query on a big table. When I issue a "limit"
> and/or "offset" clause, the query is not using the index.
> Can anyone explain me this ?

You didn't give enough information. What does you index look like that
you are expecting it to use?
Generally, you want to have matching columns. So you would want
CREATE INDEX blah ON tblprintjobs(loginuser, desceventdate, desceventtime);

Next, you should post EXPLAIN ANALYZE instead of regular explain, so we
can have an idea if the planner is actually making correct estimations.

John
=:->

>
> rvponp=# explain select * from tblprintjobs order by loginuser,
> desceventdate, desceventtime offset 25 limit 25 ;
> QUERY PLAN
> -----------------------------------------------------------------------------------
>
> Limit (cost=349860.62..349860.68 rows=25 width=206)
> -> Sort (cost=349860.56..351416.15 rows=622236 width=206)
> Sort Key: loginuser, desceventdate, desceventtime
> -> Seq Scan on tblprintjobs (cost=0.00..25589.36 rows=622236 width=206)
> (4 rows)
>
> rvponp=# explain select * from tblprintjobs order by loginuser,
> desceventdate, desceventtime ;
> QUERY PLAN
> -----------------------------------------------------------------------------
>
> Sort (cost=349860.56..351416.15 rows=622236 width=206)
> Sort Key: loginuser, desceventdate, desceventtime
> -> Seq Scan on tblprintjobs (cost=0.00..25589.36 rows=622236 width=206)
> (3 rows)
>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> *Yves Vindevogel*
> *Implements*




-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCuCdfJdeBCYSNAAMRArOGAJ47wC9pDp+ZHjj9CsP69U QVCCabHgCfVz5J
Pstlzi8Xqe0keB+sTv9lAgk=
=Qudp
-----END PGP SIGNATURE-----

Yves Vindevogel wrote:

> rvponp=# explain analyze select * from tblPrintjobs order by
> loginuser, desceventdate, desceventtime ;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=345699.06..347256.49 rows=622972 width=203) (actual
> time=259438.952..268885.586 rows=622972 loops=1)
> Sort Key: loginuser, desceventdate, desceventtime
> -> Seq Scan on tblprintjobs (cost=0.00..25596.72 rows=622972
> width=203) (actual time=21.155..8713.810 rows=622972 loops=1)
> Total runtime: 271583.422 ms
> (4 rows)


Can you post it with the limit? I realize the query takes a long time,
but that is the more important query to look at.

Also, just as a test, if you can, try dropping most of the indexes
except for the important one. It might be that the planner is having a
hard time because there are too many permutations to try.
I believe if you drop the indexes inside a transaction, they will still
be there for other queries, and if you rollback instead of commit, you
won't lose anything.

BEGIN;
DROP INDEX ...
EXPLAIN ANALYZE SELECT *...
ROLLBACK;

John
=:->


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCuC7QJdeBCYSNAAMRAqhPAJ96D2JK9uRlEzObVbGd3n 3Tttoh6gCdFICG
jsuBrmMCNswfIgrBo9rcfR8=
=tiqi
-----END PGP SIGNATURE-----

[John A Meinel - Tue at 10:14:24AM -0500]
> I believe if you drop the indexes inside a transaction, they will still
> be there for other queries, and if you rollback instead of commit, you
> won't lose anything.

Has anyone tested this?

(sorry, I only have the production database to play with at the moment,
and I don't think I should play with it ;-)

--
Tobias Brox, Beijing


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, Jun 21, 2005 at 09:46:39PM +0200, Tobias Brox wrote:
> [John A Meinel - Tue at 10:14:24AM -0500]
> > I believe if you drop the indexes inside a transaction, they will still
> > be there for other queries, and if you rollback instead of commit, you
> > won't lose anything.
>
> Has anyone tested this?

Observations from tests with 8.0.3:

DROP INDEX acquires an AccessExclusiveLock on the table and on the
index. This will cause the transaction executing the DROP INDEX
to block until no other transaction holds any kind of lock on either,
and once the locks are acquired, no other transaction will be able
to access the table or the index until the transaction doing the
DROP INDEX commits or rolls back. Rolling back leaves the index
in place.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tobias Brox <tobias@nordicbet.com> writes:
> [John A Meinel - Tue at 10:14:24AM -0500]
>> I believe if you drop the indexes inside a transaction, they will still
>> be there for other queries, and if you rollback instead of commit, you
>> won't lose anything.

> Has anyone tested this?

Certainly. Bear in mind though that DROP INDEX will acquire exclusive
lock on the index's table, so until you roll back, no other transaction
will be able to touch the table at all. So the whole thing may be a
nonstarter in a production database anyway :-(. You can probably get
away with
BEGIN;
DROP INDEX ...
EXPLAIN ...
ROLLBACK;
if you fire it from a script rather than by hand --- but EXPLAIN
ANALYZE might be a bad idea ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane - Tue at 05:20:07PM -0400]
>
> Certainly. Bear in mind though that DROP INDEX will acquire exclusive
> lock on the index's table, so until you roll back, no other transaction
> will be able to touch the table at all. So the whole thing may be a
> nonstarter in a production database anyway :-(.

That's what I was afraid of. I was running psql at the production DB
without starting a transaction (bad habit, I know) and tried to drop an
index there, but I had to cancel the transaction, it took forever and
in the same time blocking all the revenue-generating activity.

--
Tobias Brox, +86-13521622905
Nordicbet, IT dept

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match