.
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
On Mon, Feb 11, 2008 at 05:37:51PM -0700, Michael Fuhr wrote:
On Mon, Feb 11, 2008 at 04:58:35PM -0700, fabrix peƱuelas wrote:
If ssl is enable in postgresql decreanse the performance of the database?
How much?
The performance impact of an encrypted connection depends on how
expensive
the impact of an encrypted
connection vs. a non-encrypted connection. The most reliable way
to assess the impact would be to run representative queries over
your data and measure the difference yourself.
--
Michael Fuhr
---(end of broadcast)---
TIP 9
another.
What are your settings for random_page_cost, effective_cache_size,
work_mem, and shared_buffers? If you're using the default
random_page_cost of 4 then what's the EXPLAIN ANALYZE output if you
reduce it to 3 or 2 (after setting enable_seqscan back to on)?
--
Michael Fuhr
, if the calculation contains
immutable functions, it's not skipped.
Don't you mean if the calculation contains VOLATILE functions,
it's not skipped?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
/8.2/interactive/sql-altertable.html
http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
,
c1.relname,
a1.attname;
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
? Is there any chance that
somebody set all of the columns' statistics targets to zero?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
default, which you can see with:
SHOW default_statistics_target;
How exactly are you determining that no statistics are showing up
for this table? Are you running a query like the following?
SELECT *
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'cam_attr';
--
Michael Fuhr
measure that?
enable_mergejoin = off
geqo = off
I've occasionally had to tweak planner settings but I prefer to do
so for specific queries instead of changing them server-wide.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze
, but is there a way I can see what this
value is currently set to?
You could query pg_attribute.attstorage:
http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9
accurate results at the cost of longer ANALYZE times.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
in 8.2.3 and better statistics
resulted in more accurate row count estimates for this and other
parts of the plan. I don't recall if estimates for non-leading-character
matches in earlier versions can benefit from better statistics.
--
Michael Fuhr
---(end of broadcast
On Fri, Mar 30, 2007 at 04:46:11AM -0600, Michael Fuhr wrote:
Have you tried increasing the statistics target on l_pvcp.value?
I ran your queries against canned data in 8.2.3 and better statistics
resulted in more accurate row count estimates for this and other
parts of the plan. I don't
:
http://www.powerpostgresql.com/PerfList
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
such as NOLOCK. Then consider how MVCC handles concurrency
without blocking or the need for dirty reads.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
the
query and the complete output of EXPLAIN ANALYZE (preferably without
wrapping) for both versions?
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
index you want to ignore or do you want the planner to ignore all
indexes? What problem are you trying to solve?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
will continue to show the old
tablespace locations.)
I just tested this and it appeared to work, but this hypothetical
DBA might want to wait for others to comment before proceeding. He
might also want to initdb and populate a test cluster and practice
the procedure before doing it for real.
--
Michael
the data
was loaded. Try running ANALYZE in 8.1 and post the new plans if
that doesn't help.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
might be with DNS queries for (IPv6)
records prior to queries for A (IPv4) records; see this thread from
almost a year ago:
http://archives.postgresql.org/pgsql-general/2005-08/msg00216.php
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have
?
The Solaris 9 shmctl manpage mentions this token:
SHM_LOCK
Lock the shared memory segment specified by shmid in
memory. This command can be executed only by a process
that has an effective user ID equal to super-user.
--
Michael Fuhr
---(end of broadcast
(without ANALYZE) for the
slow one.
As someone else asked, are you running ANALYZE regularly? What
about VACUUM?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
user
connects (not necessarily using psql)?
Beware that setting priorities can have unintended, adverse effects.
Use a search engine to find information about priority inversion
before deciding that query priorities are a good idea.
--
Michael Fuhr
---(end of broadcast
have in postgresql.conf? What version of PostgreSQL are you
running and on what platform? How busy is the system? What's the
output of EXPLAIN UPDATE mytable ...?
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
...
8.2 will support NULL array elements.
http://archives.postgresql.org/pgsql-committers/2005-11/msg00385.php
http://developer.postgresql.org/docs/postgres/arrays.html
test= SELECT '{1,2,NULL,3,4}'::integer[];
int4
{1,2,NULL,3,4}
(1 row)
--
Michael Fuhr
] http://www.opengeospatial.org/docs/99-049.pdf
[2] http://www.postgis.org/
[3] http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
[4] http://www.oracle.com/technology/products/spatial/index.html
--
Michael Fuhr
---(end of broadcast)---
TIP 1
://archives.postgresql.org/pgsql-performance/2005-12/msg00307.php
Your results may vary. If you see substantially different results
then please post the particulars.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives
.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
be hitting geqo_threshold
(default 12)? If so then the following thread might be helpful:
http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
On Mon, Mar 06, 2006 at 07:46:05PM +0100, Joost Kraaijeveld wrote:
Michael Fuhr wrote:
What's your setting?
Default.
Have you tweaked postgresql.conf at all? If so, what non-default
settings are you using?
Are your test results more consistent
if you execute CHECKPOINT between them
[Please copy the mailing list on replies.]
On Mon, Mar 06, 2006 at 09:38:20PM +0100, Joost Kraaijeveld wrote:
Michael Fuhr wrote:
Have you tweaked postgresql.conf at all? If so, what non-default
settings are you using?
Yes, I have tweaked the following settings:
shared_buffers
SETOF foo AS $$
DECLARE
rowfoo%ROWTYPE;
query text;
BEGIN
query := 'SELECT * FROM foo WHERE val = ' || quote_literal(qval);
FOR row IN EXECUTE query LOOP
RETURN NEXT row;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE STRICT;
--
Michael Fuhr
the parameters as
$1, $2, $3, etc., which it didn't.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
parameters ($1, $2, etc.). To execute the query do this:
EXPLAIN ANALYZE EXECUTE stmt (...);
Where ... is the same parameter list you'd pass to the function
(the same values you used in the direct query).
If you need to re-prepare the query then run DEALLOCATE stmt
before doing so.
--
Michael Fuhr
On Tue, Jan 31, 2006 at 07:29:51PM -0800, Joshua D. Drake wrote:
Any ideas?
What does explain analyze say?
Also, have the tables been vacuumed and analyzed?
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
, then run
the query again with EXPLAIN ANALYZE?
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
Index Scan using foo_pkey on foo (cost=0.00..3.92 rows=1 width=6) (actual
time=0.124..0.147 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 1746.173 ms
(3 rows)
--
Michael Fuhr
---(end
recent thread varying plans were
attributed to exceeding geqo_threshold:
http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php
Does your situation look similar?
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0
On Tue, Jan 17, 2006 at 09:04:53AM +, Marcos wrote:
I already read the documentation for to use the SPI_PREPARE and
SPI_EXEC... but sincerely I don't understand how I will use this
resource in my statements.
What statements? What problem are you trying to solve?
--
Michael Fuhr
.
One complication is how to handle rules that run as part of the
insert.
http://www.postgresql.org/docs/faqs.TODO.html
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs
On Wed, Jan 11, 2006 at 12:56:55AM -0700, Michael Fuhr wrote:
WHERE ...
AND doy = EXTRACT(doy FROM now() - '24 hour'::interval)
AND doy = EXTRACT(doy FROM now())
To work on 1 Jan this should be more like
WHERE ...
AND (doy = EXTRACT(doy FROM now() - '24 hour'::interval) OR
doy
://archives.postgresql.org/pgsql-performance/2006-01/msg00104.php
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
is for that column? I want to be able to tell which columns I've
changed the statistics on, and which ones I haven't.
pg_attribute.attstattarget
http://www.postgresql.org/docs/8.1/interactive/catalog-pg-attribute.html
--
Michael Fuhr
---(end of broadcast
] your log_min_messages to WARNING or higher[2].
Or client_min_messages, depending on where you don't want to see
the notice.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
want to match
more than one year.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
that the join condition is correct? Should the query be
returning over a million rows?
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
? You
mentioned increasing work_mem, but what about others like
effective_cache_size, random_page_cost, and shared_buffers?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org
(at least for this
-- one test case):
# explain analyze
select idopont from
(select idopont from muvelet_vonalkod
where muvelet=6859 order by idopont) foo
order by idopont limit 1;
Another workaround is to use OFFSET 0 in the subquery.
--
Michael Fuhr
---(end
functions
that a PL/pgSQL function could call.
As Merlin suggested, maybe Ben could tell us what he wants to do
that he thinks should be written in C or a language other than
PL/pgSQL. Without knowing what problem is to be solved it's near
impossible to recommend an appropriate tool.
--
Michael
On Wed, Dec 21, 2005 at 10:38:10PM +0100, Steinar H. Gunderson wrote:
On Wed, Dec 21, 2005 at 02:24:42PM -0700, Michael Fuhr wrote:
The difference is clear only in specific cases; just because you
saw a 10x increase in some cases doesn't mean you can expect that
kind of increase, or indeed
On Thu, Dec 22, 2005 at 02:08:23AM +0100, Steinar H. Gunderson wrote:
On Wed, Dec 21, 2005 at 03:10:28PM -0700, Michael Fuhr wrote:
That's funny, my biggest problems with PL/PgSQL have been (among others)
exactly with large result sets...
Out of curiosity, do you have a simple test case
this table or
any referring table have triggers? Also, are you regularly vacuuming
and analyzing your tables? Have you examined pg_locks to see if
an unacquired lock might be slowing things down?
--
Michael Fuhr
---(end of broadcast)---
TIP 5
expired, and then continue processing the query?
That way admins could avoid the overhead of posting messages for
short-lived queries that nobody's likely to see in pg_stat_activity
anyway.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting
over a
persistent connection.
That's basically how the application I tested works: it receives
data from a stream and performs whatever insert/update/delete
statements are necessary to update the database for each chunk of
data. Repeat a few thousand times.
--
Michael Fuhr
On Mon, Dec 12, 2005 at 10:23:42AM -0300, Alvaro Herrera wrote:
Michael Fuhr wrote:
The cost depends on your usage patterns. I did tests with one of
my applications and saw no significant performance difference for
simple selects, but a series of insert/update/delete operations ran
about
= on
stats_block_level = on
stats_row_level = on
time: 2:53.76
[Wanders off, swearing that he ran these tests before and saw higher
penalties for block- and row-level statistics.]
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
for
simple selects, but a series of insert/update/delete operations ran
about 30% slower when block- and row-level statistics were enabled
versus when the statistics collector was disabled.
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below
/Python, etc. There's even a PL/sh:
http://pgfoundry.org/projects/plsh/
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
;
y := y + 1;
z := z + 1;
RETURN NEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
SELECT * FROM fooset(1, 2);
y | z
+
20 | 10
21 | 11
(2 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives
of assignments and RETURN NEXT statements,
you don't have to do it that way: you can use a loop, just as you
would with any other set-returning function.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
,
and that works.
You probably shouldn't set statement_timeout on a global basis
anyway, but did you reload the server after you made the change?
Setting statement_timeout in postgresql.conf and then reloading the
server works here in 8.0.4.
--
Michael Fuhr
---(end
statement_timeout to see if the value was set
to what you wanted? Are you sure you edited the right file? As a
database superuser execute SHOW config_file to see what file the
server is using. What exactly did the line look like after you
changed it?
--
Michael Fuhr
on the server?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
duplicate this in 8.0.4; I don't know if anything's
changed since 8.0.2 that would affect the query plan. Could you
post the EXPLAIN ANALYZE output? It might also be useful to see
the output with enable_seqscan disabled.
Have the tables been vacuumed and analyzed recently?
--
Michael Fuhr
will comment.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote:
On Sat, Oct 29, 2005 at 08:24:32 -0600, Michael Fuhr [EMAIL PROTECTED]
wrote:
My tests suggest that a lookup on the referring key is done only
if the referenced key is changed. Here's an example from 8.1beta4;
I used
more efficient for bulk loads?
http://www.postgresql.org/docs/8.0/interactive/populate.html
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining
/pgbuffercache/
Note that pg_buffercache shows only pages in PostgreSQL's buffer
cache; it doesn't show your operating system's cache.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
, not by automatically converting
to and from text.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
PQexecParams()
with a query like INSERT INTO foo VALUES ($1). The $1 is a
placeholder; the other arguments to PQexecParams() provide the data
itself, the data type and length, and specify whether the data is in
text format or binary. See the libpq documentation for details.
--
Michael Fuhr
the table so the statistics
represent only what you did).
You can avoid cached plans by using EXECUTE. You'll have to run
tests to see whether the potential gain is worth the overhead.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget
) (actual time=0.046..0.053 rows=1 loops=1)
Index Cond: (outer.supplierid = s.supplierid)
Total runtime: 0.703 ms
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
be committing
a fix shortly.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list
On Sat, Sep 10, 2005 at 01:03:03AM -0300, Marc G. Fournier wrote:
On Fri, 9 Sep 2005, Michael Fuhr wrote:
INSERT INTO test_check SELECT 1 FROM generate_series(1, 10);
INSERT 0 10
Time: 3492.344 ms
INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10);
INSERT 0 10
Time
, 10);
INSERT 0 10
Time: 3492.344 ms
INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10);
INSERT 0 10
Time: 23578.853 ms
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
regularly?
What version of PostgreSQL are you using?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
superfluous. Or am I missing something?
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
://www.powerpostgresql.com/PerfList/
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
find more detailed discussion in the pgsql-hackers
archives.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
. However, be careful of tuning the system based on one query:
make sure adjustments result in reasonable plans for many different
queries.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http
work_mem/sort_mem too high, though. See Run-time
Configuration in the Server Run-time Environment chapter of the
documentation for more information about these variables.
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list
://archives.postgresql.org/pgsql-committers/2005-04/msg00163.php
http://archives.postgresql.org/pgsql-committers/2005-04/msg00168.php
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
).
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
of a
millisecond; the communications overhead of executing two queries
might make that technique significantly slower than just the server
execution time that EXPLAIN ANALYZE shows.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP
in the documentation:
http://www.postgresql.org/docs/8.0/static/client-authentication.html
If you're trying to do something else then please elaborate, as
it's not clear what you mean by I want to ALTER that user to exclude
the password.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr
0;
See the System Catalogs chapter in the documentation for more
information.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 8: explain analyze is your friend
, then how can we solve it?
If the function deletes all records from the temporary table then
you could use TRUNCATE instead of DELETE. Otherwise you could
VACUUM the table between calls to the function (you can't run VACUUM
inside a function).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr
of dead tuples?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Tue, Jun 28, 2005 at 01:54:08AM +, Karl O. Pinc wrote:
On 06/27/2005 06:33:03 PM, Michael Fuhr wrote:
See timeofday().
That only gives you the time at the start of the transaction,
so you get no indication of how long anything in the
transaction takes.
Did you read
not be following this thread.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
will be able
to access the table or the index until the transaction doing the
DROP INDEX commits or rolls back. Rolling back leaves the index
in place.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 3: if posting/reading
://archives.postgresql.org/pgsql-committers/2005-04/msg00168.php
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
and analyzing the database or the individual tables? Are
any of the tables clustered? If so, on what indexes and how often
are you re-clustering them? What version of PostgreSQL are you using?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast
different?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through
as fast as a sequence scan), and
the planner uses an index scan if it has a choice (i.e., when
enable_seqscan and enable_indexscan are both on). But my test case
and postgresql.conf settings might be different enough from yours
to account for different behavior.
--
Michael Fuhr
http://www.fuhr.org
generatedCurrently, the point at which data begins being
written to disk is controlled by the work_mem configuration
variable.
You might want to test both ways in typical and worst-case scenarios
and see how each performs.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr
up a test server and load a copy of your
database into it. Just beware that because it's bleeding edge, it
might destroy your data and it might behave differently than released
versions.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast
://archives.postgresql.org/pgsql-hackers/2005-03/msg00146.php
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00153.php
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose
Index Scan using foo_date_idx on foo (cost=0.01..15.55 rows=97 width=12)
Index Cond: ((first_date = ('now'::text)::date) AND (last_date =
('now'::text)::date))
(2 rows)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast
1 - 100 of 119 matches
Mail list logo