pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect on 1milion register = 6s
6+ messages / 6 participants
[nested] [flat]
* select on 1milion register = 6s
@ 2007-07-28 19:27 Bruno Rodrigues Siqueira <bruno@ravnus.com>
2007-07-28 19:56 ` Re: select on 1milion register = 6s Hervé Piedvache <bill.footcow@gmail.com>
2007-07-28 19:59 ` Re: select on 1milion register = 6s Craig James <craig_james@emolecules.com>
0 siblings, 2 replies; 6+ messages in thread
From: Bruno Rodrigues Siqueira @ 2007-07-28 19:27 UTC (permalink / raw)
To: pgsql-performance
Friends,
Who can help me? My SELECT in a base with 1 milion register,
using expression index = 6seconds.
Please, I don't know how to makes it better.
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select on 1milion register = 6s
2007-07-28 19:27 select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
@ 2007-07-28 19:56 ` Hervé Piedvache <bill.footcow@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Hervé Piedvache @ 2007-07-28 19:56 UTC (permalink / raw)
To: pgsql-performance; +Cc: Bruno Rodrigues Siqueira <bruno@ravnus.com>
Do you have analyzed your table before doing this ?
Le samedi 28 juillet 2007, Bruno Rodrigues Siqueira a écrit :
> Friends,
>
>
>
>
>
>
>
> Who can help me? My SELECT in a base with 1 milion register,
> using expression index = 6seconds.
>
>
>
>
>
> Please, I don't know how to makes it better.
>
>
>
>
>
> Thanks
--
Hervé Piedvache
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select on 1milion register = 6s
2007-07-28 19:27 select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
@ 2007-07-28 19:59 ` Craig James <craig_james@emolecules.com>
2007-07-29 04:51 ` Re: select on 1milion register = 6s Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Craig James @ 2007-07-28 19:59 UTC (permalink / raw)
To: Bruno Rodrigues Siqueira <bruno@ravnus.com>; pgsql-performance
Bruno Rodrigues Siqueira wrote:
> Who can help me? My SELECT in a base with 1 milion register,
> using expression index = 6seconds…
Run your query using
EXPLAIN ANALYZE SELECT ... your query ...
and then post the results to this newsgroup. Nobody can help until they see the results of EXPLAIN ANALYZE. Also, include all other relevant information, such as Postgres version, operating system, amount of memory, and any changes you have made to the Postgres configuration file.
Craig
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select on 1milion register = 6s
2007-07-28 19:27 select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
2007-07-28 19:59 ` Re: select on 1milion register = 6s Craig James <craig_james@emolecules.com>
@ 2007-07-29 04:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-07-29 18:02 ` Re: select on 1milion register = 6s Alvaro Herrera <alvherre@commandprompt.com>
2007-08-01 17:37 ` Re: select on 1milion register = 6s Jan Dittmer <jdi@l4x.org>
0 siblings, 2 replies; 6+ messages in thread
From: Scott Marlowe @ 2007-07-29 04:51 UTC (permalink / raw)
To: Bruno Rodrigues Siqueira <bruno@ravnus.com>; +Cc: pgsql-performance
On 7/28/07, Bruno Rodrigues Siqueira <bruno@ravnus.com> wrote:
>
> Ok.
> QUERY PLAN
> Sort (cost=11449.37..11449.40 rows=119 width=8) (actual
> time=14431.537..14431.538 rows=2 loops=1)
> Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
> -> HashAggregate (cost=11448.79..11448.96 rows=119 width=8) (actual
> time=14431.521..14431.523 rows=2 loops=1)
> -> Index Scan using detalhamento_bas_idx3003 on detalhamento_bas
> (cost=0.00..11442.95 rows=11679 width=8) (actual time=0.135..12719.155
> rows=2335819 loops=1)
See the row mismatch there? It expects about 11k rows, gets back 2.3
million. That's a pretty big misestimate. Have you run analyze
recently on this table?
Is there a reason you're doing this:
to_char( data_encerramento ,'yyyy-mm')
between '2006-12' and '2007-01'
when you should be able to just do:
data_encerramento between '2006-12-01' and '2007-01-31'
? that should be able to use good estimates from analyze. My guess
is the planner is making a bad guess because of the way you're
handling the dates.
> SERVER
> DELL PowerEdge 2950
> XEON Quad-Core 3.0Ghz
> 4Gb RAM
> Linux CentOS 5.0 64-bits
> Postgres 8.1.4
> Postgresql.conf
> # - Memory -
>
> shared_buffers = 50000 # min 16 or max_connections*2, 8KB
400 Meg is kind of low for a server with 4 G ram. 25% is more
reasonable (i.e. 125000 buffers)
> work_mem = 3145728 # min 64, size in KB
> maintenance_work_mem = 4194304 # min 1024, size in KB
Whoa nellie! thats ~ 3 Gig of work mem, and 4 gig of maintenance work
mem. In a machine with 4 gig ram, that's a recipe for disaster.
Something more reasonable would be 128000 (~125Meg) for each since
you've limited your machine to 10 connections you should be ok.
setting work_mem too high can run your machine out of memory and into
a swap storm that will kill performance.
> fsync = off # turns forced synchronization on or
> off
So, the data in this database isn't important? Cause that's what
fsync = off says to me. Better to buy yourself a nice battery backed
caching RAID controller than turn off fsync.
> effective_cache_size = 41943040 # typically 8KB each
And you're machine has 343,604,830,208 bytes of memory available for
caching? Seems a little high to me.
> random_page_cost = 1 # units are one sequential page
> fetch
Seldom if ever is it a good idea to bonk the planner on the head with
random_page_cost=1. setting it to 1.2 ot 1.4 is low enough, but 1.4
to 2.0 is more realistic.
> stats_start_collector = off
> #stats_command_string = off
> #stats_block_level = off
> #stats_row_level = off
> #stats_reset_on_server_start = off
I think you need stats_row_level on for autovacuum, but I'm not 100% sure.
Let us know what happens after fixing these settings and running
analyze and running explain analyze, with possible changes to the
query.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select on 1milion register = 6s
2007-07-28 19:27 select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
2007-07-28 19:59 ` Re: select on 1milion register = 6s Craig James <craig_james@emolecules.com>
2007-07-29 04:51 ` Re: select on 1milion register = 6s Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-07-29 18:02 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2007-07-29 18:02 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Bruno Rodrigues Siqueira <bruno@ravnus.com>; pgsql-performance
Scott Marlowe wrote:
> On 7/28/07, Bruno Rodrigues Siqueira <bruno@ravnus.com> wrote:
> > stats_start_collector = off
> > #stats_command_string = off
> > #stats_block_level = off
> > #stats_row_level = off
> > #stats_reset_on_server_start = off
>
> I think you need stats_row_level on for autovacuum, but I'm not 100% sure.
That's correct (of course you need "start_collector" on as well). Most
likely, autovacuum is not even running.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select on 1milion register = 6s
2007-07-28 19:27 select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
2007-07-28 19:59 ` Re: select on 1milion register = 6s Craig James <craig_james@emolecules.com>
2007-07-29 04:51 ` Re: select on 1milion register = 6s Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-08-01 17:37 ` Jan Dittmer <jdi@l4x.org>
1 sibling, 0 replies; 6+ messages in thread
From: Jan Dittmer @ 2007-08-01 17:37 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Bruno Rodrigues Siqueira <bruno@ravnus.com>; pgsql-performance
Scott Marlowe wrote:
>> random_page_cost = 1 # units are one sequential page
>> fetch
>
> Seldom if ever is it a good idea to bonk the planner on the head with
> random_page_cost=1. setting it to 1.2 ot 1.4 is low enough, but 1.4
> to 2.0 is more realistic.
Which is probably the reason why the planner thinks a seq scan is
faster than an index scan...
Jan
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-08-01 17:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-28 19:27 select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
2007-07-28 19:56 ` Hervé Piedvache <bill.footcow@gmail.com>
2007-07-28 19:59 ` Craig James <craig_james@emolecules.com>
2007-07-29 04:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-07-29 18:02 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-08-01 17:37 ` Jan Dittmer <jdi@l4x.org>
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