Tom Lane wrote:
> The explain shows no such thing. What is the *actual* runtime of
> each plan per EXPLAIN ANALYZE, please?
Ok, it took 3.5 hours to complete. :-/
This is with the default cpu_tuple_cost = 0.01:
Nested Loop (cost=252.80..233010147.16 rows=1035480320 width=98)
(actual time=0.36
Hi,
I have a series of tables with identical structure. Some contain a few
thousand rows and some contain 3,000,000 rows. Another applicate writes
the rows and my applicate reads then just by selecting where pk >
last_seen_pk limit 2000.
I've found that one of the tables, when selecting from it
Hi everybody,
One of our clients was using SQL-Server and decided to switch to
PostgreSQL 8.0.1.
Hardware: Dual processor Intel(R) Xeon(TM) CPU 3.40GHz
OS: Enterprise Linux with 2.6.9-5 SMP kernel
Filesystem: ext3
SHMMAX: $ cat /proc/sys/kernel/shmmax
6442450944 <--- beleive that's ~6.5 GB, tot
Thanks a lot John for the correct search terms. :-)
The suggestion in
http://archives.postgresql.org/pgsql-performance/2005-04/msg00029.php to
add a constraint that checks (finishtime >= starttime) does not make a
difference for me. Still seq scans are used.
The width solution explained in
http:/
On Apr 21, 2005, at 7:49 AM, Shoaib Burq (VPAC) wrote:
Now I have not touch the $PGDATA/postgresql.conf (As I know very little
about memory tuning) Have run VACCUM & ANALYZE.
You should really, really bump up shared_buffers and given you have 8GB
of ram this query would likely benefit from more wo
On Thu, 21 Apr 2005, Jeff wrote:
>
> On Apr 21, 2005, at 7:49 AM, Shoaib Burq (VPAC) wrote:
>
> > Now I have not touch the $PGDATA/postgresql.conf (As I know very little
> > about memory tuning) Have run VACCUM & ANALYZE.
> >
> You should really, really bump up shared_buffers and given you have 8G
here's explain sorry about the mess: I can attach it as text-file if you
like.
ausclimate=# explain ANALYZE select count(*) from "getfutureausclimate";
QUERY PLAN
> John A Meinel <[EMAIL PROTECTED]> writes:
> > Joel Fradkin wrote:
> >> Postgres was on the second run
> >> Total query runtime: 17109 ms.
> >> Data retrieval runtime: 72188 ms.
> >> 331640 rows retrieved.
>
> > How were you measuring "data retrieval time"?
>
> I suspect he's using pgadmin. We'
Joel Fradkin wrote:
I did think of something similar just loading the data tables with junk
records and I may visit that idea with Josh.
I did just do some comparisons on timing of a plain select * from tbl where
indexed column = x and it was considerably slower then both MSSQL and MYSQL,
so I am s
On Thu, 21 Apr 2005 10:44 pm, Shoaib Burq (VPAC) wrote:
> -> Nested Loop (cost=2.19..1069345.29 rows=16 width=58) (actual
> time=135.390..366902.373 rows=13276368 loops=1)
> -> Nested Loop (cost=2.19..1067304.07 rows=44
> width=68) (actual time=107.627..186390.137 rows=
here are some i/o stats with the unchanged postgresql.conf. Gonna change
it now and have another go.
[EMAIL PROTECTED] MultiCPU_test]$ vmstat 10
procs ---memory-- ---swap-- -io --system--
cpu
r b swpd free buff cache si sobibo incs us s
Why is MYSQL returning 360,000 rows, while Postgres is only returning
330,000? This may not be important at all, though.
I also assume you are selecting from a plain table, not a view.
Yes plain table. Difference in rows is one of the datasets had sears data in
it. It (speed differences found) is
Richard van den Berg <[EMAIL PROTECTED]> writes:
> This is with the default cpu_tuple_cost = 0.01:
> Nested Loop (cost=252.80..233010147.16 rows=1035480320 width=98)
> (actual time=0.369..12672213.137 rows=6171334 loops=1)
>Join Filter: (("outer".starttimetrunc <= "inner".ts) AND
> ("outer".
FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the like.
Maybe one of the ODBC cognoscenti here can chime in more concretely
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joel Fradkin
Sent: Thursday, April 21, 2005 10:36 AM
T
I suspect he's using pgadmin.
Yup I was, but I did try running on the linux box in psql, but it was
running to the screen and took forever because of that.
The real issue is returning to my app using ODBC is very slow (Have not
tested the ODBC for MYSQL, MSSQL is ok (the two proc dell is runnin
> Is this an IO intensive query? If running both in parellel results in
> 2x the run time and you have sufficient cpus it would (to me) indicate
> you don't have enough IO bandwidth to satisfy the query.
any tips on how to verify this?
---(end of broadcast)---
Here is the connect string I am using.
It could be horrid as I cut it from ODBC program.
Session("StringConn") =
"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings
Joel, thanks. A couple of things jump out there for
me, not a problem for a routine ODBC connection, but
perhaps in the "lotsa stuff" context of your current
explorations, it might be relevant?
I am completely shooting from the hip, here, but...if
it were my goose to cook, I'd be investigating
Se
Just tried it with the following changes:
shared_buffers = 10600
work_mem = 102400
enable_seqscan = false
still no improvement
Ok here's the Plan with the enable_seqscan = false:
ausclimate=# explain ANALYZE select count(*) from "getfutureausclimate";
Shoaib Burq (VPAC) wrote:
Just tried it with the following changes:
shared_buffers = 10600
work_mem = 102400
enable_seqscan = false
still no improvement
Ok here's the Plan with the enable_seqscan = false:
ausclimate=# explain ANALYZE select count(*) from "getfutureausclimate";
Actually, you proba
John A Meinel wrote:
> You might try doing:
> ALTER TABLE us ALTER COLUMN starttimetrunc SET STATISTICS 200;
> ALTER TABLE us ALTER COLUMN finishtimetrunc SET STATISTICS 200;
> VACUUM ANALYZE us;
I've been looking into that. While increasing the statistics makes the
planner use the index for simpl
Tom Lane wrote:
> which is almost 80% of the entire runtime. Which is enormous.
> What are those column datatypes exactly?
Table "richard.sessions"
Column |Type | Modifiers
+-+-
Richard van den Berg <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Perhaps you are incurring a datatype conversion cost?
> Not that I can tell.
No, apparently not. Hmm ... timestamp_cmp_internal is just a couple of
isnan() checks and one or two floating-point compares. Should be pretty
dang
All,
Running PostgreSQL 7.4.2, Solaris.
Client is reporting that the size of an index is
greater than the number of rows in the table (1.9
million vs. 1.5 million). Index was automatically
created from a 'bigserial unique' column.
Database contains several tables with exactly the same
columns (
Bill,
> What about if an out-of-the-ordinary number of rows
> were deleted (say 75% of rows in the table, as opposed
> to normal 5%) followed by a 'VACUUM ANALYZE'? Could
> things get out of whack because of that situation?
Yes. You'd want to run REINDEX after and event like that. As you shoul
David,
> What also seems weird to me is that the control table has some unique
> indexes created on it, but the data_upate_events table just has a unique
> constraint. Will postgres use an index in the background to enforce
> this constraint?
If you somehow have a unique constraint without a uni
--- Josh Berkus wrote:
> Bill,
>
> > What about if an out-of-the-ordinary number of
> rows
> > were deleted (say 75% of rows in the table, as
> opposed
> > to normal 5%) followed by a 'VACUUM ANALYZE'?
> Could
> > things get out of whack because of that situation?
>
> Yes. You'd want to run R
Bill,
> Honestly, this seems like an inordinate amount of
> babysitting for a production application. I'm not
> sure if the client will be willing to accept it.
Well, then, tell them not to delete 75% of the rows in a table at once. I
imagine that operation brought processing to a halt, too.
Bill,
> Honestly, this seems like an inordinate amount of
> babysitting for a production application. I'm not
> sure if the client will be willing to accept it.
Well, then, tell them not to delete 75% of the rows in a table at once. I
imagine that operation brought processing to a halt, too.
Is:
REINDEX DATABASE blah
supposed to rebuild all indices in the database, or must you specify
each table individualy? (I'm asking because I just tried it and it
only did system tables)
Alex Turner
netEconomist
On 4/21/05, Josh Berkus wrote:
> Bill,
>
> > What about if an out-of-the-ordinary
Alex,
> REINDEX DATABASE blah
>
> supposed to rebuild all indices in the database, or must you specify
> each table individualy? (I'm asking because I just tried it and it
> only did system tables)
"DATABASE
Recreate all system indexes of a specified database. Indexes on user tables
are not pr
josh@agliodbs.com (Josh Berkus) writes:
> Bill,
>
>> What about if an out-of-the-ordinary number of rows
>> were deleted (say 75% of rows in the table, as opposed
>> to normal 5%) followed by a 'VACUUM ANALYZE'? Could
>> things get out of whack because of that situation?
>
> Yes. You'd want to ru
josh@agliodbs.com (Josh Berkus) writes:
> David,
>
>> What also seems weird to me is that the control table has some unique
>> indexes created on it, but the data_upate_events table just has a unique
>> constraint. Will postgres use an index in the background to enforce
>> this constraint?
>
> If
Is
this a common issue among all RDBMSs or is it
something that is PostgreSQL specific?
Speaking from experience, this sort of thing affects MSSQL as well, although
the maintenance routines are different.
Yes, this is true with MSSQL too, however sql server implements a defrag
index
Michael,
> Every five minutes, DBCC INDEXDEFRAG will report to the user an
> estimated percentage completed. DBCC INDEXDEFRAG can be terminated at
> any point in the process, and *any completed work is retained.*"
Keen. Sounds like something for our TODO list.
--
Josh Berkus
Aglio Database
More info on what is bloating:
It's only in one database (the one that's most used), and after running
oid2name on the bloated files, the result is (mysteriously) empty.
Here's the run on the three enormous files:
$ /usr/local/bin/oid2name -d smt -o 160779
From database "smt":
Filenode Table
Hi folks,
I'm doing a simple lookup in a small table by an unique id, and I'm
wondering, why explains tells me seqscan is used instead the key.
The table looks like:
id bigint primary key,
a varchar,
b varchar,
c varchar
and I'm quering: select * from foo w
--- [EMAIL PROTECTED] wrote:
> I gather you mean, out-of-the-ordinary for most
> apps, but not for this client?
Actually, no. The normal activity is to delete 3-5%
of the rows per day, followed by a VACUUM ANALYZE.
Then over the course of the day (in multiple
transactions) about the same amount
Same thing happens in Oracle
ALTER INDEX rebuild
To force a rebuild. It will mark the free blocks as 'free' below the
PCTFREE value for the tablespace.
Basically If you build an index with entries. and each entry is
1/4 of a block, the database will write 2500 blocks to the disk. If
you
* Jaime Casanova <[EMAIL PROTECTED]> wrote:
> Even if your data never changes it *can* change so the function should
> be at most stable not immutable.
okay, the planner sees that the table could potentionally change.
but - as the dba - I'd like to tell him, this table *never* changes
in practi
On Thursday 21 April 2005 12:05, Enrico Weigelt wrote:
> Hi folks,
>
>
> I'm doing a simple lookup in a small table by an unique id, and I'm
> wondering, why explains tells me seqscan is used instead the key.
>
> The table looks like:
>
> idbigint primary key,
> a varchar,
* Tom Lane <[EMAIL PROTECTED]> wrote:
> Yeah, I was actually thinking about a two-step process: inline the
> function to produce somethig equivalent to a handwritten scalar
> sub-SELECT, and then try to convert sub-SELECTs into joins.
... back to my original question ...
What kind of query sho
On Thu, Apr 21, 2005 at 11:28:43AM -0700, Josh Berkus wrote:
> Michael,
>
> > Every five minutes, DBCC INDEXDEFRAG will report to the user an
> > estimated percentage completed. DBCC INDEXDEFRAG can be terminated at
> > any point in the process, and *any completed work is retained.*"
>
> Keen
Dave,
> See http://archives.postgresql.org/pgsql-general/2005-03/msg01465.php
> for my thoughts on a non-blocking alternative to REINDEX. I got no
> replies to that message. :-(
Well, sometimes you have to be pushy. Say, "Hey, comments please?"
The hackers list is about 75 posts a day, it's e
If id is PK, the query shoudl return 1 row only...
--- Enrico Weigelt <[EMAIL PROTECTED]> wrote:
>
> Hi folks,
>
>
> I'm doing a simple lookup in a small table by an
> unique id, and I'm
> wondering, why explains tells me seqscan is used
> instead the key.
>
> The table looks like:
>
> id
On Thu, Apr 21, 2005 at 11:38:22AM -0700, Richard Plotkin wrote:
> More info on what is bloating:
>
> It's only in one database (the one that's most used), and after running
> oid2name on the bloated files, the result is (mysteriously) empty.
> Here's the run on the three enormous files:
>
> $
That returned the same result. I also tried oid2name -d smt -x -i -S
and, separately -s, and also separately, -d with all other databases,
and none of the databases turned up any listing, in either oid or
filenode, for any of these three bloated files. One thing I've noticed
is that these oid
On Thu, 21 Apr 2005, Enrico Weigelt wrote:
> I'm doing a simple lookup in a small table by an unique id, and I'm
> wondering, why explains tells me seqscan is used instead the key.
>
> The table looks like:
>
> idbigint primary key,
> a varchar,
> b varchar,
> c
Quoting Bill Chandler <[EMAIL PROTECTED]>:
> ... The normal activity is to delete 3-5% of the rows per day,
> followed by a VACUUM ANALYZE.
...
> However, on occasion, deleting 75% of rows is a
> legitimate action for the client to take.
> > In case nobody else has asked: is your max_fsm_page
Mischa,
Thanks. Yes, I understand that not having a large
enough max_fsm_pages is a problem and I think that it
is most likely the case for the client. What I wasn't
sure of was if the index bloat we're seeing is the
result of the "bleeding" you're talking about or
something else.
If I deleted
Bill,
> If I deleted 75% of the rows but had a max_fsm_pages
> setting that still exceeded the pages required (as
> indicated in VACUUM output), would that solve my
> indexing problem or would I still need to REINDEX
> after such a purge?
Depends on the performance you're expecting.The FSM re
Bill Chandler wrote:
Mischa,
Thanks. Yes, I understand that not having a large
enough max_fsm_pages is a problem and I think that it
is most likely the case for the client. What I wasn't
sure of was if the index bloat we're seeing is the
result of the "bleeding" you're talking about or
something
Hi folks,
do foreign keys have any influence on performance (besides slowing
down huge inserts) ? do they bring any performance improvement ?
thx
--
-
Enrico Weigelt== metux IT service
phone: +49 36207 519931
Please see attached the output from explain analyse. This is with the
shared_buffers = 10600
work_mem = 102400
enable_seqscan = true
BTW I guess should mention that I am doing the select count(*) on a View.
Ran the Explain analyse with the nestedloop disabled but it was
On Fri, Apr 22, 2005 at 02:06:15AM +0200, Enrico Weigelt wrote:
> do foreign keys have any influence on performance (besides slowing
> down huge inserts) ? do they bring any performance improvement ?
No. They only cause additional tables to be visited to enforce them.
--
Alvaro Herrera (<[EMAI
On Fri, 22 Apr 2005, Shoaib Burq (VPAC) wrote:
> Please see attached the output from explain analyse. This is with the
>
> shared_buffers = 10600
> work_mem = 102400
> enable_seqscan = true
>
> BTW I guess should mention that I am doing the select count(*) on a View.
>
> Ran the
Bill Chandler <[EMAIL PROTECTED]> writes:
> Client is reporting that the size of an index is
> greater than the number of rows in the table (1.9
> million vs. 1.5 million).
This thread seems to have wandered away without asking the critical
question "what did you mean by that?"
It's not possible
57 matches
Mail list logo
