Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread chris smith
> OK. Stop and think about what you're telling postgresql to do here. > > You're telling it to cast the field group_id to int8, then compare it to > 9. How can it cast the group_id to int8 without fetching it? That's > right, you're ensuring a seq scan. You need to put the int8 cast on the > ot

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Arnau
I have done the same tests on 8.1.0. espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM agenda_users_groups WHERE group_id = 9; QUERY PLAN

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Tom Lane
Arnau <[EMAIL PROTECTED]> writes: > I have done the same tests on 8.1.0. Bitmap scans are a totally different animal that doesn't exist in 7.4. A plain indexscan, such as 7.4 knows about, is generally not effective for fetching more than a percent or two of the table. The crossover point for a bi

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Scott Marlowe
On Tue, 2006-04-25 at 10:47, Arnau wrote: > Tom Lane wrote: > > Arnau <[EMAIL PROTECTED]> writes: > > > > > >>espsm_moviltelevision=# select count(*) from agenda_users_groups ; > >> count > >>- > >> 2547556 > > > > > > So the SELECT is fetching nearly 15% of the rows in the table. T

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Arnau
Tom Lane wrote: Arnau <[EMAIL PROTECTED]> writes: Seq Scan on agenda_users_groups (cost=0.00..53108.45 rows=339675 width=8) (actual time=916.903..5763.830 rows=367026 loops=1) Filter: (group_id = 9::numeric) Total runtime: 7259.861 ms (3 filas) espsm_moviltelevision=# select count(*

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Tom Lane
Arnau <[EMAIL PROTECTED]> writes: > Seq Scan on agenda_users_groups (cost=0.00..53108.45 rows=339675 > width=8) (actual time=916.903..5763.830 rows=367026 loops=1) > Filter: (group_id = 9::numeric) > Total runtime: 7259.861 ms > (3 filas) > espsm_moviltelevision=# select count(*) from a

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Scott Marlowe
On Tue, 2006-04-25 at 08:49, Arnau wrote: > chris smith wrote: > > On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote: > > > >>Hi all, > >> > >> I have the following running on postgresql version 7.4.2: > >> > >>CREATE SEQUENCE agenda_user_group_id_seq > >>MINVALUE 1 > >>MAXVALUE 9223372036854775807 >

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Guillaume Smet
On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote: > espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM > agenda_users_groups > espsm_moviltelevision-# WHERE group_id = '9'; > QUERY PLAN > --

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Arnau
chris smith wrote: On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote: Hi all, I have the following running on postgresql version 7.4.2: CREATE SEQUENCE agenda_user_group_id_seq MINVALUE 1 MAXVALUE 9223372036854775807 CYCLE INCREMENT 1 START 1; CREATE TABLE AGENDA_USERS_GROUPS ( AGENDA_USER_GROU

Re: [PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread chris smith
On 4/25/06, Arnau <[EMAIL PROTECTED]> wrote: > Hi all, > >I have the following running on postgresql version 7.4.2: > > CREATE SEQUENCE agenda_user_group_id_seq > MINVALUE 1 > MAXVALUE 9223372036854775807 > CYCLE > INCREMENT 1 > START 1; > > CREATE TABLE AGENDA_USERS_GROUPS > ( > AGENDA_USER_

[PERFORM] Query on postgresql 7.4.2 not using index

2006-04-25 Thread Arnau
Hi all, I have the following running on postgresql version 7.4.2: CREATE SEQUENCE agenda_user_group_id_seq MINVALUE 1 MAXVALUE 9223372036854775807 CYCLE INCREMENT 1 START 1; CREATE TABLE AGENDA_USERS_GROUPS ( AGENDA_USER_GROUP_ID INT8 CONSTRAINT pk_agndusrgrp_usergrou