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'
On Wed, Jun 26, 2013 at 7:35 PM, Igor Neyman <iney...@perceptron.com> wrote: > Hi, > postgres does a seqscan, even though there is an index present and it > should be much more efficient to use it. > I tried to synthetically reproduce it, but it won't make the same choice > when i do. > I can reproduce it with a simplified set of the data itself though. > here's the query, and the analyzed plan: > select count(*) > from d2 > join g2 on g2.gid=d2.gid > where g2.k=1942 > > Aggregate (cost=60836.71..60836.72 rows=1 width=0) (actual > time=481.526..481.526 rows=1 loops=1) > -> Hash Join (cost=1296.42..60833.75 rows=1184 width=0) (actual > time=317.403..481.513 rows=17 loops=1) > Hash Cond: (d2.gid = g2.gid) > -> Seq Scan on d2 (cost=0.00..47872.54 rows=3107454 width=8) > (actual time=0.013..231.707 rows=3107454 loops=1) > -> Hash (cost=1290.24..1290.24 rows=494 width=8) (actual > time=0.207..0.207 rows=121 loops=1) > Buckets: 1024 Batches: 1 Memory Usage: 5kB > -> Index Scan using g_blok on g2 (cost=0.00..1290.24 > rows=494 width=8) (actual time=0.102..0.156 rows=121 loops=1) > Index Cond: (k = 1942) > Total runtime: 481.600 ms > Here's the DDL: > create table g2 (gid bigint primary key, k integer); > create table d2 (id bigint primary key, gid bigint); > --insert into g2 (...) > --insert into d2 (...) > create index g_blok on g2(blok); > create index d_gid on d2(gid); > alter table d2 add constraint d_g_fk foreign key (gid) references g2 (gid); > analyze d2; > analyze g2; > > Any advice? > > Cheers, > Willy-Bas Loos > -- > > So, did you try to set: > > enable_seqscan = off > > and see if different execution plan is more efficient? > > Igor Neyman > -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
