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
patrick ~ <[EMAIL PROTECTED]> writes:
> PREPARE pkk_00 ( integer )
Hi Tom, -performance@,
I apologize if I didn't follow through with the PREPARE and
EXECUTE. I assume that is what you are refering to. After
reading the PostgreSQL docs on PREPARE statement I realized
two things: a) PREPARE is only session long and b) that I
can not (at least I haven't figured o
patrick ~ <[EMAIL PROTECTED]> writes:
> 1. Is this really the only solution left for me?
You still haven't followed the suggestions that were given to you
(ie, find out what is happening with the plan for the query inside
the problematic function).
regards, tom lane
-
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 initial char.
But it *can* know that, at least given adequate ANALYZE statistics.
Merlin,
> Good, I'll give it a shot and see what I come up with...thx.
>
Do share your experience with us.
--
With Best Regards,
Vishal Kashyap.
Did you know SaiPACS is one and only PACS
Management tool.
http://saihertz.com
---(end of broadcast)--
Looking around at the pg_ tables and some PostgreSQL online
docs prompted by another post/reply on this list regarding
ALERT TABLE SET STATISTICS i found out that prior to a VACUUM
the following select (taken from the online docs) shows:
pkk=# select relname, relkind, reltuples, relpages from pg_c
So there are really only 9000 rows out of 76 million starting with 'I'? How
about combining some techniques - you could create an index on the first two
chars of the field (should be selective enough to give an index scan),
select from that, and select the actual data with the like clause.
I
Hi everyone,
Some more data I've collected, trying to best tune dbt-2 with
8.0beta4. Was hoping for some suggestions, explanations for what I'm
seeing, etc.
A review of hardware I've got:
4 x 1.5Ghz Itanium 2
16GB memory
84 15K RPM disks (6 controlers, 12 channels)
Physical Database table layo
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 higher without the partial
> > indexes than it is wit
> I have two dual opteron 248's with 4g of ram each, 6x36G 15k rpm ultra
> 320 scsi disks in hardware raid 5, and they are by far the fastest
> machines I've user used. As far as this "headache" of using 64 bit
> Linux, I've experienced no such thing. I'm using gentoo on both
> machines, which are
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 higher without the partial
> indexes than it is with them, and the actual runtime of the query is
> definitely longer without the part
Hi guys,
I have been given a dual PIII with 768MB RAM and I am going to install
PostgreSQL on it, for data warehousing reasons. I have also been given four
160 Ultra SCSI disks (36MB each) with a RAID controller (Adaptec 2100). I
am going to use a RAID5 architecture (this gives me approximate
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 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 sequential
> > scan on the table, with a filt
Matt Nuzum <[EMAIL PROTECTED]> writes:
> To me, these three queries seem identical... why doesn't the first one
> (simplest to understand and write) go the same speed as the third one?
This is the standard problem that the planner has to guess about the
selectivity of inequalities involving non-co
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 sequential
> scan on the table, with a filter -- quite obviously this is slow as
> heck, and yes, I've run analyze several
Matt Nuzum wrote:
To me, these three queries seem identical... why doesn't the first one
(simplest to understand and write) go the same speed as the third one?
If you look at the explain output, you will notice that only the 3rd
query is using an Index Scan, where as the 1st and 2nd are doing a
> 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 heck, and yes, I've run
> analyze several times and in fact have the vacuum anal
Just wanted to know if there were any insights after looking at
requested 'explain analyze select ...'?
Thanks,
--patrick
__
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com
---(end of broadcast)--
I have two dual opteron 248's with 4g of ram each, 6x36G 15k rpm ultra
320 scsi disks in hardware raid 5, and they are by far the fastest
machines I've user used. As far as this "headache" of using 64 bit
Linux, I've experienced no such thing. I'm using gentoo on both
machines, which are dedicated
To me, these three queries seem identical... why doesn't the first one
(simplest to understand and write) go the same speed as the third one?
I'll I'm trying to do is get statistics for one day (in this case,
today) summarized. Table has ~25M rows. I'm using postgres 7.3.? on
rh linux 7.3 (note
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 this field, such as "SELECT ... WHERE field
On Fri, Nov 05, 2004 at 07:47:54AM -0500, Christopher Browne wrote:
>
> How about changing the criterion to:
>
> where today between '2004-11-05' and '2004-11-06';
>
> That ought to make use of the index on "today".
Yes it should, but it'll also return records that have a "today"
value of '20
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 this field, such as "SELECT ... WHERE field LIKE 'A%'"
>
> I thought an interesting way to do this would be to simply create
> p
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 'AA%';" should be just as fast or very close to
Does anybody have any experiences with postgresql 7.4+ running on amd-64
in 64 bit mode? Specifically, does it run quicker and if so do the
performance benefits justify the extra headaches running 64 bit linux?
Right now I'm building a dual Opteron 246 with 4 gig ddr400.
Merlin
--
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
> 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
> the index above, which is what's not making sense to me.
It looks for an exact exp
> For some reason it's a requirement that partial wildcard
> searches are done on this field, such as "SELECT ... WHERE
> field LIKE 'A%'"
>
> I thought an interesting way to do this would be to simply
> create partial indexes for each letter on that field, and it
> works when the query matche
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
After a long battle with technology, [EMAIL PROTECTED] (Antony Paul), an earthling,
wrote:
> Hi all,
>I have a table which have more than 20 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There
On Fri, Nov 05, 2004 at 05:32:49PM +0900, Michael Glaesemann wrote:
>
> On Nov 5, 2004, at 5:14 PM, Michael Glaesemann wrote:
>
> >
> >On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
> >>where today::date = '2004-11-05';
> >>
> >>This is the only condition in the query. There is a btree index on t
On Fri, 2004-11-05 at 12:46 +0530, Antony Paul wrote:
> Hi all,
>I have a table which have more than 20 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column to
To what extent would your problems be solved by having a 2nd server, a replication
system (such as slony-1, but there are others), and some sort of load-balancer in
front of it? The load-balancing could be as simple as round-robin DNS server,
perhaps...
Then when you need to do maintenance such
On Fri, Nov 05, 2004 at 12:46:20PM +0530, Antony Paul wrote:
>I have a table which have more than 20 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column toda
On Nov 5, 2004, at 5:14 PM, Michael Glaesemann wrote:
On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
where today::date = '2004-11-05';
This is the only condition in the query. There is a btree index on the
column today.
Is there any way to optimise it.
I'm sure others out there have better ideas, b
On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
where today::date = '2004-11-05';
This is the only condition in the query. There is a btree index on the
column today.
Is there any way to optimise it.
I'm sure others out there have better ideas, but you might want to try
where current_date = date '20
38 matches
Mail list logo
