[PERFORM] select max/count(id) not using index
Hi. I have a table with 24k records and btree index on column 'id'. Is this normal, that 'select max(id)' or 'select count(id)' causes a sequential scan? It takes over 24 seconds (on a pretty fast machine): = explain ANALYZE select max(id) from ogloszenia; QUERY PLAN -- Aggregate (cost=3511.05..3511.05 rows=1 width=4) (actual time=24834.629..24834.629 rows=1 loops=1) - Seq Scan on ogloszenia (cost=0.00..3473.04 rows=15204 width=4) (actual time=0.013..24808.377 rows=16873 loops=1) Total runtime: 24897.897 ms Maybe it's caused by a number of varchar fields in this table? However, 'id' column is 'integer' and is primary key. Clustering table on index created on 'id' makes such a queries many faster, but they still use a sequential scan. Richard. -- First they ignore you. Then they laugh at you. Then they fight you. Then you win. - Mohandas Gandhi. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] select max/count(id) not using index
I have a table with 24k records and btree index on column 'id'. Is this normal, that 'select max(id)' or 'select count(id)' causes a sequential scan? It takes over 24 seconds (on a pretty fast machine): = explain ANALYZE select max(id) from ogloszenia; Yes, it is. It is a known issue with Postgres's extensible operator architecture. The work around is to have an index on the id column and do this instead: SELECT id FROM ogloszenia ORDER BY id DESC LIMIT 1; Which will be really fast. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] select max/count(id) not using index
Hello It is normal behavior PostgreSQL. Use SELECT id FROM tabulka ORDER BY id DESC LIMIT 1; regards Pavel On Mon, 22 Dec 2003, Ryszard Lach wrote: Hi. I have a table with 24k records and btree index on column 'id'. Is this normal, that 'select max(id)' or 'select count(id)' causes a sequential scan? It takes over 24 seconds (on a pretty fast machine): = explain ANALYZE select max(id) from ogloszenia; QUERY PLAN -- Aggregate (cost=3511.05..3511.05 rows=1 width=4) (actual time=24834.629..24834.629 rows=1 loops=1) - Seq Scan on ogloszenia (cost=0.00..3473.04 rows=15204 width=4) (actual time=0.013..24808.377 rows=16873 loops=1) Total runtime: 24897.897 ms Maybe it's caused by a number of varchar fields in this table? However, 'id' column is 'integer' and is primary key. Clustering table on index created on 'id' makes such a queries many faster, but they still use a sequential scan. Richard. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] select max/count(id) not using index
Guten Tag Ryszard Lach, Am Montag, 22. Dezember 2003 um 11:39 schrieben Sie: RL Hi. RL I have a table with 24k records and btree index on column 'id'. Is this RL normal, that 'select max(id)' or 'select count(id)' causes a sequential RL scan? It takes over 24 seconds (on a pretty fast machine): Yes, that was occasionally discussed on the mailinglists. For the max(id) you can use instead SELECT id FROM table ORDER BY id DESC LIMIT 1 Christoph Nelles = explain ANALYZE select max(id) from ogloszenia; RL QUERY PLAN RL -- RL Aggregate (cost=3511.05..3511.05 rows=1 width=4) (actual RL time=24834.629..24834.629 rows=1 loops=1) RL- Seq Scan on ogloszenia (cost=0.00..3473.04 rows=15204 width=4) RL (actual time=0.013..24808.377 rows=16873 loops=1) RL Total runtime: 24897.897 ms RL Maybe it's caused by a number of varchar fields in this table? However, RL 'id' column is 'integer' and is primary key. RL Clustering table on index created on 'id' makes such a queries RL many faster, but they still use a sequential scan. RL Richard. -- Mit freundlichen Grssen Evil Azraelmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
