Thread: Problem with large table not using indexes (I think)

Problem with large table not using indexes (I think)

From
Benjamin Arai
Date:
Hi,

The largest table in my database (30GB) has mysteriously went from
taking milli-seconds to perform a query to minutes.  This disks are fine
and I have a 4GB shared_memory.  Could this slow down have to do with
the fsm_max_pages or something else like that?  I made it larger but the
queries still taking a long time.  I do daily vacuum's but I don't run
it with -z or --full.  I would like to avoid doing a --full if possible
because it would literally take over a week to complete.  Any help would
be greatly appreciated.

Benjamin


Re: Problem with large table not using indexes (I think)

From
"A. Kretschmer"
Date:
am  Sat, dem 23.12.2006, um 11:26:08 -0800 mailte Benjamin Arai folgendes:
> because it would literally take over a week to complete.  Any help would
> be greatly appreciated.

What says an 'explain analyse'?


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: [ADMIN] Problem with large table not using indexes (I think)

From
Jeff Frost
Date:
On Sat, 23 Dec 2006, Benjamin Arai wrote:

> The largest table in my database (30GB) has mysteriously went from taking
> milli-seconds to perform a query to minutes.  This disks are fine and I have
> a 4GB shared_memory.  Could this slow down have to do with the fsm_max_pages
> or something else like that?  I made it larger but the queries still taking a
> long time.  I do daily vacuum's but I don't run it with -z or --full.  I
> would like to avoid doing a --full if possible because it would literally
> take over a week to complete.  Any help would be greatly appreciated.

Benjamin,

When is the last time you ran ANALYZE?  That's what the -z option does.  If
you're only vacuuming once daily, you should definitely analyze with the -z
flag as well.

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: Problem with large table not using indexes (I think)

From
Benjamin Arai
Date:
Function Scan on "getTimeSeries"  (cost=0.00..12.50 rows=1000 width=24)
(actual time=11065.981..11067.008 rows=262 loops=1)
 Total runtime: 11067.991 ms
(2 rows)

It is a PL function.  Do I need to break it down?

A. Kretschmer wrote:
> am  Sat, dem 23.12.2006, um 11:26:08 -0800 mailte Benjamin Arai folgendes:
>
>> because it would literally take over a week to complete.  Any help would
>> be greatly appreciated.
>>
>
> What says an 'explain analyse'?
>
>
> Andreas
>

Re: [ADMIN] Problem with large table not using indexes (I think)

From
Benjamin Arai
Date:
I thought that you only need to use the -z flag if the distribution of
the data is changing.

Jeff Frost wrote:
> On Sat, 23 Dec 2006, Benjamin Arai wrote:
>
>> The largest table in my database (30GB) has mysteriously went from
>> taking milli-seconds to perform a query to minutes.  This disks are
>> fine and I have a 4GB shared_memory.  Could this slow down have to do
>> with the fsm_max_pages or something else like that?  I made it larger
>> but the queries still taking a long time.  I do daily vacuum's but I
>> don't run it with -z or --full.  I would like to avoid doing a --full
>> if possible because it would literally take over a week to complete.
>> Any help would be greatly appreciated.
>
> Benjamin,
>
> When is the last time you ran ANALYZE?  That's what the -z option
> does.  If you're only vacuuming once daily, you should definitely
> analyze with the -z flag as well.
>

Re: [ADMIN] Problem with large table not using indexes (I think)

From
Jeff Frost
Date:
On Sat, 23 Dec 2006, Benjamin Arai wrote:

> I thought that you only need to use the -z flag if the distribution of the
> data is changing.

You're absolutely correct.  Have you not been inserting, updating or deleting
data?  It sounds like you are based on the followup email you just sent:

> One more note about my problem, when you run a query on older data in the
> table then it work great but if you query newer data then is very slow.

> Ex.

> SELECT * from my_table WHERE date >=12/1/2005 and date <= 12/1/2006; <- slow

> SELECT * from my_table WHERE date >=12/1/2002 and date <= 12/1/2003; <- fast

> It just has to do with the new data for some reason.

Try and run "ANALYZE my_table;" from psql and see if that makes things faster.
If it does, then you likely need to analyze more often than never.  I'm
guessing if you're inserting data that has a date or timestamp, then you
definitely need to be analyzing..that's even one of the examples used in the
docs:

http://www.postgresql.org/docs/8.1/interactive/maintenance.html#VACUUM-FOR-STATISTICS

"For example, a timestamp column that contains the time of row update will
have a constantly-increasing maximum value as rows are added and updated; such
a column will probably need more frequent statistics updates than, say, a
column containing URLs for pages accessed on a website. The URL column may
receive changes just as often, but the statistical distribution of its values
probably changes relatively slowly."


>
> Jeff Frost wrote:
>> On Sat, 23 Dec 2006, Benjamin Arai wrote:
>>
>>> The largest table in my database (30GB) has mysteriously went from taking
>>> milli-seconds to perform a query to minutes.  This disks are fine and I
>>> have a 4GB shared_memory.  Could this slow down have to do with the
>>> fsm_max_pages or something else like that?  I made it larger but the
>>> queries still taking a long time.  I do daily vacuum's but I don't run it
>>> with -z or --full.  I would like to avoid doing a --full if possible
>>> because it would literally take over a week to complete.  Any help would
>>> be greatly appreciated.
>>
>> Benjamin,
>>
>> When is the last time you ran ANALYZE?  That's what the -z option does.  If
>> you're only vacuuming once daily, you should definitely analyze with the -z
>> flag as well.
>>
>
>

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: Problem with large table not using indexes (I think)

From
Benjamin Arai
Date:
One more note about my problem,  when you run a query on older data in
the table then it work great but if you query newer data then is very slow.

Ex.

SELECT * from my_table WHERE date >=12/1/2005 and date <= 12/1/2006; <- slow

SELECT * from my_table WHERE date >=12/1/2002 and date <= 12/1/2003; <- fast

It just has to do with the new data for some reason.

Benjamin


Benjamin Arai wrote:
> Function Scan on "getTimeSeries"  (cost=0.00..12.50 rows=1000
> width=24) (actual time=11065.981..11067.008 rows=262 loops=1)
> Total runtime: 11067.991 ms
> (2 rows)
>
> It is a PL function.  Do I need to break it down?
>
> A. Kretschmer wrote:
>> am  Sat, dem 23.12.2006, um 11:26:08 -0800 mailte Benjamin Arai
>> folgendes:
>>
>>> because it would literally take over a week to complete.  Any help
>>> would be greatly appreciated.
>>>
>>
>> What says an 'explain analyse'?
>>
>>
>> Andreas
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>