Re: [GENERAL] EXPLAIN ANALYZE not considering primary and unique indices!
Thanks a lot everybody! I got it clear. I was wrongly thinking that PostgreSQL might not be creating the indices by default. regards Sanjay ---(end of broadcast)--- TIP 1: 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: [GENERAL] EXPLAIN ANALYZE not considering primary and unique indices!
Bill Moran <[EMAIL PROTECTED]> writes: > In response to Sanjay <[EMAIL PROTECTED]>: >> Seq Scan on website (cost=0.00..1.31 rows=1 width=162) (actual >> time=0.047..0.051 rows=1 loops=1) >> Filter: (website_id = 1) >> Total runtime: 0.102 ms >> Wondering why it is not using the index, which would have been >> automatically created for the primary key. > Because PG thinks the seq scan is faster than an index scan. The cost estimate shows there is only one page in the table (assuming seq_page_cost has its default value of 1.0). You're basically never going to get an indexscan plan for a one-page table: it takes one read to fetch the page, and any reads done to fetch index pages are going to be more expensive than just examining the tuples, unless you have a *whole* lot of tuples in the one page. Load the table up with a realistic amount of data, and ANALYZE it, and then see what plan you get. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] EXPLAIN ANALYZE not considering primary and unique indices!
On Monday 27 August 2007 05:21, Sanjay <[EMAIL PROTECTED]> wrote: >Wondering why it is not using the index, which would have > been > automatically created for the primary key. Because you not only have just one row in the whole table, 100% of them will match the query. In short, one page fetch for a seq scan is faster than first looking it up in an index and then fetching the same page. set enable_seqscan=false; -- "Government big enough to supply everything you need is big enough to take everything you have ... the course of history shows that as a government grows, liberty decreases." -- Thomas Jefferson ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] EXPLAIN ANALYZE not considering primary and unique indices!
In response to Sanjay <[EMAIL PROTECTED]>: > Hi All, > > Say I have a simple table WEBSITE(website_id int4 PRIMARY KEY, name > VARCHAR(30)). While I try this: > > EXPLAIN ANALYZE SELECT * FROM WEBSITE WHERE website_id = 1 > > the output is: > -- > Seq Scan on website (cost=0.00..1.31 rows=1 width=162) (actual > time=0.047..0.051 rows=1 loops=1) > Filter: (website_id = 1) > Total runtime: 0.102 ms > --- > Wondering why it is not using the index, which would have been > automatically created for the primary key. Because PG thinks the seq scan is faster than an index scan. Depending on other factors, it may be right. If there's only a few rows in the table, then a seq scan is going to be faster than scanning the index, only to grab most of the table in to memory anyway. If it's wrong, it's either because your analyze data isn't up to date, or your tuning parameters don't match your hardware. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 1: 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
[GENERAL] EXPLAIN ANALYZE not considering primary and unique indices!
Hi All, Say I have a simple table WEBSITE(website_id int4 PRIMARY KEY, name VARCHAR(30)). While I try this: EXPLAIN ANALYZE SELECT * FROM WEBSITE WHERE website_id = 1 the output is: -- Seq Scan on website (cost=0.00..1.31 rows=1 width=162) (actual time=0.047..0.051 rows=1 loops=1) Filter: (website_id = 1) Total runtime: 0.102 ms --- Wondering why it is not using the index, which would have been automatically created for the primary key. Needing help. thanks Sanjay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] EXPLAIN ANALYZE not considering primary and unique indices!
How many rows are in this table? Sanjay wrote: Hi All, Say I have a simple table WEBSITE(website_id int4 PRIMARY KEY, name VARCHAR(30)). While I try this: EXPLAIN ANALYZE SELECT * FROM WEBSITE WHERE website_id = 1 the output is: -- Seq Scan on website (cost=0.00..1.31 rows=1 width=162) (actual time=0.047..0.051 rows=1 loops=1) Filter: (website_id = 1) Total runtime: 0.102 ms --- Wondering why it is not using the index, which would have been automatically created for the primary key. Needing help. thanks Sanjay ---(end of broadcast)--- TIP 6: explain analyze is your friend
