2010/5/26 Maria L. Wilson
> Was hoping that someone would like to discuss the need for indexes on
> columns that are referenced in a foreign key. Are they necessary? Are they
> worth the overhead? PostgreSQL version 8.4
>
>
That depends. They are not necessary. They are overhead (like all oth
"Jeff Larsen" <[EMAIL PROTECTED]> writes:
> I've noticed that PG automatically creates indexes when you create a
> primary key. But when you create a foreign key on a child table, it
> does not create an index on the referencing columns of the child
> table.
This is intentional since depending on
--- On Mon, 10/29/07, Jeff Larsen <[EMAIL PROTECTED]> wrote:
> Does PG *not* need an index to perform joins between parent
> and child tables quickly?
PG doesn't need the a FK index to quickly insure that the constraint is
maintained.
> Or is it simply left up to the administrator to decide
> if
Leonia Zaj?c wrote:
> Li?cieHi !
>
> Can anybody say, HOW does the postgre sql query planer, or executer,
> prepers the query plan, if i have several indexes on my table ?
You can look at EXPLAIN to see the index chosen. I have an "Internals
through Pictures" PDF on my home page below that outli
ght not, be
> practical, feasible, maintainable.
>
> Thanks for the replies. I'll look more at the memory usage and see what
> comes up.
>
> -- sgl
>
>
> > From: Chris Travers <[EMAIL PROTECTED]>
> > Date: Fri, 29 Jul 2005 09:50:07 -0700
> > To:
-- sgl
> From: Chris Travers <[EMAIL PROTECTED]>
> Date: Fri, 29 Jul 2005 09:50:07 -0700
> To: Steve Lane <[EMAIL PROTECTED]>
> Cc:
> Subject: Re: [ADMIN] Indexes on RAM disk = insanity?
>
> Steve Lane wrote:
>
>> All:
>>
>> We have a po
Steve Lane wrote:
All:
We have a postgres 7.4 server where we're trying to achieve some speedups.
Right now, at least superficially, RAM appears to be the bottleneck -- lots
of swaps in and out.
There is another consultant beside myself in the mix and he asked this
question: can we put the dat
Steve Lane <[EMAIL PROTECTED]> writes:
> Or will postgres 8 rebuild an index if it doesn't find it where it expects?
No, but there's always REINDEX.
> I recognize, or think I do, that PG 8 tablespaces would be required to
> accomplish this.
It'd certainly make it a lot easier. Also, it's probab
> RAM appears to be the bottleneck -- lots
> of swaps in and out.
> can we put the database indexes on a RAM disk?
Can you increase the RAM in the machine? I guess I should assume you're
maxed out.
Rick
[EMAIL PROTECTED] wrote on 07/29/2005 10:32:28 AM:
> All:
>
> We have a postgres 7.4 server
On Thu, 2005-06-16 at 08:25 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > I'm suspicious of a more subtle intermittent error.
>
> Yeah, I am too, but so far none of the reporters have been cooperative
> about providing more information :-(
>
> > We have no
> > information a
Tom Lane wrote:
I'm suspicious of a more subtle intermittent error.
Yeah, I am too, but so far none of the reporters have been cooperative
about providing more information :-(
Well, at least from me, I can tell you I gave all information I came up
with. One thing that I pointed out is this
Simon Riggs <[EMAIL PROTECTED]> writes:
> I'm suspicious of a more subtle intermittent error.
Yeah, I am too, but so far none of the reporters have been cooperative
about providing more information :-(
> We have no
> information about what the magic values are, only that they are not
> correct. S
On Fri, 2005-06-10 at 12:57 -0400, Tom Lane wrote:
> "Bruno G. Albuquerque" <[EMAIL PROTECTED]> writes:
> > Any pointers? Is there anything I can do for Windows to actually wait
> > for the postgresql processes to clean up?
>
> What it looks like to me is that your disk drive is lying about what
"Bruno G. Albuquerque" <[EMAIL PROTECTED]> writes:
> Any pointers? Is there anything I can do for Windows to actually wait
> for the postgresql processes to clean up?
What it looks like to me is that your disk drive is lying about what
it's actually written; or possibly Windows is lying to Postgr
Scott Marlowe wrote:
I am having a weird problem here. I have the automated process to
install PostgreSQL (8.0.1) on Windows 2000 machines. Besides installing
the database server itself, my process does the following:
1 - Runs initdb to created the database I will be using.
2 - Runs a SQL
On Wed, 2005-06-08 at 13:39, Bruno G. Albuquerque wrote:
> I am having a weird problem here. I have the automated process to
> install PostgreSQL (8.0.1) on Windows 2000 machines. Besides installing
> the database server itself, my process does the following:
>
> 1 - Runs initdb to created the d
Reindexdb in contrib should help with rebuilding indexes.
Bruno G. Albuquerque wrote:
I am having a weird problem here. I have the automated process to
install PostgreSQL (8.0.1) on Windows 2000 machines. Besides installing
the database server itself, my process does the following:
1 - Runs
Anshaj <[EMAIL PROTECTED]> writes:
> when I try to do a query like
> explain analyze select * from foo where snumber > 1000;
> It do a sequence scan on table.
One-sided inequalities are frequently not selective enough to justify an
indexscan. A rule of thumb is that if the WHERE selects more than
Please do not post new topic as reply's to unrelated threads!!
On Friday 22 October 2004 02:10, Anshaj wrote:
> Dear group,
>
> I have a table foo
> anshajdb=# \d foo
>Table "public.foo"
> Column | Type| Modifiers
> -+---+---
>
ginal Message -
From: "Joe Conway" <[EMAIL PROTECTED]>
To: "Jodi Kanter" <[EMAIL PROTECTED]>
Cc: "Postgres Admin List" <[EMAIL PROTECTED]>
Sent: Friday, March 21, 2003 10:44 AM
Subject: Re: [ADMIN] indexes not being used!
> Jodi Kanter wrote:
&
- Original Message -
From: "Curt Sampson" <[EMAIL PROTECTED]>
To: "Donny Drummonds" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, February 07, 2003 5:13 AM
Subject: Re: [ADMIN] Indexes on Large Tables
> On Mon, 3 Feb 2003, Donny Dru
On Mon, 3 Feb 2003, Donny Drummonds wrote:
> If I do not index the column from the where clause the query returns
> the 150,000 rows in 4 and a half minutes. If in do index the column
> from the where clause using a btree the 150,000 rows return in 11 and
> a half minutes.
>
> Any insight would be
On Mon, 3 Feb 2003, Donny Drummonds wrote:
> I am running a dual athlon 1800 with an gig of ram. I am running postgres
> 7.2. I have a table that hs 15 million rows. I have a query that has one
> columns and the select cluase and one column in the where clause with no
> joining or sub-queries.
On 6/13/02 10:22 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> BTW, I'd certainly recommend getting the WAL files (pg_xlog directory)
> moved to their own drive long before you worry about separating indexes
> from data. That's a lot simpler (you only need a symlink for the
> directory).
tom:
Co
I have a perl script that I have written which handles moving indexes from
one filesystem to another. It currently only moves indexes, but could be
easily extended to include tables. The syntax of the command looks like:
./moveindex.pl -s /db1/185204209 -d /db2/185204209 -i foo_pkey bar
It lo
You sure can, Charlie.
We have our indexes, lightly used/smaller tables and heavily used/larger
tables split across several disks.
In a nutshell,
1. You just have to create the objects.
2. Then identify the objects using oid2name.
3. Shut down your database and move the objects.
4. Then put symlin
=?iso-8859-1?Q?Ragnar_Kj=F8rstad?= <[EMAIL PROTECTED]> writes:
> On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote:
>> I've looked around a lot and don't think this is possible with Postgres, but
>> figured I would ask in case I missed something. Is it possible to configure
>> things
On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote:
> I've looked around a lot and don't think this is possible with Postgres, but
> figured I would ask in case I missed something. Is it possible to configure
> things so that an index resides on a separate disk ? It doesn't look like
- Original Message -
From: "Jean-Luc Lachance"
> Interesting... What happens when you drop and recreate the index???
>
repeat the whole procedure again
hth,
Marin
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do no
Interesting... What happens when you drop and recreate the index???
Marin Dimitrov wrote:
>
> - Original Message -
> From: "Judy Jecelin"
>
> >
> > Hi,
> >
> > A relatively novice user of PostgreSQL but not
> > of other databases... I'm trying to understand the
> > use of initlocation.
- Original Message -
From: "Judy Jecelin"
>
> Hi,
>
> A relatively novice user of PostgreSQL but not
> of other databases... I'm trying to understand the
> use of initlocationI understand having separate
> data storage areas (perhaps for each database
> in your postmaster), but is th
Heni Lolov <[EMAIL PROTECTED]> writes:
> explain select *
> from tyc_glup
> where de>0 and de<100 and ra<10 and ra>0;
> NOTICE: QUERY PLAN:
> Merge Join (cost=42975.66..44050.21 rows=34159 width=50)
> -> Sort (cost=108.53..108.53 rows=26 width=14)
> -> Index Scan using t
You don't need to post questions four times ;-)
Heni Lolov <[EMAIL PROTECTED]> writes:
> I have folowing table
> create table usno(
> reg_id int2,
> id int2,
> ra int4,
> de int4,
> mag_r int2,
> mag_b int2
> );
> I make indexes in this order:
>
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> R D <[EMAIL PROTECTED]> writes:
> > i'm storing col1 to col3 as integers to save
> storage
> > space(infact they are fixed point).I have many
> tables
> > ctrated using this template only the view devidor
> > constants are different for each table. Here t
R D <[EMAIL PROTECTED]> writes:
> i'm storing col1 to col3 as integers to save storage
> space(infact they are fixed point).I have many tables
> ctrated using this template only the view devidor
> constants are different for each table. Here they are
> 1000.0 1000.0 and 100.0 but for every table t
35 matches
Mail list logo
