Re: [PERFORM] select count(*) from anIntColumn where int_value = 0; is very slow
Hi we have a table with about 4 million rows. One column has an int value, there is a btree index on it. We tried to execute the following statement and it is very slow on a dual G5 2GHZ with 4 GB of RAM. explain analyze select count(*) from job_property where int_value = 0; Aggregate (cost=144348.80..144348.80 rows=1 width=0) (actual time=13536.852..13536.852 rows=1 loops=1) - Seq Scan on job_property (cost=0.00..144255.15 rows=37459 width=0) (actual time=19.422..13511.653 rows=42115 loops=1) Filter: (int_value = 0) Total runtime: 13560.862 ms Is your int_value data type int4? If not then use ... from job_property where int_value = '0' Indexes are used only if datatypes matches. Rigmor Ukuhe Is this more or less normal or can we optimize this a little bit? FrontBase (which we compare currently) takes 2 seconds first time and about 0.2 seconds on second+ queries. regards David ---(end of broadcast)--- TIP 6: Have you searched our list archives? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] select count(*) from anIntColumn where int_value = 0; is very slow
Hi, Is your int_value data type int4? If not then use ... from job_property where int_value = '0' Indexes are used only if datatypes matches. tried those variations already. Strange enough, after dropping and recreating the index everything worked fine. regards David ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] select count(*) from anIntColumn where int_value = 0; is very slow
Had you done a VACUUM ANALYZE at all? There has been much discussion lately about the planner needing to be updated to know that the index is a better choice. On Feb 11, 2004, at 6:32 AM, David Teran wrote: Hi, Is your int_value data type int4? If not then use ... from job_property where int_value = '0' Indexes are used only if datatypes matches. tried those variations already. Strange enough, after dropping and recreating the index everything worked fine. regards David ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- PC Drew Manager, Dominet IBSN 1600 Broadway, Suite 400 Denver, CO 80202 Phone: 303-984-4727 x107 Cell: 720-841-4543 Fax: 303-984-4730 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
