Postgresql performance

steve44

I'm new at the Postgresql database, and have viewed all kinds of info on the web, but am not seeing what may be the problem, so maybe you can help. I'm not using PHP, but am using Postgres, which I see people here are using.

I'm using a Postgres database with an MS Access front end thru an ODBC connection. The idea is that I've read Postgres is better than Jet at multiuser performance. I'm not seeing this however, and am seeing that it takes hundreds of times longer to get results than with using an Access table.

I'm linked to a Postgres database running under Linux. I've upped the shared memory on Linux to 128MB, and have the postmaster using most of the 128MB for shared memory.

My query consists of 6 tables joined together. The main table, "employees" has about 100,000 records, then there's a department table, division table, etc that get joined in based on deptid stored in employee, and the department record has a divid to join in the division record. I've created indexes on these id fields to help with the join. The department table has a few hundre records, and the other 5 tables it joins in all have less than 100 records.

An EXPLAIN however says it is doing a HASH JOIN. Should I be trying to get it to do an INDEX SCAN? Would that be faster?

Basically I'm trying to see how long does it take to read in the first record, given a SELECT of the entire employee table (no WHERE). Maybe this is a bad test, but with an Access table I get the first record in milliseconds, and with Postgres it takes from 90 to over 200 seconds to read the first record, depending on if I'm using ADO or DAO, and I've tried it with ODBC and ODBCDirect.

Any ideas? The query I'm using is complex given the number of tables, but a simplier one using only three of the tables that is similiar is:
SELECT *
FROM (employee LEFT JOIN dept ON employee.deptid = dept.deptid) LEFT JOIN division ON dept.divid = division.divid;

Thanks in advance for any ideas or comments!

bastien

it is really slow...and it also depends on where in the network the postgre db resides, network traffic may affect it..

i would change the setup to use an php interface on a webserver

hth

edwardsbc

I agree that part of the problem could be in OLE DB / ODBC implementation. I assume you millisecond Access result came from running the query from within the main table's MDB file on the same machine. Have you tried the JOIN hitting a copy of the MDB on a remote machine through ODBC (or whatever)?

Try the JOIN from the postgres client on the same machine as the postgres database. Is the speed noticably different?

ultraslacker

You did vacuum analyze after creating the indexes, right?

Where exactly is the slowdown - what does explain analyze tell you?

steve44

I did do a vacuum analyze after the indexes were created.

Here's the more complicated JOIN I'm trying along with the EXPLAIN results for it:

select station.statid, station.code, division.divenumber, dept.depenumber, station.name,
station.number, station.assist, station.assistno, station.stationtypeid, station.overhead, station.jack,
station.circuit, station.date, station.deptid, dept.code as deptcode, dept.ename, dept.divid,
division.code as divcode, division.ename, station.stationcharge, station.location, station.room,
station.phone2, station.email, stntype.name as type, station.directorystation,
station.callaccountingstation, esite.sitecode, station.siteid, station.multiset, station.customflag,
station.aux1, station.aux2, station.cos from esite right join (division inner join (dept inner
join (station left join stntype on station.stationtypeid = stntype.stationtypeid) on dept.deptid =
station.deptid) on division.divid = dept.divid) on esite.siteid = station.siteid order by station.name;

NOTICE: QUERY PLAN:

Hash Join (cost=23.58..10849.20 rows=99297 width=36)
-> Hash Join (cost=22.56..9110.49 rows=99297 width=32)
-> Hash Join (cost=21.43..7371.65 rows=99297 width=28)
-> Hash Join (cost=1.07..5613.61 rows=99297 width=20)
-> Seq Scan on station (cost=0.00..5115.97 rows=99297 widt
h=16)
-> Hash (cost=1.06..1.06 rows=6 width=4)
-> Seq Scan on stntype (cost=0.00..1.06 rows=6 width
=4)
-> Hash (cost=18.48..18.48 rows=748 width=8)
-> Seq Scan on dept (cost=0.00..18.48 rows=748 width=8)
-> Hash (cost=1.11..1.11 rows=11 width=4)
-> Seq Scan on division (cost=0.00..1.11 rows=11 width=4)
-> Hash (cost=1.01..1.01 rows=1 width=4)
-> Seq Scan on esite (cost=0.00..1.01 rows=1 width=4)

The times that were in the milliseconds to read the first record were when I had the table in Access (in another mdb and linked). Even on another machine on the network the times for a linked Access table were in milliseconds. There's no other network traffic here on my 100 Base T network.

Also I was seeing Postgres only put 18K or so on the network to get the first record of the query. But again, Postgres was taking 90 to 200 seconds using a linked table in Access (ODBC DSN), or using ODBCDirect just to get the first record read with my VB code in Access.

If anyone has any ideas, I'd greatly appreciate it! Thanks!

Sxooter

right join (division inner join (dept inner
join (station left join stntype on station.stationtypeid = stntype.stationtypeid) on dept.deptid =
station.deptid) on division.divid = dept.divid) on esite.siteid = station.siteid order by station.name;

I notice that you aren't getting any index scans, so that means that postgresql is pretty much doing this the brute force way. Specifically, do you have indexes on the fields station.stationtypeid, stntype.stationtypeid, dept.deptid, station.deptid, division.divid, dept.divid, esite.siteid, and station.siteid?

If not, then it's no wonder it's taking a while to return that complex of a query. Try creating indexes on those fields, do an analyze, and try the query again and let us know if that helps.

ultraslacker

The reason postgresql isn't using any index scans is because all employee rows are grabbed. Indexes are pointless then!

You are actually comparing the time it takes access to return one row with the time it takes postgresql to return all - your query deals with 99297 rows.

steve44

That's what I'm thinking, that even though my program is only reading the first record and then stopping, Postgres is actually retrieving all the rows in the recordset.

Another much simplier test I did was to read all the rows in the "station" table, with no JOIN, just a plain SELECT * FROM station. Remember there's about 100,000 records in that table. Again, my program is stopping after reading the first record to get the time, but Postgres is getting all the records (even though it may be only returning one over the network, based on the low network traffic). I think Postgres itself has pulled together all the records in the recordset. This was taking 8 to 9 seconds to read the first record. When that table had only 15,000 records, it took about a full second to read the first record. Compare this to the table in Access itself, it could open the 100,000 record table and read the first record in .037 seconds.

ultraslacker

I think you are comparing apples and oranges. Access is using an index - postgresql isn't - given the query postgres has no better option. It is complete nonsense that access is doing the join query in milliseconds - it is only returning the first row. Add the primary key of the first row in the ON clause in postgres. Or try returning all the rows in Access.

Anyway, this is hardly a test for multiuser performance. You are doing a simple select - no table/row locking, transactional overhead... Access is absolutely a nightmare when it comes to concurrency.

steve44

I'm sure you are right. There are keys on the primary indexes, for example deptid in the dept table, however, I have no key on the main table "station" for the foreign key deptid. Do I need a key there since the station record is read anyway?

But it probably is different in the methods it uses between Access and Postgres. Unfortunately though, that means the forms, the way they currently work under Access are painfully slow when using data under Postgres. Since the form uses this complex query, it takes over 30 minutes for the form to load and show the first record, where if all the data is under Access tables, the form appears right away with the first record.

I'm not sure what the solution is, but the general idea is a search for another database backend that does better with multiusers than Access does, but we don't want to rewrite the entire front end to get it to work.

ultraslacker

Sorry I couldn't be more helpful but I haven't used access that much. FWIW, I believe access is using a cursor - postgres has the same thing, but I dont know if that is the answer here, as you can't update through it - but it would give you the data which you could use in turn for an update. You could use a bound cursor, which would cache the query plan.

Postgresql Cursors

Anyway, I don't think the current sql will please any database, except for Access.

--
Yes, if you were writing a query that used indexes, then station.deptid should be indexed, unless it has a low cardinality.

Sxooter

Try adding offset and limit to your query, like so:

select (allyourquerybelongtohere) limit 1 offset 0;

and see what you get. You can use limit and offset like a cursor, but postgresql should return that one row fast, since it can now use an index to find the row you want.

If you want one row, ask for one row, if you want 99000 rows, ask for 99000 rows. Just because access seems faster doesn't mean it is. The actual time to materialize all 99k rows in access is likely no faster than postgresql, access is just full of short cuts for making things look fast so joe user doesn't have to actually optimize his queries.