Re: seqscan for 100 out of 3M rows, index present

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'

--

You could change this setting on session level, and prove yourself or query optimizer right (or wrong :)

Igor Neyman

...
...
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