The moral of the story is that you're probably better off running a
bunch of small selects than in trying to optimize things with one
gargantuan select.
Ever experiment with loading the parameters into a temp table and
joining to that?
Also, it might be worth re-testing that conclusion
You might consider just selecting your primary key or a set of
primary keys to involved relations in your search query. If you
currently use select * this can make your result set very large.
Copying all the result set to the temp. costs you additional IO
that you propably dont need.
It is
Hi,
On Tue, 9 May 2006, PFC wrote:
snipp/
Back to the point : I can't use the temp table method, because temp
tables are too slow.
Creating a temp table, filling it, analyzing it and then dropping it
takes about 100 ms. The search query, on average, takes 10 ms.
just some thoughts:
You
Hi,
On Tue, 9 May 2006, PFC wrote:
You might consider just selecting your primary key or a set of
primary keys to involved relations in your search query. If you
currently use select * this can make your result set very large.
Copying all the result set to the temp. costs you additional IO
Additionally to your query you are already transferring the whole result
set multiple times. First you copy it to the result table. Then you
read it again. Your subsequent queries will also have to read over
all the unneeded tuples just to get your primary key.
Considering that the
On Tue, May 09, 2006 at 12:10:37PM +0200, PFC wrote:
Yes, but in this case temp tables add too much overhead. I wish
there were RAM based temp tables like in mysql. However I guess the
current temp table slowness comes from the need to mark their existence in
the system
It would be interesting to know what the bottleneck is for temp tables
for you. They do not go via the buffer-cache, they are stored in
private memory in the backend, they are not xlogged. Nor flushed to
disk on backend exit. They're about as close to in-memory tables as
you're going to get...
Hi!
On Mon, Apr 03, 2006 at 11:56:13PM +0100, Robert Watson wrote:
This is why it's disabled by default, and the jail documentation
specifically advises of this possibility. Excerpt below.
Ah, I see, glad to see it's accurately documented.
As it has been for the last five years, I
I am currently looking at some performance issues. Our product uses the
postgres as the backend
and it uses a huge storage space.
Is there a script available, which does the following:
1. When you point it to a postgres installation, - 1.the script first
identifies what platform
PFC [EMAIL PROTECTED] writes:
Feature proposal :
A way to store query results in a named buffer and reuse them in the
next
queries.
Why not just fix the speed issues you're complaining about with temp
tables? I see no reason to invent a new concept.
(Now, just fix might be
PFC [EMAIL PROTECTED] writes:
I really like this. It's clean, efficient, and easy to use.
This would be a lot faster than using temp tables.
Creating cursors is very fast so we can create two, and avoid doing
twice the same work (ie. hashing the ids from the results to
Creating cursors for a simple plan like a single sequential scan is fast
because it's using the original data from the table.
I used the following query :
SELECT * FROM bookmarks ORDER BY annonce_id DESC LIMIT 20
It's a backward index scan + limit... not a seq scan. And it's
Documentation updated to mention dimmensions are not enforced.
---
Stefan Kaltenbrunner wrote:
Thomas Hallgren wrote:
I can create a function that takes a two dimension int array:
CREATE FUNCTION twodims(int[][])
On Mon, 08 May 2006 03:58:54 +0800
imacat [EMAIL PROTECTED] wrote:
On Sun, 07 May 2006 13:09:48 -0400
Tom Lane [EMAIL PROTECTED] wrote:
imacat [EMAIL PROTECTED] writes:
I cannot compile PL/Perl and PL/Python under x86_64.
Works fine for me (without any tinkering with flags) on Fedora
Does the time for commit change much if you leave out the analyze?
Yes, when I don't ANALYZE the temp table, commit time changes from 30 ms
to about 15 ms ; but the queries get horrible plans (see below) :
Fun thing is, the rowcount from a temp table (which is the problem here)
should
On 5/9/06, PFC [EMAIL PROTECTED] wrote:
You might consider just selecting your primary key or a set of
primary keys to involved relations in your search query. If you
currently use select * this can make your result set very large.
Copying all the result set to the temp. costs you
SELECT * FROM somewhere WHERE id IN (SELECT id FROM result)
Well, you can either
SELECT * FROM somewhere JOIN (SELECT id FROM result GROUP BY id) AS
a USING (id);
It's the same thing (and postgres knows it)
You might want to use PL to store values, say PLperl, or even
Bruce Momjian pgman@candle.pha.pa.us writes:
Documentation updated to mention dimmensions are not enforced.
This patch seems entirely pointless, since the point is already made
(with correct spelling even) in the very next line.
regards, tom lane
On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote:
In my particular case (don't know about the SQL standard or other
cases),
it'd be enough if when an inherited table is created:
- A primary key in the inherited table is created with the same columns
as
the super
PFC [EMAIL PROTECTED] writes:
Fun thing is, the rowcount from a temp table (which is the problem
here)
should be available without ANALYZE ; as the temp table is not concurrent,
it would be simple to inc/decrement a counter on INSERT/DELETE...
No, because MVCC rules still apply.
I see that in CVS head, if you turn on standard_conforming_strings,
it still whines about backslashes:
regression=# set standard_conforming_strings to 1;
SET
regression=# select '1234\5678';
WARNING: nonstandard use of escape in a string literal
LINE 1: select '1234\5678';
^
HINT:
On 5/10/06, Dhanaraj M [EMAIL PROTECTED] wrote:
2. walks through all known tuning parameters (in /etc/system and
postgresql.conf) on the system
3. lists out what parameters are optimal/suboptimal for that
platform, and makes recommendations.
I thought that's why people still have DBAs ;}
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Documentation updated to mention dimmensions are not enforced.
This patch seems entirely pointless, since the point is already made
(with correct spelling even) in the very next line.
Thanks, reverted.
--
Bruce Momjian
Does anyone know how to discover the tables (with schemas or oid) involved
on a select statement?
I tried EXPLAIN but it only reports the table names, not schemas or oid.
I am developing a SNAPSHOT project for PostgreSQL called Pg::snapshots. It
can be found here:
On 5/2/06, Brandon Black [EMAIL PROTECTED] wrote:
On 4/30/06, Heikki Linnakangas [EMAIL PROTECTED] wrote: On Fri, 28 Apr 2006, Brandon Black wrote: I dug around in CVS to have a look for this, and I did eventually find
it (well, I found the corresponding docs patch that removed the note about
Hi
Yesterday I helped a guy on irc with a locking problem, he thought
that locking in postgresql was broken. It turned out that he had a PHP
function that he called inside his transaction and the function did BEGIN
and COMMIT. Since BEGIN inside a transaction is just a warning what
happend was
26 matches
Mail list logo
