pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem 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