On Jun 27, 2008, at 9:53 PM, Adam Rich wrote:
"Bob Duffey" <[EMAIL PROTECTED]> writes:
I'm seeing some query plans that I'm not expecting. The table in
question
is reasonably big (130,000,000 rows). The table has a primary key,
indexed
by one field ("ID", of type bigint). Thus, I wou
hubert depesz lubaczewski wrote:
> On Fri, Jun 27, 2008 at 06:37:45PM -0400, Bill Moran wrote:
>> Just in case you're not a FreeBSD expert, it's ktrace on FreeBSD. strace
>> is the Linux equivalent.
>
> i'm not an freebsd expert.
>
> i ran ktrace -p - it exited immediately.
> then i ran ktrace
Bill Thoen wrote:
What I'm wondering is how in PostgreSQL do you select only the first 10
records from a selection, then the next 10, then the next, and possibly
go back to a previous 10?
LIMIT with OFFSET has already been mentioned. There's another option if
your web app is backed by an app
2008/6/28 Adam Rich <[EMAIL PROTECTED]>:
>
> > This is not wrong, or at least not obviously wrong. A full-table
> > indexscan is often slower than seqscan-and-sort. If the particular
> > case is wrong for you, you need to look at adjusting the planner's
> > cost parameters to match your environm
>
> "Bob Duffey" <[EMAIL PROTECTED]> writes:
> > I'm seeing some query plans that I'm not expecting. The table in
> question
> > is reasonably big (130,000,000 rows). The table has a primary key,
> indexed
> > by one field ("ID", of type bigint). Thus, I would expect the
> following
> > query
2008/6/28 Tom Lane <[EMAIL PROTECTED]>:
> "Bob Duffey" <[EMAIL PROTECTED]> writes:
> > I'm seeing some query plans that I'm not expecting. The table in
> question
> > is reasonably big (130,000,000 rows). The table has a primary key,
> indexed
> > by one field ("ID", of type bigint). Thus, I wo
On Fri, Jun 27, 2008 at 06:37:45PM -0400, Bill Moran wrote:
> Just in case you're not a FreeBSD expert, it's ktrace on FreeBSD. strace
> is the Linux equivalent.
i'm not an freebsd expert.
i ran ktrace -p - it exited immediately.
then i ran ktrace -p -f stats.ktrace.log -t\+
and it also exited
"Bob Duffey" <[EMAIL PROTECTED]> writes:
> I'm seeing some query plans that I'm not expecting. The table in question
> is reasonably big (130,000,000 rows). The table has a primary key, indexed
> by one field ("ID", of type bigint). Thus, I would expect the following
> query to simply scan throu
Hi,
I'm seeing some query plans that I'm not expecting. The table in question
is reasonably big (130,000,000 rows). The table has a primary key, indexed
by one field ("ID", of type bigint). Thus, I would expect the following
query to simply scan through the table using the primary key:
select
On Fri, 27 Jun 2008 18:04:19 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> hubert depesz lubaczewski <[EMAIL PROTECTED]> writes:
> > friend asked me to setup replication on their machines, and i noticed
> > that one of cpus (2 quad xeons) is used to 100%:
> > pgsql 58241 99.0 0.2 22456 7432 ??
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes:
> friend asked me to setup replication on their machines, and i noticed
> that one of cpus (2 quad xeons) is used to 100%:
> pgsql 58241 99.0 0.2 22456 7432 ?? Rs Thu10AM 1530:35.93 postgres:
> stats collector process(postgres)
Hmm,
"Aswani Kumar" <[EMAIL PROTECTED]> writes:
> The Postgres was built using ./configure
> XML2_CONFIG=/usr/local/libxml/bin/xml2-config
> --prefix=/usr/local/postgresql-8.3.1 --with-libxml. Libxml2-2.6.32 was
> built using ./configure --prefix=/usr/local/libxml and is the location
> pointed to in t
On Fri, Jun 27, 2008 at 2:09 PM, Bill Thoen <[EMAIL PROTECTED]> wrote:
> Thanks for tip on OFFSET. That's just what I needed. It's so easy when you
> know the command you're looking for, and so hard when you know what you want
> to do but don't know what the command is called!
I would strongly sug
Thanks for tip on OFFSET. That's just what I needed. It's so easy when
you know the command you're looking for, and so hard when you know what
you want to do but don't know what the command is called!
Thanks,
- Bill Thoen
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
Hello
apart from the increasing OFFSET method, you only need to
traverse the results sequentially, you can do a variant of
this:
let us assume your resultset has a a unique column pk, and is ordered on
column o:
initial select:
select * from foo order by o limit 10;
next page
select * from
On 6/27/08, Bill Thoen <[EMAIL PROTECTED]> wrote:
>
> What I want to do is present the results of a query in a web page, but only
> 10 rows at a time. My PostgreSQL table has millions of records and if I
> don't add a LIMIT 10 to the SQL selection, the request can take too long.
> The worst case sc
use a dynamic select in the web page
$1 = 10
$2 = 5
select * from mytable limit $1 OFFSET $2
--- On Fri, 6/27/08, Bill Thoen <[EMAIL PROTECTED]> wrote:
From: Bill Thoen <[EMAIL PROTECTED]>
Subject: [GENERAL] Advice Wanted on Selecting Multi-row Data Requests in 10-Row
Blocks
To: pgsql-general@p
>
> What I want to do is present the results of a query in a web page, but
> only 10 rows at a time. My PostgreSQL table has millions of records and
> if I don't add a LIMIT 10 to the SQL selection, the request can take
> too
> long. The worst case scenario is when the user requests all records
>
What I want to do is present the results of a query in a web page, but
only 10 rows at a time. My PostgreSQL table has millions of records and
if I don't add a LIMIT 10 to the SQL selection, the request can take too
long. The worst case scenario is when the user requests all records
without add
2008/6/27 wstrzalka <[EMAIL PROTECTED]>:
> Is there any possibility to have reverse() function in the PG core in
> the future?
>
this function is in orafce package.
http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29
Regards
Pavel Stehule
> There are some implementation already like ie.
2008/6/27 A B <[EMAIL PROTECTED]>:
> Thanks for the suggestion on GET DIAGNOSTICS.
>
> But concerning EXECUTE, if I do
>
> BEGIN
> EXECUTE QueryA
> EXCEPTION WHEN OTHERS THEN
> QueryB
> END;
>
>
> will it execute QueryB if QueryA fails?
yes, but it's not preferable way. It creates subtran
On Wed, Jun 25, 2008 at 8:17 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> I think IF ROW(NEW.*) <> ROW(OLD.*) will work in recent releases.
>
> Actually you'd better use IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) ...
> you really don't want to rely on <> as it will not give the behavior
> you want in t
On Fri, Jun 27, 2008 at 10:21 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Douglas McNaught" <[EMAIL PROTECTED]> writes:
>> On Fri, Jun 27, 2008 at 11:52 AM, Benjamin Weaver
>> <[EMAIL PROTECTED]> wrote:
>>> Not knowing about such things, I was scared by the following quote.
>
>> Distro support for 6
Is there any possibility to have reverse() function in the PG core in
the future?
There are some implementation already like ie. this one:
http://blog.frosties.org/post/2007/08/28/Fonction-reverse-C-avec-PostgreSQL
I think author will have nothing against using his code as he
published it on his
Tom Lane wrote:
> Rodrigo Gonzalez <[EMAIL PROTECTED]> writes:
>> pg_dump is working fine now, the problem appear with the pg_buffercache
>> query...without it I dont notice anything wrong with DBbut of course
>> there is something wrong. Can be pg_buffercache the problem?
>
> Oh ... looking a
On Fri, Jun 27, 2008 at 11:52 AM, Benjamin Weaver
<[EMAIL PROTECTED]> wrote:
> Sorry, guys, for wasting bandwidth on this! You guys gave just the answer I
> wanted to hear. Sounds like there aren't any problems.
>
> Not knowing about such things, I was scared by the following quote. Perhaps
> bi
Rodrigo Gonzalez <[EMAIL PROTECTED]> writes:
> pg_dump is working fine now, the problem appear with the pg_buffercache
> query...without it I dont notice anything wrong with DBbut of course
> there is something wrong. Can be pg_buffercache the problem?
Oh ... looking again at your latest probl
"Douglas McNaught" <[EMAIL PROTECTED]> writes:
> On Fri, Jun 27, 2008 at 11:52 AM, Benjamin Weaver
> <[EMAIL PROTECTED]> wrote:
>> Not knowing about such things, I was scared by the following quote.
> Distro support for 64-bit x86 in 2004 was light-years behind where it
> is now. A lot of stuff w
Thanks for the suggestion on GET DIAGNOSTICS.
But concerning EXECUTE, if I do
BEGIN
EXECUTE QueryA
EXCEPTION WHEN OTHERS THEN
QueryB
END;
will it execute QueryB if QueryA fails?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscripti
Sorry, guys, for wasting bandwidth on this! You guys gave just the answer I
wanted to hear. Sounds like there aren't any problems.
Not knowing about such things, I was scared by the following quote. Perhaps
binaries do not need to be compiled as 64 bit binaries on a 64 bit machine? Or
perhaps
That example also reports that it uses the index. Only the "is true"
variation insists on seq. scan.
On Thu, Jun 26, 2008 at 4:08 PM, Lennin Caro <[EMAIL PROTECTED]> wrote:
> use this
>
> explain analyze select * from result where active = 't';
>
> --- On *Thu, 6/26/08, Phillip Mills <[EMAIL PRO
On Fri, Jun 27, 2008 at 3:06 AM, Dean Rasheed <[EMAIL PROTECTED]> wrote:
>> Someone pointed out in an earlier thread that a
>> way to fix this, for updates on a multi-table view (where most of the
>> complication lies), is to write a "trigger" function that updates all
>> the constituent tables exc
use REINDEX
REINDEX ip_dst_idx
OR
REINDEX table_name
http://www.postgresql.org/docs/8.3/static/sql-reindex.html
tell us the result
thank
--- On Fri, 6/27/08, Ganbold <[EMAIL PROTECTED]> wrote:
From: Ganbold <[EMAIL PROTECTED]>
Subject: [GENERAL] ERROR: concurrent insert in progress
To:
use REINDEX
http://www.postgresql.org/docs/8.3/static/sql-reindex.html
tell us the result
thank
--- On Fri, 6/27/08, Ganbold <[EMAIL PROTECTED]> wrote:
From: Ganbold <[EMAIL PROTECTED]>
Subject: [GENERAL] ERROR: concurrent insert in progress
To: pgsql-general@postgresql.org
Date: Friday, Jun
On Fri, Jun 27, 2008 at 3:37 PM, Raymond O'Donnell <[EMAIL PROTECTED]> wrote:
> On 26/06/2008 17:43, Ludwig Kniprath wrote:
>>
>> As far as I know pgadmin uses gtk,
>
> Correct me if I'm wrong, but I don't think PgAdmin uses gtk on Windows.
No, it most certainly doesn't.
--
Dave Page
EnterpriseD
On 26/06/2008 17:43, Ludwig Kniprath wrote:
As far as I know pgadmin uses gtk,
Correct me if I'm wrong, but I don't think PgAdmin uses gtk on Windows.
Ray.
--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL
Rodrigo Gonzalez <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> When you say "restored from backup", are you talking about a pg_dump
>> backup, or what?
> yes, a pg_dump backup.
There must be something mighty odd in that backup. Would you be willing
to send it to me off-list, so I can try to r
Alban Hertroys wrote:
> On Jun 26, 2008, at 5:41 AM, Rodrigo Gonzalez wrote:
>
>> Tom Lane wrote:
>>> Rodrigo Gonzalez <[EMAIL PROTECTED]> writes:
Craig Ringer wrote:
> What platform are you using?
>>>
It's running under CentOS 4.4 using ext3, no RAID or LVM.
Server is quad xeon
Tom Lane wrote:
> Rodrigo Gonzalez <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>>> No, it's clear that things are already broken before pg_dump started.
>>> You need to show us how to get to this state from a fresh database.
>
>> Interestinga new problem maybe, or maybe the same one
>>
Hi all,
I just want to report that we had here almost exactly the same problem
as reported here:
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00134.php
The whole scenario repeated the same: production DB refused to work,
restarted in single user mode, run vacuum (few hours), postgres
hi
first of all - i know next to nothing about bsd.
friend asked me to setup replication on their machines, and i noticed
that one of cpus (2 quad xeons) is used to 100%:
pgsql 58241 99.0 0.2 22456 7432 ?? Rs Thu10AM 1530:35.93 postgres: stats
collector process(postgres)
what might be
2008/6/27 A B <[EMAIL PROTECTED]>:
>> I think you'd be well advised to rethink your table layout so you don't
>> need so much dynamic SQL. The above is going to suck on both
>> performance and readability grounds, and it doesn't look like it's
>> accomplishing anything you couldn't do by combining
> I think you'd be well advised to rethink your table layout so you don't
> need so much dynamic SQL. The above is going to suck on both
> performance and readability grounds, and it doesn't look like it's
> accomplishing anything you couldn't do by combining all the Rating
> tables into one table
> Date: Thu, 26 Jun 2008 12:47:04 -0500
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Subject: Re: what are rules for?
> CC: pgsql-general@postgresql.org; [EMAIL PROTECTED]; [EMAIL PROTECTED];
> [EMAIL PROTECTED]
>
> On Thu, Jun 26, 2008 at 12:11 P
44 matches
Mail list logo
