Services
24×7×365 Technical Support
Migration to PostgreSQL
High Availability Deployment
Database Audit
Remote DBA for PostgreSQL
Products
Postgres Pro Enterprise
Postgres Pro Standard
Cloud Solutions
Postgres Extensions
Resources
Blog
Documentation
Webinars
Videos
Presentations
Community
Events
Training Courses
Books
Demo Database
Mailing List Archives
About
Leadership team
Partners
Customers
In the News
Press Releases
Press Info
Services
24×7×365 Technical Support
Migration to PostgreSQL
High Availability Deployment
Database Audit
Remote DBA for PostgreSQL
Products
Postgres Pro Enterprise
Postgres Pro Standard
Cloud Solutions
Postgres Extensions
Resources
Blog
Documentation
Webinars
Videos
Presentations
Community
Events
Training Courses
Books
Demo Database
Mailing List Archives
About
Leadership team
Partners
Customers
In the News
Press Releases
Press Info
Facebook
Downloads
Home
>
mailing lists
seqscan for 100 out of 3M rows, index present - Mailing list pgsql-performance
From
Willy-Bas Loos
Subject
seqscan for 100 out of 3M rows, index present
Date
June 26, 2013
18:45:49
Msg-id
CAHnozTgJQq8vPns_WpS+7i=JrQm9zaCyQ_-eKD2HzCibfSZ1Qw@mail.gmail.com
Whole thread
Raw
Responses
Re: seqscan for 100 out of 3M rows, index present
(Igor Neyman <ineyman@perceptron.com>)
Re: seqscan for 100 out of 3M rows, index present
(Scott Marlowe <scott.marlowe@gmail.com>)
Re: seqscan for 100 out of 3M rows, index present
(Victor Yegorov <vyegorov@gmail.com>)
List
pgsql-performance
Tree view
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
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
pgsql-performance
by date:
Previous
From:
Tom Lane
Date:
26 June 2013, 04:41:55
Subject:
Re: Weird, bad 0.5% selectivity estimate for a column equal to itself
Next
From:
Igor Neyman
Date:
26 June 2013, 20:36:47
Subject:
Re: seqscan for 100 out of 3M rows, index present
Есть вопросы? Напишите нам!
Соглашаюсь с условиями обработки персональных данных
I confirm that I have read and accepted PostgresPro’s
Privacy Policy
.
I agree to get Postgres Pro discount offers and other marketing communications.
✖
×
×
Everywhere
Documentation
Mailing list
List:
all lists
pgsql-general
pgsql-hackers
buildfarm-members
pgadmin-hackers
pgadmin-support
pgsql-admin
pgsql-advocacy
pgsql-announce
pgsql-benchmarks
pgsql-bugs
pgsql-chat
pgsql-cluster-hackers
pgsql-committers
pgsql-cygwin
pgsql-docs
pgsql-hackers-pitr
pgsql-hackers-win32
pgsql-interfaces
pgsql-jdbc
pgsql-jobs
pgsql-novice
pgsql-odbc
pgsql-patches
pgsql-performance
pgsql-php
pgsql-pkg-debian
pgsql-pkg-yum
pgsql-ports
pgsql-rrreviewers
pgsql-ru-general
pgsql-sql
pgsql-students
pgsql-testers
pgsql-translators
pgsql-www
psycopg
Period
anytime
within last day
within last week
within last month
within last 6 months
within last year
Sort by
date
reverse date
rank
Services
24×7×365 Technical Support
Migration to PostgreSQL
High Availability Deployment
Database Audit
Remote DBA for PostgreSQL
Products
Postgres Pro Enterprise
Postgres Pro Standard
Cloud Solutions
Postgres Extensions
Resources
Blog
Documentation
Webinars
Videos
Presentations
Community
Events
Training Courses
Books
Demo Database
Mailing List Archives
About
Leadership team
Partners
Customers
In the News
Press Releases
Press Info
By continuing to browse this website, you agree to the use of cookies. Go to
Privacy Policy
.
I accept cookies