From: Willy-Bas Loos [mailto:willybas@xxxxxxxxx] Sent: Wednesday, June 26, 2013 3:19 PM To: Igor Neyman Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxxx> wrote: From: Willy-Bas Loos [mailto:willybas@xxxxxxxxx] Sent: Wednesday, June 26, 2013 3:04 PM To: Igor Neyman Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
