Hey people, long while since I posted here, but I'm having an index
issue that looks on the surface to be a little strange.
I have a text field that I'm trying to query on in a table with
millions of rows. Stupid I know, but a fairly common stupid thing to
try to do.
For some reason it's a requi
On Fri, 05 Nov 2004 10:07:38 -0500, Rod Taylor <[EMAIL PROTECTED]> wrote:
> > It seems to me that a query saying "SELECT column FROM table WHERE
> > column LIKE 'AA%';" should be just as fast or very close to the first
> > case up above. However, explain tells me that this query is not using
> > t
On Fri, 5 Nov 2004 10:32:43 -0500, Allen Landsidel <[EMAIL PROTECTED]> wrote:
> On Fri, 05 Nov 2004 10:07:38 -0500, Rod Taylor <[EMAIL PROTECTED]> wrote:
>
>
> > > It seems to me that a query saying "SELECT column FROM table WHERE
> > > column LIKE
On Fri, 5 Nov 2004 11:51:59 -0600, Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Fri, Nov 05, 2004 at 09:39:16 -0500,
> Allen Landsidel <[EMAIL PROTECTED]> wrote:
> >
> > For some reason it's a requirement that partial wildcard searches are
> > done
On Fri, 05 Nov 2004 14:57:40 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Allen Landsidel <[EMAIL PROTECTED]> writes:
> > With that many rows, and a normal index on the field, postgres figures
> > the best option for say "I%" is not an index scan, but a seq
On Fri, 5 Nov 2004 18:34:23 -, Matt Clark <[EMAIL PROTECTED]> wrote:
> > With that many rows, and a normal index on the field,
> > postgres figures the best option for say "I%" is not an index
> > scan, but a sequential scan on the table, with a filter --
> > quite obviously this is slow as hec
On Fri, 05 Nov 2004 16:08:56 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Allen Landsidel <[EMAIL PROTECTED]> writes:
> > With seqscan enabled however, "AB%" will use the index, but "A%" will not.
>
> > The estimated cost for the query is much
On Fri, 05 Nov 2004 23:04:23 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Matt Clark <[EMAIL PROTECTED]> writes:
> > Well, 74000/7600 ~= 0.1%, way less than 1/26, so no surprise that an
> > indexscan is better, and also no surprise that the planner can't know
> > that I is such an uncommon init
Ok, you thought maybe this thread died or got abandoned in the face of
all the senseless trolling and spam going on.. you were wrong.. ;)
I thought though I'd start over trying to explain what's going on.
I've gone through some dumps, and recreation of the database with some
different filesystem
On Thu, 11 Nov 2004 10:52:43 -0800, Josh Berkus <[EMAIL PROTECTED]> wrote:
> Allen,
>
> > Ok, you thought maybe this thread died or got abandoned in the face of
> > all the senseless trolling and spam going on.. you were wrong.. ;)
> >
> > I thought though I'd start over trying to explain what's g
On 11 Nov 2004 15:49:46 -0500, Greg Stark <[EMAIL PROTECTED]> wrote:
> Allen Landsidel <[EMAIL PROTECTED]> writes:
>
>
>
> > QUERY PLAN
> > --
On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Allen Landsidel <[EMAIL PROTECTED]> writes:
> > Clustering is really unworkable in this situation.
>
> Nonetheless, please do it in your test scenario, so we can see if it has
> any effect or n
On Fri, 12 Nov 2004 17:35:00 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
>
>
> Allen Landsidel <[EMAIL PROTECTED]> writes:
> > On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> >> Allen Landsidel <[EMAIL PROTECTED]> write
Sorry if I'm contributing more noise to the signal here, just thought
I'd repost this one to the list since it may have gotten lost in all
the garbage from the guy unhappy about the usenet thing..
-- Forwarded message ------
From: Allen Landsidel <[EMAIL PROTECTED]>
D
Asked and answered on the list probably a thousand times, but what else is
there to discuss on the performance list? :)
I recently built a rather powerful machine to be used in a heavily accessed
database.. the machine is a dual AthlonMP 2800+, 2GB of PC2100 ECC, and a
4x18GB RAID-0 using 15k r
At 17:14 10/23/2003, Vivek Khera wrote:
>>>>> "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes:
AL> I recently built a rather powerful machine to be used in a heavily
AL> accessed database.. the machine is a dual AthlonMP 2800+, 2GB of
AL> PC2100 ECC, a
Pardon this for looking somewhat "weird" but it seems I'm not getting all
the messages to the list.. I've noticed the past few days a lot of them are
coming out of order as well..
So, this was copy/pasted from the web archive of the list..
Vivek Khera wrote:
>>&
At 05:56 10/25/2003, John Pagakis wrote:
Snipping most of this, I only have one suggestion/comment to make.
[snip]
CREATE TABLE baz (
baz_key int4 NOT NULL,
baz_number CHAR(15) NOT NULL,
customer_id CHAR(39),
foobar_id INTEGER,
is_cancelled BOOL DEFAULT false NOT NULL,
cr
Yet another question.. thanks to everyone responding to all these so far.. ;)
This one is basically.. given I have a big table already in COPY format,
about 28 million rows, all keys guaranteed to be unique, I'm trying to find
out which of the following will get the import finished the fastest:
At 12:10 10/31/2003, Josh Berkus wrote:
Allen,
> a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then
> CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
> each fk needed.
Did you ANALYZE after the copy?
No, and this was my major mistake. I normally
r, I would definitely go with A.
Adding indexes after the fact seems to be much quicker. Foreign keys use
the same algorithm prior to beta 5 regardless of timing.
A primary key and unique index will have approx the same performance (a
check for NULL isn't very costly).
On Fri, 2003-10-31
At 13:40 10/31/2003, Neil Conway wrote:
On Fri, 2003-10-31 at 13:27, Allen Landsidel wrote:
> I had no idea analyze was playing such a big role in this sense.. I really
> thought that other than saving space, it wasn't doing much for tables that
> don't have indexes on the.
A
22 matches
Mail list logo