Kevin Galligan wrote, On 29-10-08 23:35:
An example of a slow query is...
select count(*) from bigdatatable where age between 22 and 40 and state
= 'NY';
explain analyze returned the following...
Aggregate (cost=5179639.55..5179639.56 rows=1 width=0) (actual
time=389529.895..389529.897 ro
On Wed, Oct 29, 2008 at 9:18 PM, Kevin Galligan <[EMAIL PROTECTED]> wrote:
> I'm approaching the end of my rope here. I have a large database.
> 250 million rows (ish). Each row has potentially about 500 pieces of
> data, although most of the columns are sparsely populated.
>
*snip*
>
> So, went
b. But if Access manages okay
with the depivoted table, it might be worth a try. Based on 500 fields, 250M
records, 2% filled it looks like it might depivot your table overnight, or
better. You'd finish with about 2.5 billion rows.
Best wishes,
Nick
> -Original Message-----
&g
Oct 29, 2008 at 7:52 PM, Dann Corbit <[EMAIL PROTECTED]> wrote:
>*From:* Kevin Galligan [mailto:[EMAIL PROTECTED]
> *Sent:* Wednesday, October 29, 2008 4:34 PM
> *To:* Dann Corbit
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: FW: [GENERAL] Slow query performan
From: Kevin Galligan [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2008 4:34 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] Slow query performance
Sorry for the lack of detail. Index on both state and age. Not a
clustered on both as the queries are
From: Kevin Galligan [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 29, 2008 3:16 PM
To: Dann Corbit
Subject: Re: [GENERAL] Slow query performance
Columns are as follows:
account | integer |
city | character varying(20) |
zip | character(5) |
dincome
An example of a slow query is...
select count(*) from bigdatatable where age between 22 and 40 and state =
'NY';
explain analyze returned the following...
Aggregate (cost=5179639.55..5179639.56 rows=1 width=0) (actual
time=389529.895..389529.897 rows=1 loops=1)
-> Bitmap Heap Scan on bigda
On Wed, Oct 29, 2008 at 2:18 PM, Kevin Galligan <[EMAIL PROTECTED]> wrote:
> I'm approaching the end of my rope here. I have a large database.
> 250 million rows (ish). Each row has potentially about 500 pieces of
> data, although most of the columns are sparsely populated.
A couple of notes her
I'm approaching the end of my rope here. I have a large database.
250 million rows (ish). Each row has potentially about 500 pieces of
data, although most of the columns are sparsely populated.
What I'm trying to do is, essentially, search for sub-sets of that
data based on arbitrary queries of
On Sat, Nov 01, 2003 at 10:18:14AM +1300, Mark Kirkwood wrote:
>
> Dave Weaver wrote:
>
> >>- clustering the "obs" table on "station"
> >
> >Sorry, I don't understand what you mean by this - can you explain?
> Supposing obs_pkey is on (station, valid_time):
>
> cluster obs_pkey on obs
Be aware
Dave Weaver wrote:
- clustering the "obs" table on "station"
Sorry, I don't understand what you mean by this - can you explain?
Supposing obs_pkey is on (station, valid_time):
cluster obs_pkey on obs
will re-order the rows in obs based on the index obs_pkey. (This is
clustering on 'sta
I'm having severe performance issues with a conceptually simple
database. The database has one table, containing weather observations.
The table currently has about 13.5 million rows, and is being updated
constantly. The database is running on a dual 550MHz PIII with 512MB RAM.
On the whole, que
"Dave Weaver" <[EMAIL PROTECTED]> writes:
> Is the upgrade likely to make a difference?
I'm not sure if it would help for this specific query, but in general
each major PG release has useful performance improvements over the
previous one.
What I'm wondering about is an index-bloat problem (see th
On Thu, 30 Oct 2003, Dave Weaver wrote:
> Jeff wrote:
> > Dave Weaver wrote:
> > > For instance:
> > > SELECT station, air_temp FROM obs
> > > WHERE station = 'EGBB'
> > > AND valid_time > '28/8/03 00:00'
> > > AND valid_time < '28/10/03 00:00'
> > >
> > > takes 4 mi
> > For instance:
> > SELECT station, air_temp FROM obs
> > WHERE station = 'EGBB'
> > AND valid_time > '28/8/03 00:00'
> > AND valid_time < '28/10/03 00:00'
> >
> > takes 4 mins 32 secs.
How many rows should that return?
[explain analyze will tell you that]
and wh
Jeff wrote:
> Dave Weaver wrote:
> > For instance:
> > SELECT station, air_temp FROM obs
> > WHERE station = 'EGBB'
> > AND valid_time > '28/8/03 00:00'
> > AND valid_time < '28/10/03 00:00'
> >
> > takes 4 mins 32 secs.
>
> How many rows should that return?
> [expla
16 matches
Mail list logo
