Re: [PERFORM] Why those queries do not utilize indexes?
I could force Postgres to use the best index by removing condition "msgstatus = CAST( 0 AS smallint );" from WHERE clause & set enable_seqscan to off; Total runtime in this case dropped from 1883 ms ( sequential reads ) to 1.598 ms ( best index ). But unfortunatelly It does not resolve my problem. I can not remove above condition. I need to find a way to use whole condition "WHERE user_id = CAST( 2 AS BIGINT ) and msgstatus = CAST( 0 AS smallint );" and still utilyze index. Yes you are right. Using "messagesStatus" index is even worse for my data set then sequential scan. Igor Artimenko --- Dennis Bjorklund <[EMAIL PROTECTED]> wrote: > On Fri, 27 Aug 2004, Artimenko Igor wrote: > > > 1. Sequential search and very high cost if set enable_seqscan to on; > > Seq scan on messageinfo ( cost=0.00..24371.30, rows =36802 ) > > > > 2. Index scan but even bigger cost if set enable_seqscan to off; > > Index messagesStatus on messageinfo ( Cost=0.00..27220.72, rows=36802 ) > > So pg thinks that a sequential scan will be a little bit faster (The cost > is a little bit smaller). If you compare the actual runtimes maybe you > will see that pg was right. In this case the cost is almost the same so > the runtime is probably almost the same. > > When you have more data pg will start to use the index since then it will > be faster to use an index compared to a seq. scan. > > -- > /Dennis Björklund > > ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Why those queries do not utilize indexes?
Hi everybody! Here is my queries: 1. explain SELECT * FROM messageinfo WHERE user_id = CAST( 2 AS BIGINT ) and msgstatus = CAST( 0 AS smallint ); 2. explain SELECT * FROM messageinfo WHERE messageinfo.user_id = 2::int8 and msgstatus = 0::smallint; In both cases Explain command shows: 1. Sequential search and very high cost if set enable_seqscan to on; Seq scan on messageinfo ( cost=0.00..24371.30, rows =36802 ) 2. Index scan but even bigger cost if set enable_seqscan to off; Index messagesStatus on messageinfo ( Cost=0.00..27220.72, rows=36802 ) messageinfo table has 200 records which meet this criteria and 662420 in total: CREATE TABLE messageinfo ( user_id int8 NOT NULL, msgstatus int2 NOT NULL DEFAULT (0)::smallint, receivedtime timestamp NOT NULL DEFAULT now(), msgread bool DEFAULT false, CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE, ) WITH OIDS; CREATE INDEX msgstatus ON messageinfo USING btree (user_id, msgstatus); CREATE INDEX "messagesStatus" ON messageinfo USING btree (msgstatus); CREATE INDEX msgread ON messageinfo USING btree (user_id, msgread); CREATE INDEX "receivedTime" ON messageinfo USING btree (receivedtime); MY QUESTIONS ARE: 1. Should I afraid of high cost indexes? Or query will still be very efficient? 2. Postgres does not use the index I need. For my data sets its always msgstatus index is narrowest compare with messagesStatus one. Is any way to enforce to use a particular index? Whats the logic when Postgres chooses one index compare with the other. 3. I can change db structure to utilize Postgres specifics if you can tell them to me. 4. Also, originally I had messagesStatus index having 2 components ( msgstatus, user_id ). But query SELECT * FROM messageinfo WHERE msgstatus = 0 did not utilize indexes in this case. It only worked if both index components are in WHERE part. So I have to remove 2-nd component user_id from messagesStatus index even I wanted it. Is any way that where clause has only 1-st component but index is utilized? Igor Artimenko __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Postgres does not utilyze indexes
Thanks a lot. This issue has been resolved by casting to int8. I thought Postgres does those casts up by himself implicitelly. = Thanks a lot Igor Artimenko I specialize in Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Postgres does not utilize indexes. Why?
Hi everybody!
I cant make use of indexes even I tried the same test by changing different settings
in
postgres.conf like geqo to off/on & geqo related parameters, enable_seqscan off/on &
so on. Result
is the same.
Here is test itself:
Ive created simplest table test and executed the same statement explain analyze
select id from
test where id = 5; Few times I added 100,000 records, applied vacuum full; and
issued above
explain command.
Postgres uses sequential scan instead of index one.
Of cause Time to execute the same statement constantly grows. In my mind index should
not allow
time to grow so much.
Why Postgres does not utilizes primary unique index?
What Im missing? It continue growing even there are 1,200,000 records. It should at
least start
using index at some point.
Details are below:
100,000 records:
QUERY PLAN
Seq Scan on test (cost=0.00..2427.00 rows=2 width=8) (actual time=99.626..199.835
rows=1
loops=1)
Filter: (id = 5)
Total runtime: 199.990 ms
200,000 records:
QUERY PLAN
-
Seq Scan on test (cost=0.00..4853.00 rows=2 width=8) (actual time=100.389..402.770
rows=1
loops=1)
Filter: (id = 5)
Total runtime: 402.926 ms
300,000 records:
QUERY PLAN
-
Seq Scan on test (cost=0.00..7280.00 rows=1 width=8) (actual time=100.563..616.064
rows=1
loops=1)
Filter: (id = 5)
Total runtime: 616.224 ms
(3 rows)
I've created test table by script:
CREATE TABLE test
(
id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,
description char(50),
CONSTRAINT users_pkey PRIMARY KEY (id)
);
CREATE SEQUENCE next_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 100
START 1
CACHE 5
CYCLE;
I use postgres 7.4.2
=
Thanks a lot
Igor Artimenko
I specialize in
Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data
Modeling
__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
