From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Willy-Bas Loos <willybas(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: seqscan for 100 out of 3M rows, index present |
Date: | 2013-06-26 19:30:17 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70EC1BBD8A46@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
From: Willy-Bas Loos [mailto:willybas(at)gmail(dot)com]
Sent: Wednesday, June 26, 2013 3:19 PM
To: Igor Neyman
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] seqscan for 100 out of 3M rows, index present
plan with enable_seqscan off:
Aggregate (cost=253892.48..253892.49 rows=1 width=0) (actual time=208.681..208.681 rows=1 loops=1)
-> Nested Loop (cost=5.87..253889.49 rows=1198 width=0) (actual time=69.403..208.647 rows=17 loops=1)
-> Index Scan using geo_blok_idx on geo g (cost=0.00..1314.43 rows=500 width=8) (actual time=45.776..46.147 rows=121 loops=1)
Index Cond: (blok = 1942)
-> Bitmap Heap Scan on bmp_data d (cost=5.87..502.91 rows=179 width=8) (actual time=1.340..1.341 rows=0 loops=121)
Recheck Cond: (geo_id = g.geo_id)
-> Bitmap Index Scan on bmp_data_geo_idx (cost=0.00..5.82 rows=179 width=0) (actual time=1.206..1.206 rows=0 loops=121)
Index Cond: (geo_id = g.geo_id)
Total runtime: 208.850 ms
On Wed, Jun 26, 2013 at 9:08 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
From: Willy-Bas Loos [mailto:willybas(at)gmail(dot)com]
Sent: Wednesday, June 26, 2013 3:04 PM
To: Igor Neyman
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] seqscan for 100 out of 3M rows, index present
nope
$ grep ^[^#] /etc/postgresql/9.1/main/postgresql.conf|grep -e ^[^[:space:]]
data_directory = '/var/lib/postgresql/9.1/main' # use data in another directory
hba_file = '/etc/postgresql/9.1/main/pg_hba.conf' # host-based authentication file
ident_file = '/etc/postgresql/9.1/main/pg_ident.conf' # ident configuration file
external_pid_file = '/var/run/postgresql/9.1-main.pid' # write an extra PID file
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
unix_socket_directory = '/var/run/postgresql' # (change requires restart)
ssl = true # (change requires restart)
shared_buffers = 2GB # min 128kB
work_mem = 100MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
synchronous_commit = off # synchronization level; on, off, or local
checkpoint_segments = 10 # in logfile segments, min 1, 16MB each
log_line_prefix = '%t ' # special values:
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
--
How much RAM you have on this machine?
What else is this machine is being used for (besides being db server)?
And, what is your setting for effective_cache_size? It looks like you didn't change it from default (128MB).
You need to adjust effective_cache_size so somewhat between 60%-75% of RAM, if the database is the main process running on this machine.
Again, effective_cache_size could be set on session level, so you could try it before changing GUC in postgresql.conf.
When trying it, don't forget to change enable_seqscan back to "on" (if it's still "off").
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | Willy-Bas Loos | 2013-06-26 20:12:02 | Re: seqscan for 100 out of 3M rows, index present |
Previous Message | Willy-Bas Loos | 2013-06-26 19:18:51 | Re: seqscan for 100 out of 3M rows, index present |