* Jay Greenfield (jag@timberline.ca) wrote:
> Database has one table with 1.2 million rows
> Query:
>
> UPDATE ntdn SET gha=area/10000
>
> I could post the EXPLAIN ANALYZE results but its 4,000+ lines long
How do you get 4,000+ lines of explain analyze for one update query in a
database with only one table? Something a bit fishy there. Perhaps you
mean explain verbose, though I don't really see how that'd be so long
either, but it'd be closer. Could you provide some more sane
information?
> I've run various tests on a number of Postgres parameters; none of which
> have come close to Access' time of 5.00 min. Postgres times range between
> 24 min and 121 min.
>
> Some of the Postgres variables and ranges I've tested.
> work_mem: 1,000 to 2,000,000
> temp_buffers: 1,000 to 10,000
> shared_buffers: 1,000 to 64,000
> sort_mem: 1,024,000
> fsync on / off
>
> Why does Access run so much faster? How can I get Postgres to run as fast
> as Access?
While it's true that Access almost certainly takes some shortcuts, 24
minutes for an update across 1.2 millon rows seems an awefully long time
for Postgres. Is this table exceptionally large in same way (ie: lots
of columns)? I expect running with fsync off would be closer to 'Access
mode' though it has risks (of course). Also, it might be faster to
insert into a seperate table rather than run a huge update like that in
Postgres. Also, if there are indexes on the table in question, you
might drop them before doing the update/insert and recreate them after
the query has finished.
You really havn't provided anywhere near enough information to figure
out what the actual problem is here. Access does take shortcuts but the
times you're posting for Postgres seem quite far off based on the
hardware and commands you've described...
Thanks,
Stephen