Re: [PERFORM] Limit clause not using index
I just tried this on 8.0.3. A query which runs very fast through an index on a 25 million row table blocked when I dropped the index within a database transaction. As soon as I rolled back the database transactiton, the query completed, the index appears fine, and the query runs fast, as usual. So, it looks like this is right except for the assertion that the index is still available for other queries. -Kevin Tobias Brox [EMAIL PROTECTED] 06/21/05 2:46 PM [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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Limit clause not using index
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 ? 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 Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Limit clause not using index
Yves Vindevogel wrote: Hi, rvponp=# explain select * from tblprintjobs order by loginuser, desceventdate, desceventtime offset 25 limit 25 ; 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 ? Do you have an index on (loginuser,desceventdate,desceventtime)? -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Limit clause not using index
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* signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Limit clause not using index
Yves Vindevogel [EMAIL PROTECTED] writes: Can anyone explain me this ? 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) Do you have an index matching that sort key? I'd certainly expect the above to use it if it were there. For the full table case it's not so clear --- an indexscan isn't always better. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Limit clause not using index
x-tad-biggerThese are my indexes create index ixprintjobsapplicationtype on tblPrintjobs (applicationtype); create index ixprintjobsdesceventdate on tblPrintjobs (desceventdate); create index ixprintjobsdesceventtime on tblPrintjobs (desceventtime); create index ixprintjobsdescpages on tblPrintjobs (descpages); create index ixprintjobsdocumentname on tblPrintjobs (documentname) ; create index ixprintjobseventcomputer on tblPrintjobs (eventcomputer); create index ixprintjobseventdate on tblPrintjobs (eventdate); create index ixprintjobseventtime on tblPrintjobs (eventtime); create index ixprintjobseventuser on tblPrintjobs (eventuser); create index ixprintjobshostname on tblPrintjobs (hostname) ; create index ixprintjobsipaddress on tblPrintjobs (ipaddress) ; create index ixprintjobsloginuser on tblPrintjobs (loginuser) ; create index ixprintjobspages on tblPrintjobs (pages) ; create index ixprintjobsprintport on tblPrintjobs (printport) ; create index ixprintjobsprintqueue on tblPrintjobs (printqueue) ; create index ixprintjobsrecordnumber on tblPrintjobs (recordnumber) ; create index ixprintjobssize on tblPrintjobs (size) ; create index ixprintjobsusertype on tblPrintjobs (usertype) ; create index ixPrintjobsDescpagesDocumentname on tblPrintjobs (descpages, documentname) ; create index ixPrintjobsHostnamePrintqueueDesceventdateDesceventtime on tblPrintjobs (hostname, printqueue, desceventdate, desceventtime) ; create index ixPrintjobsLoginDescEventdateDesceventtime on tblPrintjobs (loginuser, desceventdate, desceventtime) ; /x-tad-bigger On 21 Jun 2005, at 16:42, Tom Lane wrote: Yves Vindevogel [EMAIL PROTECTED]> writes: Can anyone explain me this ? 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) Do you have an index matching that sort key? I'd certainly expect the above to use it if it were there. For the full table case it's not so clear --- an indexscan isn't always better. regards, tom lane Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Limit clause not using index
Nevermind guys There's an error in a function that is creating these indexes. The function never completed succesfully so the index is not there Very sorry about this !! On 21 Jun 2005, at 16:57, Yves Vindevogel wrote: x-tad-biggerThese are my indexes create index ixprintjobsapplicationtype on tblPrintjobs (applicationtype); create index ixprintjobsdesceventdate on tblPrintjobs (desceventdate); create index ixprintjobsdesceventtime on tblPrintjobs (desceventtime); create index ixprintjobsdescpages on tblPrintjobs (descpages); create index ixprintjobsdocumentname on tblPrintjobs (documentname) ; create index ixprintjobseventcomputer on tblPrintjobs (eventcomputer); create index ixprintjobseventdate on tblPrintjobs (eventdate); create index ixprintjobseventtime on tblPrintjobs (eventtime); create index ixprintjobseventuser on tblPrintjobs (eventuser); create index ixprintjobshostname on tblPrintjobs (hostname) ; create index ixprintjobsipaddress on tblPrintjobs (ipaddress) ; create index ixprintjobsloginuser on tblPrintjobs (loginuser) ; create index ixprintjobspages on tblPrintjobs (pages) ; create index ixprintjobsprintport on tblPrintjobs (printport) ; create index ixprintjobsprintqueue on tblPrintjobs (printqueue) ; create index ixprintjobsrecordnumber on tblPrintjobs (recordnumber) ; create index ixprintjobssize on tblPrintjobs (size) ; create index ixprintjobsusertype on tblPrintjobs (usertype) ; create index ixPrintjobsDescpagesDocumentname on tblPrintjobs (descpages, documentname) ; create index ixPrintjobsHostnamePrintqueueDesceventdateDesceventtime on tblPrintjobs (hostname, printqueue, desceventdate, desceventtime) ; create index ixPrintjobsLoginDescEventdateDesceventtime on tblPrintjobs (loginuser, desceventdate, desceventtime) ; /x-tad-bigger On 21 Jun 2005, at 16:42, Tom Lane wrote: Yves Vindevogel [EMAIL PROTECTED]> writes: Can anyone explain me this ? 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) Do you have an index matching that sort key? I'd certainly expect the above to use it if it were there. For the full table case it's not so clear --- an indexscan isn't always better. regards, tom lane Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Pasted Graphic 2.tiff> Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller---(end of broadcast)--- TIP 8: explain analyze is your friend Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(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
Re: [PERFORM] Limit clause not using index
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) On 21 Jun 2005, at 16:42, John A Meinel wrote: 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* Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Limit clause not using index
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 =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Limit clause not using index
Yves Vindevogel [EMAIL PROTECTED] writes: create index ixPrintjobsLoginDescEventdateDesceventtime on tblPrintjobs (loginuser, desceventdate, desceventtime) ; Hmm, that certainly looks like it should match the query. What happens to the EXPLAIN output if you do set enable_sort = false? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Limit clause not using index
[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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Limit clause not using index
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Limit clause not using index
Tobias Brox [EMAIL PROTECTED] 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
Re: [PERFORM] Limit clause not using index
[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
