pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with large table not using indexes (I think)
8+ messages / 4 participants
[nested] [flat]

* Problem with large table not using indexes (I think)
@ 2006-12-23 19:26 Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:33 ` Re: Problem with large table not using indexes (I think) A. Kretschmer <andreas.kretschmer@schollglas.com>
  2006-12-23 19:39 ` Re: Problem with large table not using indexes (I think) Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Benjamin Arai @ 2006-12-23 19:26 UTC (permalink / raw)
  To: pgsql-admin; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Problem with large table not using indexes (I think)
  2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
@ 2006-12-23 19:33 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
  2006-12-23 19:41   ` Re: Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  1 sibling, 1 reply; 8+ messages in thread

From: A. Kretschmer @ 2006-12-23 19:33 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Problem with large table not using indexes (I think)
  2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:33 ` Re: Problem with large table not using indexes (I think) A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-12-23 19:41   ` Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:51     ` Re: Problem with large table not using indexes (I think) Benjamin Arai <me@benjaminarai.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Benjamin Arai @ 2006-12-23 19:41 UTC (permalink / raw)
  To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-general@postgresql.org

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
>   



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Problem with large table not using indexes (I think)
  2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:33 ` Re: Problem with large table not using indexes (I think) A. Kretschmer <andreas.kretschmer@schollglas.com>
  2006-12-23 19:41   ` Re: Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
@ 2006-12-23 19:51     ` Benjamin Arai <me@benjaminarai.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Benjamin Arai @ 2006-12-23 19:51 UTC (permalink / raw)
  To: Benjamin Arai <benjamin@araisoft.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-general@postgresql.org

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
>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Problem with large table not using indexes (I think)
  2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
@ 2006-12-23 19:39 ` Jeff Frost <jeff@frostconsultingllc.com>
  2006-12-23 19:48   ` Re: Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:52   ` Re: Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Jeff Frost @ 2006-12-23 19:39 UTC (permalink / raw)
  To: Benjamin Arai <benjamin@araisoft.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Problem with large table not using indexes (I think)
  2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:39 ` Re: Problem with large table not using indexes (I think) Jeff Frost <jeff@frostconsultingllc.com>
@ 2006-12-23 19:48   ` Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:55     ` Re: Problem with large table not using indexes (I think) Jeff Frost <jeff@frostconsultingllc.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Benjamin Arai @ 2006-12-23 19:48 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org

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.
>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Problem with large table not using indexes (I think)
  2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:39 ` Re: Problem with large table not using indexes (I think) Jeff Frost <jeff@frostconsultingllc.com>
  2006-12-23 19:48   ` Re: Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
@ 2006-12-23 19:55     ` Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Jeff Frost @ 2006-12-23 19:55 UTC (permalink / raw)
  To: Benjamin Arai <benjamin@araisoft.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Problem with large table not using indexes (I think)
  2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
  2006-12-23 19:39 ` Re: Problem with large table not using indexes (I think) Jeff Frost <jeff@frostconsultingllc.com>
@ 2006-12-23 19:52   ` Benjamin Arai <benjamin@araisoft.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Benjamin Arai @ 2006-12-23 19:52 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin

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

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.
>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2006-12-23 19:55 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-23 19:26 Problem with large table not using indexes (I think) Benjamin Arai <benjamin@araisoft.com>
2006-12-23 19:33 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-12-23 19:41   ` Benjamin Arai <benjamin@araisoft.com>
2006-12-23 19:51     ` Benjamin Arai <me@benjaminarai.com>
2006-12-23 19:39 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-12-23 19:48   ` Benjamin Arai <benjamin@araisoft.com>
2006-12-23 19:55     ` Jeff Frost <jeff@frostconsultingllc.com>
2006-12-23 19:52   ` Benjamin Arai <benjamin@araisoft.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox