pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
bigint index not used 2+ messages / 2 participants [nested] [flat]
* bigint index not used @ 2004-03-24 12:11 Shiar <shiar@shiar.org> 0 siblings, 1 reply; 2+ messages in thread From: Shiar @ 2004-03-24 12:11 UTC (permalink / raw) To: pgsql-performance We've got a table containing userdata, such as a bigint column 'icq'. To easily check whether a user has an icq number entered, we made the following index: userinfo_icq_ne0_id_key btree (id) WHERE (icq <> 0::bigint), However, it doesn't seem to be used: > EXPLAIN ANALYZE SELECT id FROM userinfo WHERE icq <> '0'; Seq Scan on userinfo (cost=0.00..47355.90 rows=849244 width=4) (actual time=0.563..1222.963 rows=48797 loops=1) Filter: (icq <> 0::bigint) Total runtime: 1258.703 ms > SET enable_seqscan TO off; > EXPLAIN ANALYZE SELECT id FROM userinfo WHERE icq <> '0'; Index Scan using userinfo_icq_ne0_id_key on userinfo (cost=0.00..65341.34 rows=48801 width=4) (actual time=0.124..256.478 rows=48797 loops=1) Filter: (icq <> 0::bigint) Total runtime: 290.804 ms It would even rather use much larger indexes, for example the integer pics with index: userinfo_pics_gt0_id_key btree (id) WHERE (pics > 0), > EXPLAIN ANALYZE SELECT id FROM userinfo WHERE icq <> '0' AND pics > 0; Index Scan using userinfo_pics_gt0_id_key on userinfo (cost=0.00..60249.29 rows=323478 width=4) (actual time=0.039..1349.590 rows=23500 loops=1) Filter: ((icq <> 0::bigint) AND (pics > 0)) Total runtime: 1368.227 ms We're running PostgreSQL 7.4.1 on a Debian/Linux 2.4 system with 4GB RAM and a fast SCSI RAID array, with settings: shared_buffers = 65536 # min max_connections*2 or 16, 8KB each sort_mem = 16384 # min 64, size in KB effective_cache_size = 327680 # typically 8KB each random_page_cost = 1.5 # 4 # units are one sequential page fetch cost -- Shiar - http://www.shiar.org > Mi devas forfughi antau fluganta nubskrapulo alterighos sur mia kapo ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: bigint index not used @ 2004-03-26 23:15 Tom Lane <tgl@sss.pgh.pa.us> parent: Shiar <shiar@shiar.org> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2004-03-26 23:15 UTC (permalink / raw) To: Shiar <shiar@shiar.org>; +Cc: pgsql-performance Shiar <shiar@shiar.org> writes: >> EXPLAIN ANALYZE SELECT id FROM userinfo WHERE icq <> '0'; > Seq Scan on userinfo (cost=0.00..47355.90 rows=849244 width=4) (actual time=0.563..1222.963 rows=48797 loops=1) > Filter: (icq <> 0::bigint) > Total runtime: 1258.703 ms The rows estimate is way off, which might or might not have much to do with the issue, but it's surely suspicious. > We're running PostgreSQL 7.4.1 on a Debian/Linux 2.4 system with 4GB RAM and a > fast SCSI RAID array, with settings: Update to 7.4.2 and follow the procedure in the release notes about fixing pg_statistic; that may make things better. int8 columns are vulnerable to the statistic misalignment bug. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-03-26 23:15 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-03-24 12:11 bigint index not used Shiar <shiar@shiar.org> 2004-03-26 23:15 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox