Vlad Arkhipov <[EMAIL PROTECTED]> writes:
> I've came across this issue while writing report-like query for 2 not
> very large tables. I've tried several methods to resolve this one (see
> below). But now I'm really stuck...
It looks like you are wishing to optimize for all-in-memory situations,
i
I've came across this issue while writing report-like query for 2 not
very large tables. I've tried several methods to resolve this one (see
below). But now I'm really stuck...
PostgreSQL 8.3, default configuration
There are 2 tables (structure was simplified to show only problematic
place):
crea
"b wragg" <[EMAIL PROTECTED]> writes:
> I'm new to this, but I can't figure out why the "Gunbower Island Primary
> School" is getting top place. How do I get the query to improve the ranking
> so that an exact match (like "Gunbower|Island|Vic") gets a higher position?
I'm new at this too, but AFAI
Craig James wrote:
Tom Lane wrote:
Craig James <[EMAIL PROTECTED]> writes:
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
My ex
Hi,
I am using postgresql for application. daily i will get more
than 5,00,000 records.
i have done the partitioning of the table for each month.
while generating reports, i will do join on some other table
with the large table
it takes too much time to get the d
Hi all,
I'm running the following query to match a supplied text string to an actual
place name which is recorded in a table with extra info like coordinates,
etc.
SELECT ts_rank_cd(textsearchable_index_col , query, 32 /* rank/(rank+1) */)
AS rank,*
FROM gazetteer, to_tsquery('Gunbower|Island|Vic
Tom Lane wrote:
Craig James <[EMAIL PROTECTED]> writes:
Count() on Oracle and MySQL is almost instantaneous, even for very large
tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
My experience doesn't ma
Craig James wrote:
My question is: What do the other databases do that Postgres can't do,
and why not?
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
I think Mysql can only do that for the myisam engine - innodb and
> The strange thing of course is that the data is exactly the same for
> both runs, the tables have not been changed between runs, and I did
> them right after another. Even more strange is that the seq scan is
> faster than the index scan.
It is not strange at all, since both queries read A
On 6-Mar-08, at 5:10 PM, Stephen Denne wrote:
Dave Cramer wrote:
I have two almost identical queries. Strangely enough the one
that uses the index is slower ???
The index scan is being used so that it can retrieve the rows in the
name order.
It expects that if it was to retrieve every row
Dave Cramer wrote:
> I have two almost identical queries. Strangely enough the one
> that uses the index is slower ???
The index scan is being used so that it can retrieve the rows in the name order.
It expects that if it was to retrieve every row via the index, it would get
about 1010 rows th
Tom Lane wrote:
> Greg Smith <[EMAIL PROTECTED]> writes:
> > On Wed, 26 Dec 2007, Guillaume Smet wrote:
> >> beta RPMs are by default compiled with --enable-debug and
> >> --enable-cassert which doesn't help them to fly fast...
>
> > Got that right. Last time I was going crazy after running pgben
On 6-Mar-08, at 12:26 PM, Josh Berkus wrote:
Dave,
Below I have two almost identical queries. Strangely enough the one
that uses the index is slower ???
My first guess would be that records are highly correlated by DOB
and not at
all by name. However, it would help if you supplied both
OK, added to TODO:
* Reduce memory usage of aggregates in set returning functions
http://archives.postgresql.org/pgsql-performance/2008-01/msg00031.php
---
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >
Bruce Momjian <[EMAIL PROTECTED]> writes:
> This this a bug or TODO item?
TODO, I think. I wouldn't want to risk pushing a change in this into
back branches.
regards, tom lane
>> I'm not sure why it's like this. Some digging in the CVS history shows
>> that indeed the c
Craig James <[EMAIL PROTECTED]> writes:
> Count() on Oracle and MySQL is almost instantaneous, even for very large
> tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
regards, tom lane
Dave,
> Below I have two almost identical queries. Strangely enough the one
> that uses the index is slower ???
My first guess would be that records are highly correlated by DOB and not at
all by name. However, it would help if you supplied both the index
definitions and what changed between
This this a bug or TODO item?
---
Tom Lane wrote:
> "John Smith" <[EMAIL PROTECTED]> writes:
> >> It's pipelined either way. But int8 is a pass-by-reference data type,
> >> and it sounds like we have a memory leak for this
On Thu, 06 Mar 2008 07:28:50 -0800
Craig James <[EMAIL PROTECTED]> wrote:
> In the 3 years I've been using Postgres, the problem of count() performance
> has come up more times than I can recall, and each time the answer is, "It's
> a sequential scan -- redesign your application."
>
> My questio
On Thu, 6 Mar 2008 12:43:57 +0530
"sathiya psql" <[EMAIL PROTECTED]> wrote:
> >
> > Actually it isn't obvious as oids have been deprecated for years.
>
>
> no in my version it is now also available
I didn't say they were gone. I said they are deprecated. You should not
be using them.
>
>
On Thu, 2008-03-06 at 07:28 -0800, Craig James wrote:
...
> My question is: What do the other databases do that Postgres can't do, and
> why not?
>
> Count() on Oracle and MySQL is almost instantaneous, even for very large
> tables. So why can't Postgres do what they do?
...
I can vouch that Or
On Thu, Mar 6, 2008 at 3:49 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
>
> You only get this accelerated significantly when using MyISAM, which can
> tell you an exact count of all the rows it hasn't corrupted yet.
Please don't do that again. I'm going to have to spend the next hour
cleaning coff
On Thu, 6 Mar 2008, Steinar H. Gunderson wrote:
On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote:
Count() on Oracle and MySQL is almost instantaneous, even for very large
tables. So why can't Postgres do what they do?
In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB
In response to Craig James <[EMAIL PROTECTED]>:
> In the 3 years I've been using Postgres, the problem of count() performance
> has come up more times than I can recall, and each time the answer is, "It's
> a sequential scan -- redesign your application."
>
> My question is: What do the other d
On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote:
> Count() on Oracle and MySQL is almost instantaneous, even for very large
> tables. So why can't Postgres do what they do?
In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB table.)
/* Steinar */
--
Homepage: http://www
Craig James wrote:
> This is a real problem. Countless people (including me) have
> spent significant effort rewriting applications because of this
> performance flaw in Postgres. Over and over, the response is,
> "You don't really need to do that ... change your application."
> Well, sure, it's
In the 3 years I've been using Postgres, the problem of count() performance has come up
more times than I can recall, and each time the answer is, "It's a sequential scan
-- redesign your application."
My question is: What do the other databases do that Postgres can't do, and why
not?
Count()
On Thu, 6 Mar 2008, Tom Lane wrote:
Red Hat did a "Visual Explain" tool several years ago, which is
unmaintained now but still available for download
(http://sources.redhat.com/rhdb/). I've heard that EDB picked it up and
is now maintaining their own fork, but I don't know the status of that
Greg Smith <[EMAIL PROTECTED]> writes:
> On Thu, 6 Mar 2008, RaviRam Kolipaka wrote:
>> My goal is create a visual representation of the expain plan.
> This problem has been solved already by code that's in pgadmin and you
> might look at that source code for hints if you want to write your own
On Thu, 6 Mar 2008, sathiya psql wrote:
any way will you explain., what is this COST, actual time and other
stuffs
There's a long list of links to tools and articles on this subject at
http://www.postgresqldocs.org/index.php/Using_EXPLAIN
--
* Greg Smith [EMAIL PROTECTED] http://www.gre
On Thu, 6 Mar 2008, sathiya psql wrote:
is there any article saying the difference between this 7.3 and 8.4
I've collected a list of everything on this topic I've seen at
http://www.postgresqldocs.org/index.php/Version_8.3_Changes
The Feature Matrix linked to there will be a quicker way to
A. Kretschmer wrote:
am Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
TRIGGER i can use if i want the count of the whole table, but i require for
some of the rows with WHERE condition
so how to do that ???
Okay, in this case a TRIGGER are a bad idea. You c
On Thu, 6 Mar 2008, RaviRam Kolipaka wrote:
My goal is create a visual representation of the expain plan.
This problem has been solved already by code that's in pgadmin and you
might look at that source code for hints if you want to write your own
implementation. There's a good intro to usi
sathiya psql escribió:
> > Yes it is the latest stable version.
>
> is there any article saying the difference between this 7.3 and 8.4
http://www.postgresql.org/docs/8.3/static/release.html
In particular,
http://www.postgresql.org/docs/8.3/static/release-8-3.html
http://www.postgresql.org/docs/
am Thu, dem 06.03.2008, um 18:13:50 +0530 mailte sathiya psql folgendes:
>
> Yes it is the latest stable version.
>
>
> is there any article saying the difference between this 7.3 and 8.4
http://developer.postgresql.org/pgdocs/postgres/release.html
Andreas
--
Andreas Kretschmer
Kontakt:
Of course, the official documentation covers that information in its
release notes
http://www.postgresql.org/docs/8.3/static/release.html
best wishes
Harald
On Thu, Mar 6, 2008 at 1:43 PM, sathiya psql <[EMAIL PROTECTED]> wrote:
>
>
> >
> >
> >
> > Yes it is the latest stable version.
>
> is th
> Yes it is the latest stable version.
>
is there any article saying the difference between this 7.3 and 8.4
Hi,
On 6-Mar-08, at 6:58 AM, sathiya psql wrote:
The only thing that is going to help you is really fast disks, and
more memory, and you should consider moving to 8.3 for all the other
performance benefits.
Is 8.3 is a stable version or what is the latest stable version of
postgres ??
Yes
On 6-Mar-08, at 1:43 AM, sathiya psql wrote:
is there any way to explicitly force the postgres to use index scan
If you want to count all the rows in the table there is only one way
to do it (without keeping track yourself with a trigger ); a seq scan.
An index will not help you.
The o
I had 50 lakh records in my table...
while counting that am using that row in where condition... which makes
problem, cpu is waiting for device...
Debian OS, postresql 7.4, 50 lakh records.
Query is
EXPLAIN ANALYZE select count(call_id) from call_log where call_id > 1;
while seeing the top, cp
40 matches
Mail list logo
