[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
[PERFORM] general peformance question
Hi, This a kind of newbie-question. I've been using Postgres for a long time in a low transction environment - and it is great. Now I've got an inquiry for using Postgresql in a heavy-load on-line system. This system must handle something like 20 questions per sec with a response time at 1/10 sec. Each question will result in approx 5-6 reads and a couple of updates. Anybody have a feeling if this is realistic on a Intelbased Linux server with Postgresql. Ofcourse I know that this is too little info for an exact answer but - as I said - maybe someone can give a hint if it's possible. Maybe someone with heavy-load can give an example of what is possible... Regards Conny Thimrén ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] postgresql performance on linux port
I just restored a database running on a solaris box to a linux box and queries take forever to execute. The linux box is faster and has twice the memory allocated to postgresql, is there anything obvious that I should look at? It is using a journal file system. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] is it possible to get the optimizer to use indexes
Christopher Kings-Lynne [EMAIL PROTECTED] writes: It appears that the optimizer only uses indexes for = clause? The optimizer will used indexes for LIKE clauses, so long as the clause is a prefix search, eg: SELECT * FROM test WHERE a LIKE 'prf%'; Doesn't this still depend on your locale? -Doug ---(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
[PERFORM] general performance question
Hi, This a kind of newbie-question. I've been using Postgres for a long time in a low transction environment - and it is great. Now I've got an inquiry for using Postgresql in a heavy-load on-line system. This system must handle something like 20 questions per sec with a response time at 1/10 sec. Each question will result in approx 5-6 reads and a couple of updates. Anybody have a feeling if this is realistic on a Intelbased Linux server with Postgresql. Ofcourse I know that this is too little info for an exact answer but - as I said - maybe someone can give a hint if it's possible. Maybe someone with heavy-load can give an example of what is possible... Regards Conny Thimrén
Re: [PERFORM] general peformance question
On Thu, 2003-12-18 at 12:04, Conny Thimren wrote: Hi, This a kind of newbie-question. I've been using Postgres for a long time in a low transction environment - and it is great. Now I've got an inquiry for using Postgresql in a heavy-load on-line system. This system must handle something like 20 questions per sec with a response time at 1/10 sec. Each question will result in approx 5-6 reads and a couple of updates. Anybody have a feeling if this is realistic on a Intelbased Linux server with Postgresql. Ofcourse I know that this is too little info for an exact answer but - as I said - maybe someone can give a hint if it's possible. Maybe someone with heavy-load can give an example of what is possible... Ok, is that 20 questions per second (20 in parallel taking 1 second each) or serialized taking 50ms each. Are they simple selects / updates (less than 10 rows in result set, very simple joins) or are they monster 30 table join queries? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] postgresql performance on linux port
Michael Guerin [EMAIL PROTECTED] writes: I just restored a database running on a solaris box to a linux box and queries take forever to execute. Did you remember to run ANALYZE? Have you applied the same configuration settings that you were using before? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] general peformance question
On Thu, 18 Dec 2003, Conny Thimren wrote: Hi, This a kind of newbie-question. I've been using Postgres for a long time in a low transction environment - and it is great. Now I've got an inquiry for using Postgresql in a heavy-load on-line system. This system must handle something like 20 questions per sec with a response time at 1/10 sec. Each question will result in approx 5-6 reads and a couple of updates. Anybody have a feeling if this is realistic on a Intelbased Linux server with Postgresql. Ofcourse I know that this is too little info for an exact answer but - as I said - maybe someone can give a hint if it's possible. Maybe someone with heavy-load can give an example of what is possible... That really depends on how heavy each query is, so it's hard to say from what little you've given us. If you are doing simple banking style transactions, then you can easily handle this load, if you are talking a simple shopping cart, ditto, if, however, you are talking about queries that run 4 or 5 tables with millions of rows againts each other, you're gonna have to test it yourself. With the autovacuum daemon running, I ran a test overnight of pgbench (more for general purpose burn in than anything else) pgbench -i -s 100 pgbench -c 50 -t 25 that's 10 million transactions, and it took just over twelve hours to complete at 220+ transactions per second. so, for financials, you're likely to find it easy to meet your target. But as the tables get bigger / more complex / more interconnected you'll see a drop in performance. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [HACKERS] fsync method checking
Ideally that path isn't taken very often. But I'm currently having a discussion off-list with a CMU student who seems to be seeing a case where it happens a lot. (She reports that both WALWriteLock and WALInsertLock are causes of a lot of process blockages, which seems to mean that a lot of the WAL I/O is being done with both held, which would have to mean that AdvanceXLInsertBuffer is doing the I/O. More when we figure out what's going on exactly...) I would figure, that this is in a situation where a large transaction fills one XLInsertBuffer, and a lot of WAL buffers are not yet written. Andreas ---(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
