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.
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
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,
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
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
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
rvponp=# explain analyze select * from tblPrintjobs order by loginuser, desceventdate, desceventtime ;
QUERY PLAN
Yves Vindevogel wrote:
rvponp=# explain analyze select * from tblPrintjobs order by
loginuser, desceventdate, desceventtime ;
QUERY PLAN
Sort
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?
[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
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
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
[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
13 matches
Mail list logo
