Sebastien Lemieux wrote:
Hi,
I need to extend postgreSQL to support some specific function I will
code in C (ideally C++ if it can work!).
Be very carefull with using C++ functions inside postgresql. If the C++
code throws an exception the postgresql backend will get a SIGABRT and
terminate
I noticed a change between our 7.2 and 7.3 postgresql database.
On 7.2:
template1=> select '_' < '5';
?column?
--
f
(1 row)
On 7.3:
template1=# select '_' < '5';
?column?
--
t
(1 row)
Any reason for this change?
dave
---(end of broadcast)
My first guess would be that you're not running in "C" locale
on the 7.3 system. I get false on my 7.3.1 system in C locale,
but if I compare the two strings in C using en_US for example I
seem to get results like the above ('_'<'5' is true).
It turns out our 7.3 database was somehow initd with lo
I'm trying to keep a variable around for the duration of a transaction.
Unfortunately, the "SET LOCAL" command doesnt allow me to create my own
variable. Also, the "CREATE TEMP TABLE ... ON COMMIT DELETE ROWS" isnt
yet implemented.
But, I believe I can implement it using a TEMP table like this
I just tried an experiment that I thought would improve the performance
of the PostGIS spatial index.
The old way used a BOX (bounding box of 4 doubles) as the key in the index.
The new way uses a bounding box of 4 single-precision floats (its only
16 bytes long - a BOX is 32). I thought that
Couple of observations:
1. Are you sure your data is handled as 32 bit all the way through? Run
time casting will offset performance gains on 32 bit floats. Is your
comparison routine casting to double?
I thought this might be the case - but I thought it would be small. The
only place it might
Tom Lane wrote:
I'd suggest profiling the backend with both key types to get an idea of
where the time is going.
I've been trying to use gprof to do some profiling, but I'm having
troubles. Whats the best way to profile?
PS: actually, allowing for the 12-byte index tuple overhead, you
couldn't
Humm -- strange results here:
typedef struct
{
float xmin;
float ymin;
float xmax;
float ymax;
} BOX2DFLOAT4;
This takes about 18,000 ms to do a nested query with 10,000 iterations.
typedef struct
{
float xmin;
float ymin;
float xmax;
I tracked the problem down to the "penalty" function used to build the
tree. Basically, it compares the area of the bounding boxes.
There wasnt enough precision in the area calculations - instead of
giving 0.0 it would give numbers in the[+-]10^-6 range. This really
screwed up how the tree w
I have a table with about 16,000,000 rows in it.
When I do a:
SELECT * FROM LIMIT 1;
it takes about 10 minutes (thats about how long it takes to do a full
sequential scan).
I had originally thought that there might be a large number of
"wasted/retired" tuples in the table so I "vacuum analysed"
Gaetano Mendola wrote:
David Blasby wrote:
I have a table with about 16,000,000 rows in it.
When I do a:
SELECT * FROM LIMIT 1;
it takes about 10 minutes (thats about how long it takes to do a full
sequential scan).
I had originally thought that there might be a large number of
"wasted/re
Tom Lane wrote:
INFO: "csn_edges": found 0 removable, 16289929 nonremovable row
versions in 2783986 pages
That works out to just under 6 rows per 8K page, which wouldn't be too
bad if the rows are 1K wide on average, but are they? (You might want
to run contrib/pgstattuple to get some exact inf
I did a CLUSTER - it took just under 12 hours. The original load of the
table (including parsing all the geometries) took about 4 hours (+4 to
build the indexes and vacuum analyse). I dont know why the cluster is
so much slower...
My "SELECT * FROM LIMIT 1;" is now fast.
Thanks for the help!
I've been noticing query planning to be different for a cursor-based
select and normal select.
For example, my query looks like this:
=# SELECT
The query takes about 1/4 of a second.
But, for:
=# BEGIN;
=# DECLARE mycursor BINARY CURSOR FOR SELECT ;
=# FETCH ALL IN mycursor;
The same [SQL] q
Tom Lane wrote:
You may need to bite the bullet and try to devise some real selectivity
estimation techniques for your geometric operators. The stuff in
src/backend/utils/adt/geo_selfuncs.c at the moment is all just stubs :-(
I've already done this - it actually gives pretty accurate estimates.
B
15 matches
Mail list logo
