I’m running postgresql 8.1.0 with postgis 1.0.4 on a
FC3 system, 3Ghz, 1 GB memory.
I am using COPY to fill a table that contains one postgis
geometry column.
With no geometry index, it takes about 45 seconds to COPY
one file.
If I add a geometry index, this time degrades. It
k
keep the input data on a separate drive
from my pg tables? If so, some pointers on the best way to set that up
would be appreciated.
Please let me know if anyone has
additional ideas.
-Original Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Rick
once the table has,
say, 50 million rows.
> -Original Message-
> From: Luke Lonergan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 01, 2005 9:27 PM
> To: Rick Schumeyer; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] COPY into table too slow with index: n
I installed another drive in my linux pc in an attempt to
improve performance
on a large COPY to a table with a geometry index.
Based on previous discussion, it seems there are three
things competing for the hard
drive:
1)
the input data file
2)
the pg table
3)
Based on a suggestion on the postgis list, I partitioned my 80 million (for
now) record table into
subtables of about 230k records (the amount of data collected in five
minutes). At the moment
I have 350 subtables.
Everything seems to be great…COPY time is ok, building a
geometric in
I have two index questions. The first is about an
issue that has been recently discussed,
and I just wanted to be sure of my understanding.
Functions like count(), max(), etc. will
use sequential scans instead of index scans because the
index doesn’t know which rows
are actually visibl
That makes a lot of sense. Sure enough, if I change the query from
WHERE x > 0 (which return a lot of rows) to
WHERE x > 0 AND x < 1
I now get an index scan.
> As for why you see index usage in your first example query and not your
> second: compare the number of rows in question. An index is e
On a linux box (Linux db1 2.6.18.8-md #1 SMP Wed May 23 17:21:37 EDT
2007 i686 GNU/Linux)
I edited postgresql.conf and changed:
shared_buffers = 5000 work_mem = 16384
max_stack_depth = 4096
and then restarted postgres. The puzzling part is that postgres
actually started. When I hav
