[PERFORM] Index Being Ignored?

2006-06-30 Thread Joe Lester
I have a index question. My table has 800K rows and I a doing a basic query on an indexed integer field which takes over 2 seconds to complete because it's ignoring the index for some reason. Any ideas as to why it's ignoring the index? I'm using postgres 8.0.2.SELECT count(*) FROM purchase_order_i

[PERFORM] newly created database makes queries run 300% faster

2006-06-30 Thread Ksenia Marasanova
Hi, Alfter hours of adjusting performance of the queries in my Postgres 7.3 database - reprogramming the queries, VACUUMing, changing value of enable_seqscan - I gived it up, recreated the database and transferred the dump of the old database into it. The queries went from 15 sec to 50 msec!! Wow

Re: [PERFORM] Index Being Ignored?

2006-06-30 Thread Markus Schaber
Hi, Joe, Joe Lester wrote: > Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual > time=2205.688..2205.724 rows=1 loops=1) > -> Seq Scan on purchase_order_items (cost=0.00..21978.08 rows=286882 > width=0) (actual time=0.535..2184.405 rows=7458 loops=1) > Filter: (expected_quant

Re: [PERFORM] newly created database makes queries run 300% faster

2006-06-30 Thread Brad Nicholson
Ksenia Marasanova wrote: > Hi, > > Alfter hours of adjusting performance of the queries in my Postgres > 7.3 database - reprogramming the queries, VACUUMing, changing value of > enable_seqscan - I gived it up, recreated the database and transferred > the dump of the old database into it. > The que

Re: [PERFORM] Index Being Ignored?

2006-06-30 Thread Tom Lane
Joe Lester <[EMAIL PROTECTED]> writes: > SELECT count(*) FROM purchase_order_items WHERE expected_quantity > '0' > Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual > time=2205.688..2205.724 rows=1 loops=1) >-> Seq Scan on purchase_order_items (cost=0.00..21978.08 > rows=286882

Re: [PERFORM] FWD: Update touches unrelated indexes?

2006-06-30 Thread Jozsef Szalay
Hi Tom, >This surprises you why? I don't know anything about how PG stores keys along with their references to the actual rows but my assumption was that that reference is some sort of an index into a table that maps the reference to an actual disk/file address. So even if the row or the page wit

Re: [PERFORM] Index Being Ignored?

2006-06-30 Thread Joe Lester
great! Thanks Markus and Tom! On Jun 30, 2006, at 10:29 AM, Markus Schaber wrote: Hi, Joe, Joe Lester wrote: Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual time=2205.688..2205.724 rows=1 loops=1) -> Seq Scan on purchase_order_items (cost=0.00..21978.08 rows=286882 width=0

Re: [PERFORM] FWD: Update touches unrelated indexes?

2006-06-30 Thread Alvaro Herrera
Jozsef Szalay wrote: > >he probably needs to think harder about whether every one of those > >indexes is really carrying its weight. > > Unfortunately all of those indexes are required by the application. It > appears that the only viable option I have is to drop the indexes and > recreate them a

Re: [PERFORM] [pgadmin-support] 100% CPU

2006-06-30 Thread Jim C. Nasby
moving to -performance On Fri, Jun 30, 2006 at 08:32:39AM +0100, Peter Newman wrote: > Good Morning > > I am new to postgres and have been asked to look at a server where we > truncate a table then load data. > > The CPU has started to hit 100% usage during this process. > > Can you please des

Re: [PERFORM] Index Being Ignored?

2006-06-30 Thread Alvaro Herrera
Joe Lester wrote: > I have a index question. My table has 800K rows and I a doing a basic > query on an indexed integer field which takes over 2 seconds to > complete because it's ignoring the index for some reason. Any ideas > as to why it's ignoring the index? I'm using postgres 8.0.2. > >