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(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'
>
> --
>
> 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