pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect is not using index?
3+ messages / 2 participants
[nested] [flat]
* select is not using index?
@ 2004-02-04 22:55 Mark Harrison <mh@pixar.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Harrison @ 2004-02-04 22:55 UTC (permalink / raw)
To: pgsql-performance
We are suddenly getting slow queries on a particular table.
Explain shows a sequential scan. We have "vacuum analyze" ed
the table.
Any hints?
Many TIA!
Mark
testdb=# \d bigtable
Table "public.bigtable"
Column | Type | Modifiers
---------+---------+-----------
id | bigint | not null
typeid | integer | not null
reposid | integer | not null
Indexes: bigtable_id_key unique btree (id)
Foreign Key constraints: type FOREIGN KEY (typeid) REFERENCES types(typeid) ON UPDATE NO ACTION ON DELETE NO ACTION,
repository FOREIGN KEY (reposid) REFERENCES repositories(reposid) ON UPDATE NO ACTION ON DELETE NO ACTION
testdb=# select count(1) from bigtable;
count
---------
3056831
(1 row)
testdb=# explain select * from bigtable where id = 123;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on bigtable (cost=0.00..60000.00 rows=1 width=16)
Filter: (id = 123)
(2 rows)
testdb=# vacuum verbose analyze bigtable;
INFO: --Relation public.bigtable--
INFO: Pages 19200: Changed 0, Empty 0; Tup 3056831: Vac 0, Keep 0, UnUsed 207009.
Total CPU 1.03s/0.24u sec elapsed 9.32 sec.
INFO: Analyzing public.bigtable
VACUUM
testdb=# explain select * from bigtable where id = 123;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on bigtable (cost=0.00..57410.39 rows=1 width=16)
Filter: (id = 123)
(2 rows)
--
Mark Harrison
Pixar Animation Studios
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select is not using index?
@ 2004-02-04 23:22 Corey Edwards <coreyed@linxtechnologies.com>
parent: Mark Harrison <mh@pixar.com>
0 siblings, 1 reply; 3+ messages in thread
From: Corey Edwards @ 2004-02-04 23:22 UTC (permalink / raw)
To: Mark Harrison <mh@pixar.com>; +Cc: pgsql-performance
On Wed, 2004-02-04 at 14:55, Mark Harrison wrote:
> testdb=# \d bigtable
> Table "public.bigtable"
> Column | Type | Modifiers
> ---------+---------+-----------
> id | bigint | not null
> typeid | integer | not null
> reposid | integer | not null
> Indexes: bigtable_id_key unique btree (id)
> testdb=# explain select * from bigtable where id = 123;
Your column is a bigint but 123 defaults to type int. Indexes aren't
used when there's a type mismatch. Use an explicit cast or quote it:
select * from bigtable where id = 123::bigint;
Or
select * from bigtable where id = '123';
Corey
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select is not using index?
@ 2004-02-05 17:31 Mark Harrison <mh@pixar.com>
parent: Corey Edwards <coreyed@linxtechnologies.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Harrison @ 2004-02-05 17:31 UTC (permalink / raw)
To: Corey Edwards <coreyed@linxtechnologies.com>; +Cc: pgsql-performance
Corey Edwards wrote:
> Your column is a bigint but 123 defaults to type int. Indexes aren't
> used when there's a type mismatch. Use an explicit cast or quote it:
>
> select * from bigtable where id = 123::bigint;
>
> Or
>
> select * from bigtable where id = '123';
Thanks Corey, both of these do exactly what I need...
Cheers,
Mark
--
Mark Harrison
Pixar Animation Studios
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-02-05 17:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-04 22:55 select is not using index? Mark Harrison <mh@pixar.com>
2004-02-04 23:22 ` Corey Edwards <coreyed@linxtechnologies.com>
2004-02-05 17:31 ` Mark Harrison <mh@pixar.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox