First, what version of postgres, and have you run VACUUM ANALYZE recently? Also, please attach the result of running EXPLAIN ANALYZE. (eg, explain analyze select s.* from shipment ...)
I'm using postgres 8.0. I wish I could paste explain analyze, but I won't be at work for a few days. I was hoping some Postgres/SQL experts here would be able to simply look at the query and make recommendations because it's not a very difficult or unique query.
It's very possible that you don't have up-to-date statistics, which causes postgres to make a bad estimate of what the fastest plan is.
I run VACUUM ANALYZE religiously. I even dumped the production database and used it as my test database after a full vacuum analyze. It's really as fresh as it can be.
I don't know how to make Hibernate do what you want, but if you change the query to using subselects (not all databases support this, so hibernate might not let you), you can see a performance improvement.
Yes, Hibernate supports sub-selects. In fact, I can even drop down to JDBC explicitly, so whatever SQL tricks out there I can use will work on Hibernate. In what way will sub-selects improve this query?
Also sometimes using explicit joins can be worse than just letting the query manager figure it out. So something like select s.* from shipment s, carrier_code cc, carrier c, ... where s.carrier_code_id = cc.id and c.id = cc.carrier_id and ....
I think I can avoid using joins in Hibernate, but it makes the query harder to maintain. How much of a performance benefit are we talking with this change? Since hibernate is an object language, you don't actually have to specify many joins. You can use the "dot" notation.
Query query = session.createQuery(
"select shipment " +
"from Shipment shipment " +
" inner join shipment.cargoControlNumber.carrierCode.carrier.persons person " +
" inner join shipment.currentStatus currentStatus " +
" inner join currentStatus.releaseCode releaseCode " +
" left join fetch shipment.currentStatus " +
"where " +
" person.id = :personId and " +
" shipment.isPurged = false and " +
" releaseCode.number = '9' and " +
" currentStatus is not null and " +
" currentStatus.date >= current_date - 31 " +
"order by currentStatus.date desc"
);
query.setParameter( "personId", personId );
query.setFirstResult( firstResult );
query.setMaxResults( maxResults );return query.list();
As you can see, it's fairly elegant language and maps to SQL quite well.
But again, since this is generated from another program (Hibernate), I really don't know how you tell it how to tune the SQL. Probably the biggest "non-bug" performance improvements are from tuning the SQL.
I agree, but the ones I've tried aren't good enough. I have made these indexes that apply to this query as well as others in my from looking at my SQL scripts. Many of my queries have really sped up to 14 milliseconds from these indexes. But I can't make this query run any faster.
CREATE INDEX carrier_to_person_person_id_idx ON carrier_to_person USING btree (person_id);
CREATE INDEX carrier_to_person_carrier_id_idx ON carrier_to_person USING btree (carrier_id);
CREATE INDEX carrier_code_carrier_id_idx ON carrier_code USING btree (carrier_id);
CREATE INDEX shipment_carrier_code_id_idx ON shipment USING btree (carrier_code_id);
CREATE INDEX current_status_date_idx ON shipment_status USING btree (date);
CREATE INDEX shipment_current_status_id_idx ON shipment USING btree (current_status_id);
CREATE INDEX shipment_status_shipment_id_idx ON shipment_status USING btree (shipment_id);
Thanks for your responses everyone. I'll try and get you that explain analyze. I'm just not at work at the moment but this is a problem that I'm simply puzzled and worried about. I'm getting all of this from CVS on my work server.
Ken
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
