Re: [GENERAL] Index not used when using expression

Dingyuan Wang writes: > I have a table named "gps", with an indexed column "packettime", which > has unix timestamps. > The following query: > select * from gps where packettime < extract(epoch from '2017-05-01 > 08:00+08'::timestamp with time zone) > explains to: > Seq Scan

[GENERAL] Index not used when using expression

Hi, I have a table named "gps", with an indexed column "packettime", which has unix timestamps. The following query: select * from gps where packettime < extract(epoch from '2017-05-01 08:00+08'::timestamp with time zone) explains to: Seq Scan on gps (cost=0.00..43411860.64 rows=384325803

[GENERAL] Index not used when using a function

SELECT * FROM locations WHERE id = 12345 LIMIT 1 uses the primary key (id) index, but... SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892) LIMIT 1 does not and is verrry slow. Any ideas why? Whats weird is that it works (uses index) on a previous db, but when I copied

Re: [GENERAL] Index not used when using a function

Nick nboutel...@gmail.com writes: SELECT * FROM locations WHERE id = 12345 LIMIT 1 uses the primary key (id) index, but... SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892) LIMIT 1 does not and is verrry slow. Any ideas why? You didn't mark the function stable or

Re: [GENERAL] Index not used when using a function

On Jan 13, 4:21 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: Nick nboutel...@gmail.com writes: SELECT * FROM locations WHERE id = 12345 LIMIT 1 uses the primary key (id) index, but... SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892) LIMIT 1 does not and is verrry slow.

[GENERAL] index not used in joins

Hello all, i have a problem with index usage and joins. Attached is some SQL demonstrating my problem; Why is the index only used in the 2nd query? Can anybody explain me how to avoid/fix this. Thanks in advance Sebastian CREATE TABLE users ( login NAME NOT NULL PRIMARY KEY, datum

Re: [GENERAL] index not used in joins

Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). EXPLAIN ANALYZE SELECT * FROM v;

Re: [GENERAL] index not used in joins

Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). [output of EXPLAIN ANALYZE] OK - so what

Re: [GENERAL] index not used in joins

Sebastian Böck wrote: Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). [output of

Re: [GENERAL] index not used in joins

Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list

Re: [GENERAL] index not used?

Scott Marlowe wrote: On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote: I'm trying to access a table with about 120M rows. It's a vertical version of a table with 360 or so columns. The new columns are: original item col, original item row, and the value. I created an index: CREATE INDEX idx on table

[GENERAL] index not used?

I'm trying to access a table with about 120M rows. It's a vertical version of a table with 360 or so columns. The new columns are: original item col, original item row, and the value. I created an index: CREATE INDEX idx on table (col, row) however, selects are still very slow. It seems it

Re: [GENERAL] index not used?

On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote: I'm trying to access a table with about 120M rows. It's a vertical version of a table with 360 or so columns. The new columns are: original item col, original item row, and the value. I created an index: CREATE INDEX idx on table (col, row)

Re: [GENERAL] index not used?

Scott Marlowe writes: On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote: I'm trying to access a table with about 120M rows. It's a vertical version of a table with 360 or so columns. The new columns are: original item col, original item row, and the value. I created an index:

Re: [GENERAL] index not used?

On Wed, 2004-10-20 at 09:45, Dan Pelleg wrote: Scott Marlowe writes: On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote: I'm trying to access a table with about 120M rows. It's a vertical version of a table with 360 or so columns. The new columns are: original item col, original item

Re: [GENERAL] index not used?

Scott Marlowe writes: On Wed, 2004-10-20 at 09:45, Dan Pelleg wrote: Scott Marlowe writes: On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote: I'm trying to access a table with about 120M rows. It's a vertical version of a table with 360 or so columns. The new columns are:

Re: [GENERAL] index not used with subselect in where clause ?

Christian Fritze [EMAIL PROTECTED] writes: explain select * from allmain where exists (select distinct dokids_as_int from allslwfull where dokids_as_int = idn and wort_nouml_lower like 'gen%') Try dropping the "distinct" on the inner select. As a moment's thought will reveal, it's

Re: [GENERAL] index not used with subselect in where clause ?

, the truth, and what really happened. - Original Message - From: "Tom Lane" [EMAIL PROTECTED] To: "Christian Fritze" [EMAIL PROTECTED] Cc: "Stephan Szabo" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, April 17, 2001 10:33 AM Subject: Re: [GENERAL] index no

Re: [GENERAL] index not used with subselect in where clause ?

"Rod Taylor" [EMAIL PROTECTED] writes: Adding a LIMIT 1 in the subplan may also help -- as you only need a single match to make it true so additional finds are useless -- it'll stop sooner or will be more likely to use an index than a full table scan. I believe this is not necessary; the

Re: [GENERAL] index not used with subselect in where clause ?

Adding a LIMIT 1 in the subplan may also help -- as you only need a single match to make it true so additional finds are useless -- it'll stop sooner or will be more likely to use an index than a full table scan. -- Rod Taylor I'm not sure if I understand you correctly here: the subplan