On Fri, May 25, 2007 at 18:45:15 -0700,
Craig James <[EMAIL PROTECTED]> wrote:
> We're thinking of building some new servers. We bought some a while back
> that have ECC (error correcting) RAM, which is absurdly expensive compared
> to the same amount of non-ECC RAM. Does anyone have any real
This should have been asked on the performance list, not filed as a bug.
I doubt anyone will have a complete answer to your question without
EXPLAIN ANALYZE output from the query.
Have you ANALYZE'd the tables recently? Poor statistics is one possible
cause of the issue you are having.
On Fri, Ma
On Wed, Feb 28, 2007 at 05:21:41 +1030,
Shane Ambler <[EMAIL PROTECTED]> wrote:
>
> The difference between SCSI and IDE/SATA in this case is a lot if not
> all IDE/SATA drives tell you that the cache is disabled when you ask it
> to but they either don't actually disable it or they don't retai
On Tue, Feb 27, 2007 at 15:35:13 +1030,
Shane Ambler <[EMAIL PROTECTED]> wrote:
>
> From all that I have heard this is another advantage of SCSI disks -
> they honor these settings as you would expect - many IDE/SATA disks
> often say "sure I'll disable the cache" but continue to use it or don
On Sun, Feb 25, 2007 at 23:11:01 +0100,
Peter Kovacs <[EMAIL PROTECTED]> wrote:
> A related question:
> Is it sufficient to disable write cache only on the disk where pg_xlog
> is located? Or should write cache be disabled on both disks?
With recent linux kernels you may also have the option to
On Tue, Feb 06, 2007 at 10:31:26 -0800,
Mark Lewis <[EMAIL PROTECTED]> wrote:
>
> Sure it's possible:
>
> CREATE TABLE parent (col1 int4);
> -- insert many millions of rows into parent
> CREATE TABLE child (col1 int4 REFERENCES parent(col1));
> -- insert many millions of rows into child, very
On Tue, Feb 06, 2007 at 09:39:54 -0500,
Mark Stosberg <[EMAIL PROTECTED]> wrote:
>
> I've been investigating partial indexes for the pets table. It has about
> 300,000 rows, but only about 10 are "active", and those are the ones we
> are care about. Queries are also frequently made on males vs f
On Mon, Feb 05, 2007 at 18:01:05 -0500,
Mark Stosberg <[EMAIL PROTECTED]> wrote:
>
> It's also notable that the units used are meters, not miles like
> geo_distance(). That's what the magic number of "16093.44" is-- 10 miles
> converted to meters.
You can change the earth() function in earthdis
On Mon, Feb 05, 2007 at 14:47:25 -0500,
Mark Stosberg <[EMAIL PROTECTED]> wrote:
>
> This is also interesting. Is this approach practical if I want to index
> what's near each of about 40,000 US zipcodes, or the approach mostly
> useful if you there are just a small number of fixed points to add
On Sat, Feb 03, 2007 at 14:00:26 -0500,
Mark Stosberg <[EMAIL PROTECTED]> wrote:
>
> I'm using geo_distance() from contrib/earthdistance would like to find a
> way to spend up the geo distance calculation if possible. This is for a
> proximity search: "Show me adoptable pets within 250 miles of
On Tue, Jan 30, 2007 at 14:33:34 +0600,
Igor Lobanov <[EMAIL PROTECTED]> wrote:
> Greetings!
>
> I have rather large table with about 5 millions of rows and a dozen of
> columns. Let's suppose that columns are named 'a', 'b', 'c' etc. I need
> to query distinct pairs of ('a';'b') from this tab
On Tue, Jan 23, 2007 at 11:34:52 +0100,
Laurent Manchon <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 80 rows:
>
> select count(*)from tbl;
>
> PostgreSQL return result in 28 sec every time.
> although MS-
On Fri, Jan 19, 2007 at 09:05:35 -0500,
Kevin Hunter <[EMAIL PROTECTED]> wrote:
>
> Seriously though, that would have bitten me. Thank you, I did not know
> that. Does that mean that I can't publish the results outside of my
> work/research/personal unit at all? Or do I just need to obscure
> From: "Steve" <[EMAIL PROTECTED]>
> To: pgsql-performance@postgresql.org
> Sent: 1/17/2007 2:41 PM
> Subject: [PERFORM] Configuration Advice
>
> SO ... our goal here is to make this load process take less time. It
> seems the big part is building the big summary table; this big summary
> tabl
On Fri, Dec 15, 2006 at 10:44:39 -0600,
Bruno Wolff III <[EMAIL PROTECTED]> wrote:
>
> The other feature I would like is to be able to use write barriers with
> encrypted file systems. I haven't found anythign on whether or not there
> are near term plans by any one to
On Fri, Dec 15, 2006 at 10:34:15 -0600,
Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> The reply wasn't (directly copied to the performance list, but I will
> copy this one back.
Sorry about this one, I meant to intersperse my replies and hit the 'y'
key at the wron
On Thu, Dec 14, 2006 at 13:21:11 -0800,
Ron Mayer <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III wrote:
> > On Thu, Dec 14, 2006 at 01:39:00 -0500,
> > Jim Nasby <[EMAIL PROTECTED]> wrote:
> >> On Dec 11, 2006, at 12:54 PM, Bruno Wolff III wrote:
> >
The reply wasn't (directly copied to the performance list, but I will
copy this one back.
On Thu, Dec 14, 2006 at 13:21:11 -0800,
Ron Mayer <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III wrote:
> > On Thu, Dec 14, 2006 at 01:39:00 -0500,
> > Jim Nasby <[EMAIL PROT
On Thu, Dec 14, 2006 at 01:39:00 -0500,
Jim Nasby <[EMAIL PROTECTED]> wrote:
> On Dec 11, 2006, at 12:54 PM, Bruno Wolff III wrote:
> >
> >This appears to be changing under Linux. Recent kernels have write
> >barriers
> >implemented using cache flush commands (
On Wed, Dec 06, 2006 at 08:55:14 -0800,
Mark Lewis <[EMAIL PROTECTED]> wrote:
> > Anyone run their RAIDs with disk caches enabled, or is this akin to
> > having fsync off?
>
> Disk write caches are basically always akin to having fsync off. The
> only time a write-cache is (more or less) safe t
On Wed, Dec 06, 2006 at 18:45:56 +0100,
Markus Schiltknecht <[EMAIL PROTECTED]> wrote:
>
> Cool, thank you for the example :-) I thought the MTA or at least the the
> mailing list would wrap mails at some limit. I've now set word-wrap to
> characters (it seems not possible to turn it off
This really should have been asked on pgsql-performance and would probably
get a better response there..
On Sun, Nov 26, 2006 at 16:35:52 +,
Michael Simms <[EMAIL PROTECTED]> wrote:
> PostgreSQL version: 8.1.4
> Operating system: Linux kernel 2.6.12
> Description:Performance seriou
On Mon, Oct 09, 2006 at 23:33:03 +0200,
Tobias Brox <[EMAIL PROTECTED]> wrote:
>
> Just a comment from the side line; can't the rough "set
> enable_seqscan=off" be considered as sort of a hint anyway? There have
> been situations where we've actually had to resort to such crud.
That only works
On Wed, Oct 04, 2006 at 08:30:03 -0700,
"Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
>
> Although I 100% agree with you Bruno, it should be noted that our lists
> are a closed box for most people. They don't follow what is largely
> considered standard amongst lists which is to have list informa
On Wed, Oct 04, 2006 at 10:03:00 +0200,
Luc Delgado <[EMAIL PROTECTED]> wrote:
>
> Please unsubscribe me! Thank you!
If you really can't figure out how to unsubscribe from a list, you should
contact the list owner, not the list. The list members can't unsubscribe you
(and it isn't their job t
On Tue, Oct 03, 2006 at 12:13:43 -0700,
Graham Davis <[EMAIL PROTECTED]> wrote:
> Also, the multikey index of (assetid, ts) would already be sorted and
> that is why using such an index in this case is
> faster than doing a sequential scan that does the sorting afterwards.
That isn't necessaril
On Fri, Jul 21, 2006 at 07:41:02 +,
Alaa El Gohary <[EMAIL PROTECTED]> wrote:
>
> The following bug has been logged online:
The report below isn't a bug, its a performance question and should have
been sent to [EMAIL PROTECTED] I am redirecting replies there.
> A query on the postgresql DB
On Wed, Jul 12, 2006 at 15:41:14 -0500,
Medora Schauer <[EMAIL PROTECTED]> wrote:
> I have just upgraded from 7.3.4 to 8.1.4 and now *all* db access calls
> are extremely slow. I didn't need to preserve any old data so at this
> point all my tables are empty. Just connecting to a db takes sever
On Wed, Jul 12, 2006 at 10:16:40 -0600,
"Koth, Christian (DWBI)" <[EMAIL PROTECTED]> wrote:
>
> I have noticed a strange performance behaviour using a commit statement on
> two different machines. On one of the machines the commit is many times
> faster than on the other machine which has fast
On Mon, Jul 10, 2006 at 17:55:38 +1000,
Neil Hepworth <[EMAIL PROTECTED]> wrote:
>
> running on our server (obviously we need to update certain queries,
> e.g. delete .. using.. and test with 8.1 first) - I will be pushing
> for an upgrade as soon as possible. And the fsync=false is a
You can
On Thu, Jun 15, 2006 at 15:38:32 -0400,
John Vincent <[EMAIL PROTECTED]> wrote:
> Any suggestions? FYI the original question wasn't meant as a poke at
> >comparing PG to MySQL to DB2. I'm not making an yvalue judgements either
> >way. I'm just trying to understand how we can use it the best way p
ex is on (cusip, date), then the records will be
ordered by cusip, date immediately after the cluster. (New records added
after the cluster are not guarenteed to be ordered by the index.)
>
> Benjamin
>
> -Original Message-
> From: Bruno Wolff III [mailto:[EMAIL PROTECTED]
On Tue, Jun 13, 2006 at 09:04:15 -0700,
Benjamin Arai <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a database where there are three columns (name,date,data). The
> queries are almost always something like SELECT date,data FROM table WHERE
> name=blah AND date > 1/1/2005 AND date < 1/1/2006;. I
On Wed, May 31, 2006 at 01:23:07 -0500,
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> On Sun, May 28, 2006 at 07:20:59PM -0400, Greg Stark wrote:
> > Brendan Duddridge <[EMAIL PROTECTED]> writes:
> > More likely you were blocking on some lock. Until that other query holding
> > that lock tries to c
On Thu, May 25, 2006 at 16:31:40 -0400,
Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On 5/25/06, Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> >On Thu, May 25, 2006 at 16:07:19 -0400,
> > Merlin Moncure <[EMAIL PROTECTED]> wrote:
> >> been doing a lot
On Thu, May 25, 2006 at 16:07:19 -0400,
Merlin Moncure <[EMAIL PROTECTED]> wrote:
> been doing a lot of pgsql/mysql performance testing lately, and there
> is one query that mysql does much better than pgsql...and I see it a
> lot in normal development:
>
> select a,b,max(c) from t group by a,b;
Please don't reply to previous messages to start new threads. This makes it
harder to find stuff in the archives and may keep people from noticing your
message.
On Wed, May 17, 2006 at 08:54:52 -0700,
"Craig A. James" <[EMAIL PROTECTED]> wrote:
> Here's a "corner case" that might interest someon
On Thu, May 11, 2006 at 18:41:25 -0500,
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> On Thu, May 11, 2006 at 07:20:27PM -0400, Bruce Momjian wrote:
>
> My damn powerbook drive recently failed with very little warning, other
> than I did notice that disk activity seemed to be getting a bit slower.
On Wed, May 10, 2006 at 01:15:11 -0400,
Tom Lane <[EMAIL PROTECTED]> wrote:
>
> Maybe the real problem is at the other end of the process, ie we should
> require some evidence of a greater-than-room-temp IQ to subscribe in the
> first place?
I suspect it is more lazyiness that smarts. That had
On Tue, May 09, 2006 at 12:10:32 +0200,
"Jean-Yves F. Barbier" <[EMAIL PROTECTED]> wrote:
> Naa, you can find ATA &| SATA ctrlrs for about EUR30 !
But those are the ones that you would generally be better off not using.
> Definitely NOT, however if your server doen't have a heavy load, the
> so
On Tue, May 09, 2006 at 09:24:15 +0530,
soni de <[EMAIL PROTECTED]> wrote:
>
> EXPLAIN
> pdb=# EXPLAIN ANALYZE select * from wan where kname = 'pluto' order by stime
> limit 50;
> NOTICE: QUERY PLAN:
>
> Limit (cost=3515.32..3515.32 rows=50 width=95) (actual time=
> 230492.69..230493.07 rows=
On Tue, May 02, 2006 at 12:06:30 -0700,
Tony Wasson <[EMAIL PROTECTED]> wrote:
>
> Ah thanks, it's a bug in my understanding of the thresholds.
>
> "With the standard freezing policy, the age column will start at one
> billion for a freshly-vacuumed database."
>
> So essentially, 1B is normal,
On Fri, Apr 28, 2006 at 17:37:30 +,
Bealach-na Bo <[EMAIL PROTECTED]> wrote:
> >The above shows that the indexes contained 10M rows and 160M of dead
> >space each. That means you weren't vacuuming nearly enough.
>
> How is it that a row in the table can grow to a size far exceeding the sum
On Thu, Apr 27, 2006 at 09:06:48 -0400,
Ketema Harris <[EMAIL PROTECTED]> wrote:
> Yes, your right, I meant not have to do the backups from the db server
> itself. I can do that within the storage device now, by allocating space
> for it, and letting the device copy the data files on some period
On Thu, Apr 27, 2006 at 08:57:51 -0400,
Ketema Harris <[EMAIL PROTECTED]> wrote:
> performance from the db. I also would hopefully then not have to do
> periodic backups from the db server to some other type of storage. Is this
> not a good idea? How bad of a performance hit are we talking abo
On Fri, Apr 21, 2006 at 10:12:24 +0530,
soni de <[EMAIL PROTECTED]> wrote:
> I don't want to query exactly 81900 rows into set. I just want to fetch 50
> or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 rows
> starting from last to end).
You can do this efficiently, if stime h
On Thu, Apr 20, 2006 at 11:07:31 +0530,
soni de <[EMAIL PROTECTED]> wrote:
> Please provide me some help regarding how could I use cursor in following
> cases? :
>
> I want to fetch 50 records at a time starting from largest stime.
>
> SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900;
So
On Sat, Mar 18, 2006 at 11:50:48 +0300,
Oleg Bartunov wrote:
> I may be wrong but we in astronomy have several sky indexing schemes, which
> allows to effectively use classical btree index. See
> http://www.sai.msu.su/~megera/oddmuse/index.cgi/SkyPixelization
> for details. Sergei Koposov has d
On Fri, Mar 17, 2006 at 08:34:26 -0700,
Dan Harris <[EMAIL PROTECTED]> wrote:
> Markus Bertheau wrote:
> >Have you tried using a GIST index on lat & long? These things are
> >meant for two-dimensional data, whereas btree doesn't handle
> >two-dimensional data that well. How many rows satisfy eith
On Mon, Mar 13, 2006 at 09:19:32 -0800,
"Craig A. James" <[EMAIL PROTECTED]> wrote:
> Alvaro Herrera wrote:
> >>If I only insert data into a table, never update or delete, then I should
> >>never have to vacuum it. Is that correct?
> >
> >You still need to vacuum eventually, to avoid transactio
On Sun, Mar 12, 2006 at 11:46:25 -,
Phadnis <[EMAIL PROTECTED]> wrote:
>
> 1 ) when i try to query for count or for any thg it takes a long time to
> return the result. How to avoid this
Postgres doesn't cache counts, so if you are counting a lot of records, this
may take a while to run.
On Mon, Feb 27, 2006 at 06:48:02 -0800,
Nik <[EMAIL PROTECTED]> wrote:
> I have a table that has only a few records in it at the time, and they
> get deleted every few seconds and new records are inserted. Table never
> has more than 5-10 records in it.
>
> However, I noticed a deteriorating per
On Tue, Feb 28, 2006 at 09:14:59 +0530,
"Jeevanandam, Kathirvel (IE10)" <[EMAIL PROTECTED]> wrote:
> Hi all,
Please don't hijack existing threads to start new ones. This can cause
people to miss your question and messes up the archives.
Performance questions should generally be posted to the pe
On Thu, Feb 02, 2006 at 09:12:59 +1300,
Ralph Mason <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have 2 tables both have an index on ID (both ID columns are an oid).
>
> I want to find only only rows in one and not the other.
>
> Select ID from TableA where ID not IN ( Select ID from Table B)
>
> T
On Sat, Dec 24, 2005 at 22:13:43 -0500,
Luke Lonergan <[EMAIL PROTECTED]> wrote:
> David,
>
> > now hot-swap may not be supported on all interface types, that may be what
> > you have run into, but with SCSI or SATA you should be able to hot-swap
> > with the right controller.
>
> That's actu
On Thu, Dec 15, 2005 at 21:41:06 -0800,
"Craig A. James" <[EMAIL PROTECTED]> wrote:
>
> If I understand enable_seqscan, it's an all-or-nothing affair. Turning it
> off turns it off for the whole database, right? The same is true of all of
You can turn it off just for specific queries. Howev
Have you given us explain analyse samples yet?
>
> Thanks,
> Assaf.
>
> > -Original Message-
> > From: Jan Wieck [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, December 06, 2005 2:35 PM
> > To: Assaf Yaari
> > Cc: Bruno Wolff III; pgsql-performance@
On Tue, Dec 06, 2005 at 10:52:57 +0100,
Csaba Nagy <[EMAIL PROTECTED]> wrote:
> Joost,
>
> Why do you use an offset here ? I guess you're traversing the table
> somehow, in this case it would be better to remember the last zipcode +
> housenumber and put an additional condition to get the next b
On Tue, Dec 06, 2005 at 11:08:07 +0200,
Assaf Yaari <[EMAIL PROTECTED]> wrote:
> Thanks Bruno,
>
> Issuing VACUUM FULL seems not to have influence on the time.
That was just to get the table size back down to something reasonable.
> I've added to my script VACUUM ANALYZE every 100 UPDATE's and
On Mon, Dec 05, 2005 at 19:05:01 +0200,
Assaf Yaari <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm using PostgreSQL 8.0.3 on Linux RedHat WS 3.0.
>
> My application updates counters in DB. I left a test over the night that
> increased counter of specific record. After night running (several
> hundr
On Sat, Oct 29, 2005 at 08:24:32 -0600,
Michael Fuhr <[EMAIL PROTECTED]> wrote:
> > Does an UPDATE of e.g. m_fld1 in t_master cause a 'lookup' in all tables
> > which have a cascading update-rule or is this 'lookup' only triggered if
> > the referenced column in t_master is explicitly updated?
>
On Sat, Oct 29, 2005 at 13:10:31 +0200,
Martin Lesser <[EMAIL PROTECTED]> wrote:
> Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the
> referenced table are updated which are not part of the FOREIGN KEY
> constraint?
In 8.1 there is a check to see if the foreign key value h
On Tue, Oct 25, 2005 at 22:24:06 -0600,
Sidar López Cruz <[EMAIL PROTECTED]> wrote:
> where can i find bests practices for tunning postgresql?
You should first read the documentation. For 8.1, that would be here:
http://developer.postgresql.org/docs/postgres/runtime-config.html
There is also go
On Sun, Oct 23, 2005 at 00:14:23 -0400,
[EMAIL PROTECTED] wrote:
> Hey all.
>
> Please point me to a place I should be looking if this is a common
> question that has been debated periodically and at great length
> already. :-)
You probably want to read:
http://candle.pha.pa.us/main/writings/pg
On Wed, Sep 28, 2005 at 05:33:27 +0200,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> By occation, we dropped the whole production database and refreshed it from
> a database backup - and all our performance problems seems to have gone. I
> suppose this means that to keep the database efficient, one
On Fri, Sep 23, 2005 at 18:16:44 +0200,
Stef <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III mentioned :
> => If you have a proper FSM setting you shouldn't need to do vacuum fulls
> => (unless you have an older version of postgres where index bloat might
> => be
On Tue, Sep 20, 2005 at 14:53:19 -0400,
Markus Benne <[EMAIL PROTECTED]> wrote:
> I have a table that is purged by 25% each night. I'd like to do a
> vacuum nightly after the purge to reclaim the space, but I think I'll
> still need to do a vacuum full weekly.
>
> Would there be any benefit to
On Mon, Sep 12, 2005 at 10:14:25 +0100,
Hilary Forbes <[EMAIL PROTECTED]> wrote:
> Hello everyone
>
> I must be doing something very wrong here so help please! I have two tables
>
> tableA has 300,000 recs
> tableB has 20,000 recs
>
> I need to set the value of a field in table A to a value i
Please keep replies copied to the list so that others may contribute to
and learn from the discussion.
On Tue, Aug 30, 2005 at 10:15:13 -0300,
Alvaro Nunes Melo <[EMAIL PROTECTED]> wrote:
> Hello Bruno,
>
> Bruno Wolff III wrote:
>
> >On Tue, Aug 30, 2005 at 09:37:17
On Tue, Aug 30, 2005 at 09:37:17 -0300,
Alvaro Nunes Melo <[EMAIL PROTECTED]> wrote:
>
> The server is a Dual Xeon 3.0 with 2 GB RAM and two SCSI disks. Our main
> doubt is what is the best configuration for the disks. We are thinking
> about use them in a RAID-0 array. Is this the best option
On Tue, Aug 23, 2005 at 13:41:32 +1000,
"Lenard, Rohan (Rohan)" <[EMAIL PROTECTED]> wrote:
> I've read that indexes aren't used for COUNT(*) and I've noticed (7.3.x)
> with EXPLAIN that indexes never seem to be used on empty tables - is
> there any reason to have indexes on empty tables, or will
On Fri, Aug 26, 2005 at 16:14:18 -0400,
Chris Hoover <[EMAIL PROTECTED]> wrote:
> Hopefully a quick question.
>
> In 7.3.4, how does the planner execute a query with union alls in it?
>
> Does it execute the unions serially, or does it launch a "thread" for
> each union (or maybe something else
On Tue, Aug 23, 2005 at 10:10:45 -0700,
gokulnathbabu manoharan <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I like to know the caching policies of Postgresql.
> What parameter in the postgresql.conf affects the
> cache size used by the Postgresql? As far as I have
> searched my knowledge of the p
On Fri, Jul 29, 2005 at 13:52:45 +0100,
Magno Leite <[EMAIL PROTECTED]> wrote:
>
> Description:Problem using Limit in a function, seqscan
>
> I looked for about this problem in BUG REPORT but I can't find. This is my
> problem, when I try to use limit in a function, the Postgre doesn't
On Thu, Jul 14, 2005 at 16:29:58 -0600,
Dan Harris <[EMAIL PROTECTED]> wrote:
>
> Ok, I tried this one. My ssh keeps getting cut off by a router
> somewhere between me and the server due to inactivity timeouts, so
> all I know is that both the select and explain analyze are taking
> over
On Wed, Jul 06, 2005 at 18:54:02 -0300,
Dario Pudlo <[EMAIL PROTECTED]> wrote:
> (first at all, sorry for my english)
> Hi.
>- Does "left join" restrict the order in which the planner must join
> tables? I've read about join, but i'm not sure about left join...
The left join operator is not
On Mon, Jul 04, 2005 at 20:29:50 -0400,
David Gagnon <[EMAIL PROTECTED]> wrote:
> Thanks .. I miss that FK don't create indexed ... since Primary key
> implicitly does ...
>
> I'm a bit surprised of that behavior thought, since it means that if we
> delete a row from table A all tables (B,C,D
On Tue, Jun 28, 2005 at 12:02:09 -0400,
Merlin Moncure <[EMAIL PROTECTED]> wrote:
>
> Confirmed. Hats off to you, the above some really wicked querying.
> IIRC I posted the same question several months ago with no response and
> had given up on it. I think your solution (smallest X1 not in X)
On Tue, Jun 28, 2005 at 10:21:16 -0400,
Merlin Moncure <[EMAIL PROTECTED]> wrote:
> I need a fast way (sql only preferred) to solve the following problem:
>
> I need the smallest integer that is greater than zero that is not in the
> column of a table. In other words, if an 'id' column has valu
On Thu, Jun 23, 2005 at 10:33:18 +0200,
Kurt De Grave <[EMAIL PROTECTED]> wrote:
>
> Now it's tempting to dream of some mechanism that could make the
> database consider
> replanning the query automatically once it knows the parameter, or
> choose from
> a set of plans depending on the parameter
On Thu, Jun 16, 2005 at 07:46:45 -0700,
Todd Landfried <[EMAIL PROTECTED]> wrote:
> Yes, it is 7.2. Why? because an older version of our software runs on
> RH7.3 and that was the latest supported release of Postgresql for
> RH7.3 (that we can find). We're currently ported to 8, but we still
On Wed, Jun 15, 2005 at 02:06:27 -0700,
Todd Landfried <[EMAIL PROTECTED]> wrote:
>
> What's the problem? The sucker gets s-l-o-w on relatively simple
> queries. For example, simply listing all of the users online at one
> time takes 30-45 seconds if we're talking about 800 users. We've
>
On Mon, Jun 13, 2005 at 15:05:00 -0400,
Madison Kelly <[EMAIL PROTECTED]> wrote:
> Wow!
>
> With the sequence scan off my query took less than 2sec. When I turned
> it back on the time jumped back up to just under 14sec.
>
>
> tle-bu=> set enable_seqscan = off; SET
> tle-bu=> EXPLAIN ANALYZE
On Mon, Jun 13, 2005 at 11:46:46 -0500,
Kevin Grittner <[EMAIL PROTECTED]> wrote:
> I agree that ignoring useless columns in an ORDER BY clause is less
> important than ignoring index columns where the value is fixed. There
> is one use case for ignoring useless ORDER BY columns that leaps to
>
On Mon, Jun 13, 2005 at 12:22:14 -0400,
Tom Lane <[EMAIL PROTECTED]> wrote:
>
> I don't think the use-case has been shown that justifies doing this much
> work to ignore useless ORDER BY clauses. The examples that have come up
> in the past all suggest ignoring index columns not the other way '
On Mon, Jun 13, 2005 at 09:51:57 -0500,
John A Meinel <[EMAIL PROTECTED]> wrote:
>
> I don't know if there are specific reasons why not, other than just not
> being implemented yet. It might be tricky to get it correct (for
> instance, how do you know which columns can be added, which ones will
On Mon, Jun 13, 2005 at 00:29:08 -0400,
Madison Kelly <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III wrote:
> >On Sun, Jun 12, 2005 at 23:42:05 -0400,
> > Madison Kelly <[EMAIL PROTECTED]> wrote:
> >
> >>As you probably saw in my last reply, I went back
On Sun, Jun 12, 2005 at 23:42:05 -0400,
Madison Kelly <[EMAIL PROTECTED]> wrote:
>
> As you probably saw in my last reply, I went back to the old index and
> tried the query you and Tom Lane recommended. Should this not have
> caught the index?
Probably, but there might be some other reason t
On Sun, Jun 12, 2005 at 22:00:01 -0500,
Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Sun, Jun 12, 2005 at 18:52:05 -0400,
> Madison Kelly <[EMAIL PROTECTED]> wrote:
> >
> > After sending that email I kept plucking away and in the course of
> > do
On Sun, Jun 12, 2005 at 18:52:05 -0400,
Madison Kelly <[EMAIL PROTECTED]> wrote:
>
> After sending that email I kept plucking away and in the course of
> doing so decided that I didn't need to return the 'file_type' column.
> Other than that, it would see my query now matches what you two ha
On Sun, Jun 12, 2005 at 10:12:27 -0400,
Madison Kelly <[EMAIL PROTECTED]> wrote:
> Indexes:
> "file_info_7_display_idx" btree (file_type, file_parent_dir, file_name)
> Here is my full query:
>
> tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type
> FROM file_info_7 WHER
actual query you used. There were
only 569 entries in the people table, so I find it hard to believe that an
index look up per person is taking so long that you need to cancel the query.
>
>
>
>
> On 6/8/05, Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> > On Wed, J
On Wed, Jun 08, 2005 at 15:48:27 -0700,
Junaili Lie <[EMAIL PROTECTED]> wrote:
> Hi,
> The suggested query below took forever when I tried it.
> In addition, as suggested by Tobias, I also tried to create index on
> food(p_id, id), but still no goal (same query plan).
> Here is the explain:
> TES
On Wed, Jun 08, 2005 at 11:37:40 +0200,
Kim Bisgaard <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm having problems with the query optimizer and FULL OUTER JOIN on
> PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins.
> I might be naive, but I think that it should be possible?
>
On Fri, Jun 03, 2005 at 00:09:00 -0700,
Bahadur Singh <[EMAIL PROTECTED]> wrote:
>
> Many thanks for this tip !
> But is this good idea to analyse/vacuuming the
> database tables while updates are taking place..
> Since, I update continuously say (100,000 ) times or
> more the same data set.
>
On Tue, May 31, 2005 at 11:31:58 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> [Tobias Brox]
> > test=# set enable_seqscan=off;
>
> [Bruno Wolff III - Mon at 10:16:53PM -0500]
> > It isn't surprising that an index wasn't used since a sequential scan i
On Tue, May 31, 2005 at 11:21:20 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> [Tobias Brox - Tue at 11:02:07AM +0800]
> > test=# explain select * from mock where b is NULL;
> > QUERY PLAN
> >
On Tue, May 31, 2005 at 11:02:07 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> I read in the manual today:
>
> Indexes are not used for IS NULL clauses by default. The best way to use
> indexes in such cases is to create a partial index using an IS NULL
> predicate.
>
> This is from t
On Tue, May 24, 2005 at 21:39:15 -0500,
John A Meinel <[EMAIL PROTECTED]> wrote:
>
> By the way, I think doing:
>
> CREATE DATABASE tempdb WITH TEMPLATE = originaldb;
>
> Is a much faster way of doing dump and load. I *think* it would recreate
> indexes, etc. If it just does a copy it may not
On Tue, May 24, 2005 at 08:36:36 -0700,
mark durrant <[EMAIL PROTECTED]> wrote:
>
> --MSSQL's ability to hit the index only and not having
> to go to the table itself results in a _big_
> performance/efficiency gain. If someone who's in
> development wants to pass this along, it would be a
> nic
1 - 100 of 182 matches
Mail list logo
