pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLimit clause not using index
14+ messages / 7 participants
[nested] [flat]
* Limit clause not using index
@ 2005-06-21 14:33 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 3 replies; 14+ messages in thread
From: Yves Vindevogel @ 2005-06-21 14:33 UTC (permalink / raw)
To: pgsql-performance
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: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Limit clause not using index
@ 2005-06-21 14:40 Bricklen Anderson <BAnderson@PresiNET.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
2 siblings, 0 replies; 14+ messages in thread
From: Bricklen Anderson @ 2005-06-21 14:40 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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.
_______________________________
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 14:42 John A Meinel <john@arbash-meinel.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
2 siblings, 1 reply; 14+ messages in thread
From: John A Meinel @ 2005-06-21 14:42 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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*
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 14:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
2 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2005-06-21 14:42 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
Yves Vindevogel <yves.vindevogel@implements.be> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 14:57 Yves Vindevogel <yves.vindevogel@implements.be>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: Yves Vindevogel @ 2005-06-21 14:57 UTC (permalink / raw)
To: pgsql-performance
These 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) ;
On 21 Jun 2005, at 16:42, Tom Lane wrote:
> Yves Vindevogel <yves.vindevogel@implements.be> 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: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Limit clause not using index
@ 2005-06-21 15:07 Yves Vindevogel <yves.vindevogel@implements.be>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 14+ messages in thread
From: Yves Vindevogel @ 2005-06-21 15:07 UTC (permalink / raw)
To: pgsql-performance
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: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Limit clause not using index
@ 2005-06-21 15:10 Yves Vindevogel <yves.vindevogel@implements.be>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 14+ messages in thread
From: Yves Vindevogel @ 2005-06-21 15:10 UTC (permalink / raw)
To: pgsql-performance
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:
> These 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) ;
>
>
> On 21 Jun 2005, at 16:42, Tom Lane wrote:
>
>> Yves Vindevogel <yves.vindevogel@implements.be> 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: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
>
> Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
>
> Web: http://www.implements.be
>
> First they ignore you. Then they laugh at you. Then they fight you.
> Then you win.
> Mahatma Ghandi.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Limit clause not using index
@ 2005-06-21 15:14 John A Meinel <john@arbash-meinel.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 14+ messages in thread
From: John A Meinel @ 2005-06-21 15:14 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 14+ messages in thread
From: Tom Lane @ 2005-06-21 15:30 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
Yves Vindevogel <yves.vindevogel@implements.be> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 19:46 Tobias Brox <tobias@nordicbet.com>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tobias Brox @ 2005-06-21 19:46 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
[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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 21:08 Michael Fuhr <mike@fuhr.org>
parent: Tobias Brox <tobias@nordicbet.com>
1 sibling, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2005-06-21 21:08 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: John A Meinel <john@arbash-meinel.com>; Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
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/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 21:11 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 14+ messages in thread
From: Kevin Grittner @ 2005-06-21 21:11 UTC (permalink / raw)
To: john@arbash-meinel.com; tobias@nordicbet.com; +Cc: yves.vindevogel@implements.be; pgsql-performance
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 <tobias@nordicbet.com> 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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-21 21:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tobias Brox <tobias@nordicbet.com>
1 sibling, 1 reply; 14+ messages in thread
From: Tom Lane @ 2005-06-21 21:20 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: John A Meinel <john@arbash-meinel.com>; Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Limit clause not using index
@ 2005-06-22 03:54 Tobias Brox <tobias@nordicbet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Tobias Brox @ 2005-06-22 03:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tobias Brox <tobias@nordicbet.com>; John A Meinel <john@arbash-meinel.com>; Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
[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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2005-06-22 03:54 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-21 14:33 Limit clause not using index Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-21 14:40 ` Bricklen Anderson <BAnderson@PresiNET.com>
2005-06-21 14:42 ` John A Meinel <john@arbash-meinel.com>
2005-06-21 15:07 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-21 15:14 ` John A Meinel <john@arbash-meinel.com>
2005-06-21 19:46 ` Tobias Brox <tobias@nordicbet.com>
2005-06-21 21:08 ` Michael Fuhr <mike@fuhr.org>
2005-06-21 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-22 03:54 ` Tobias Brox <tobias@nordicbet.com>
2005-06-21 14:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-21 14:57 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-21 15:10 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-21 15:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-21 21:11 Re: Limit clause not using index Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox