Re: [GENERAL] Partial Index Too Literal?
That example also reports that it uses the index. Only the "is true" variation insists on seq. scan. On Thu, Jun 26, 2008 at 4:08 PM, Lennin Caro <[EMAIL PROTECTED]> wrote: > use this > > explain analyze select * from result where active = 't'; > > --- On *Thu, 6/26/08, Phillip Mills <[EMAIL PROTECTED]>* wrote: > > From: Phillip Mills <[EMAIL PROTECTED]> > Subject: [GENERAL] Partial Index Too Literal? > To: pgsql-general@postgresql.org > Date: Thursday, June 26, 2008, 7:24 PM > > > Under somewhat unusual circumstances, rows in one of our tables have an > 'active' flag with a true value. We check for these relatively often since > they represent cases that need special handling. We've found through > testing that having a partial index on that field works well. What seems > odd to me, however, is that the index gets used only if the query is a > textual match for how the index was specified. > > That is, using an index defined as 'where active = true': > dev=# explain analyze select * from result where active = true; > QUERY PLAN > > - > Bitmap Heap Scan on result (cost=5.31..472.34 rows=4206 width=1126) > (actual time=7.868..7.868 rows=0 loops=1) >Filter: active >-> Bitmap Index Scan on result_active_idx (cost=0.00..4.26 rows=2103 > width=0) (actual time=4.138..4.138 rows=16625 loops=1) > Index Cond: (active = true) > Total runtime: 7.918 ms > (5 rows) > > dev=# explain analyze select * from result where active is true; > QUERY PLAN > > -- > Seq Scan on result (cost=0.00..537.26 rows=4263 width=1126) (actual > time=55.631..55.631 rows=0 loops=1) >Filter: (active IS TRUE) > Total runtime: 55.668 ms > (3 rows) > > This is version 8.2.6. Is there something I'm missing that could make > these queries ever produce different results? > > >
[GENERAL] Partial Index Too Literal?
Under somewhat unusual circumstances, rows in one of our tables have an 'active' flag with a true value. We check for these relatively often since they represent cases that need special handling. We've found through testing that having a partial index on that field works well. What seems odd to me, however, is that the index gets used only if the query is a textual match for how the index was specified. That is, using an index defined as 'where active = true': dev=# explain analyze select * from result where active = true; QUERY PLAN - Bitmap Heap Scan on result (cost=5.31..472.34 rows=4206 width=1126) (actual time=7.868..7.868 rows=0 loops=1) Filter: active -> Bitmap Index Scan on result_active_idx (cost=0.00..4.26 rows=2103 width=0) (actual time=4.138..4.138 rows=16625 loops=1) Index Cond: (active = true) Total runtime: 7.918 ms (5 rows) dev=# explain analyze select * from result where active is true; QUERY PLAN -- Seq Scan on result (cost=0.00..537.26 rows=4263 width=1126) (actual time=55.631..55.631 rows=0 loops=1) Filter: (active IS TRUE) Total runtime: 55.668 ms (3 rows) This is version 8.2.6. Is there something I'm missing that could make these queries ever produce different results?
Re: [GENERAL] Serialized Access
On Wed, Jun 25, 2008 at 10:21 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > > You might want to look into advisory locking. If your locks don't need > to be longer than the life of an active EntityManager session then you > can probably just issue a native query through the EntityManager to > acquire the lock before doing anything more. Thank you very much for this and the link. (I'm much more an OOP programmer than a DB programmer.) Too bad about the non-portability, but I suppose it had to be. I am a little bemused that the only supported Java persistence strategy is to try the operation, and then react to failure by recreating the entire transaction. At first I assumed I could catch the exception and then re-do the one table operation that had failed...nope, not in a transaction any more.
Re: [GENERAL] Serialized Access
On Wed, Jun 25, 2008 at 10:55 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > Until you benchmark it for your app you really don't know how > inefficient it really is compared to pessimistic locking. > Sure. The question was about more about finding the right approach/layer for implementing pessimistic locking so that comparisons *could* be made.
[GENERAL] Serialized Access
I'm working on an application that uses EJB3 entities in JBoss, with Hibernate and a PostgreSQL database. One of the entity tables needs consistent, synchronized updates to rows in an environment where telling the user that their operation failed and starting over is not an option. Because it's the default, I've used EJB3's optimistic locking with a strategy of catching EJBExceptions and retrying my updates. Since contention can be frequent, the overhead (and extra logic) for this seems like a waste. I would like to try pessimistic locking and compare the results, but here's where my problem arises. EJB documentation passes this off to the application server, which considers it a ORM problem. Hibernate says it doesn't add any lock features beyond what JDBC and the database provide In the end, I need Java code in a stateless bean that causes serialized access to database rows that are under the control of an EntityManager, but the approach to doing that is eluding me. So, I'm not entirely sure this is a PostgreSQL question, but if not perhaps someone can tell me which layer of this architecture *is* responsible. :-) Thanks in advance.
