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

[Willy-Bas Loos <willybas@xxxxxxxxx>]

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'

--

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

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth