agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedIndex not being used
11+ messages / 7 participants
[nested] [flat]
* Index not being used
@ 2002-02-14 19:33 Matthew Price <pricem@juno.com>
0 siblings, 1 reply; 11+ messages in thread
From: Matthew Price @ 2002-02-14 19:33 UTC (permalink / raw)
To: pgsql-sql
Howdy,
I have a table like:
CREATE TABLE sometable (cdate date, ....);
and
CREATE INDEX sometable_cdate on sometable (cdate);
Question is, why does the following use the index
EXPLAIN SELECT cdate FROM sometable WHERE cdate = '02/14/2002';
but this does not
EXPLAIN SELECT cdate FROM sometable WHERE cdate = current_date;
I would like to make the SQL call dynamic and not have to make my code do the work.
Thanks for any help,
Matthew
________________________________________________________________
GET INTERNET ACCESS FROM JUNO!
Juno offers FREE or PREMIUM Internet access for less!
Join Juno today! For your FREE software, visit:
http://dl.www.juno.com/get/web/.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-14 21:38 Josh Berkus <josh@agliodbs.com>
parent: Matthew Price <pricem@juno.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2002-02-14 21:38 UTC (permalink / raw)
To: Matthew Price <pricem@juno.com>; pgsql-sql
Matthew:
Please post your PostgreSQL version, and the actual explain output of
both queries. If you are using Postgres < 7.1.2, I'll warn you that
the advice you get is likely to be "upgrade!"
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-14 22:04 Matthew Price <pricem@juno.com>
0 siblings, 1 reply; 11+ messages in thread
From: Matthew Price @ 2002-02-14 22:04 UTC (permalink / raw)
To: pgsql-sql
>Please post your PostgreSQL version, and the actual explain output of
>both queries.
EXPLAIN SELECT cdate FROM sometable WHERE cdate = current_date;
NOTICE: QUERY PLAN:
Seq Scan on sometable (cost=0.00..332.70 rows=71 width=186)
EXPLAIN SELECT cdate FROM sometable WHERE cdate = '02/14/2002';
NOTICE: QUERY PLAN:
Index Scan using sometable_cdate on sometable (cost=0.00..66.64 rows=71 width=186)
My postgres version is 7.1.3
Again, thanks for any help
Matthew Price
________________________________________________________________
GET INTERNET ACCESS FROM JUNO!
Juno offers FREE or PREMIUM Internet access for less!
Join Juno today! For your FREE software, visit:
http://dl.www.juno.com/get/web/.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-14 22:38 Josh Berkus <josh@agliodbs.com>
parent: Matthew Price <pricem@juno.com>
0 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2002-02-14 22:38 UTC (permalink / raw)
To: Matthew Price <pricem@juno.com>; pgsql-sql
Matthew,
1. Is field cdate DATE or TIMESTAMP?
2. What happens with:
EXPLAIN SELECT cdate FROM sometable WHERE cdate = '2001-02-14'::DATE or
TIMESTAMP?
3. Please time both queries and give the number of resulting rows from
both queries.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-14 23:08 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2002-02-14 23:08 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Matthew Price <pricem@juno.com>; pgsql-sql
On Thu, 14 Feb 2002, Josh Berkus wrote:
> Matthew,
>
> 1. Is field cdate DATE or TIMESTAMP?
> 2. What happens with:
> EXPLAIN SELECT cdate FROM sometable WHERE cdate = '2001-02-14'::DATE or
> TIMESTAMP?
> 3. Please time both queries and give the number of resulting rows from
> both queries.
Something tells me that current_date is probably not in a form that's
considered indexable. You could probably get around that with an
iscachable function hiding it however.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-15 00:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2002-02-15 00:46 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Matthew Price <pricem@juno.com>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Something tells me that current_date is probably not in a form that's
> considered indexable.
More to the point, it's not considered a constant.
You could write
WHERE cdate = date 'now'
instead.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-15 01:23 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Christopher Kings-Lynne @ 2002-02-15 01:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Matthew Price <pricem@juno.com>; pgsql-sql
> More to the point, it's not considered a constant.
Why? Shouldn't it be constant for the duration of a transaction?
> You could write
> WHERE cdate = date 'now'
> instead.
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-15 03:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2002-02-15 03:52 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Josh Berkus <josh@agliodbs.com>; Matthew Price <pricem@juno.com>; pgsql-sql
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> More to the point, it's not considered a constant.
> Why? Shouldn't it be constant for the duration of a transaction?
There is no such concept, at present. See the archives...
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Index not being used
@ 2002-02-15 04:06 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Christopher Kings-Lynne @ 2002-02-15 04:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Josh Berkus <josh@agliodbs.com>; Matthew Price <pricem@juno.com>; pgsql-sql
> There is no such concept, at present. See the archives...
Then why does this work?:
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:35+08
(1 row)
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:36+08
(1 row)
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:37+08
(1 row)
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:38+08
(1 row)
australia=# begin transaction;
BEGIN
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:43+08
(1 row)
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:43+08
(1 row)
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:43+08
(1 row)
australia=# select current_timestamp;
timestamp
------------------------
2002-02-15 12:03:43+08
(1 row)
^ permalink raw reply [nested|flat] 11+ messages in thread
* index not being used
@ 2023-08-12 01:15 lists-pgsql@useunix.net
0 siblings, 1 reply; 11+ messages in thread
From: lists-pgsql@useunix.net @ 2023-08-12 01:15 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
I'm running an older PostgreSQL 9.1 database. I know it's old... an
upgrade is planned.
I have a table with the following columns.
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+----------+-------------
sat | text | not null | extended |
ts | bigint | not null | plain |
apid | integer | not null | plain |
bin | integer | not null | plain |
value | bigint | not null | plain |
A unique index on (sat, ts, apid, bin).
There are only a handful of unique sat values but there are about 20
million rows in the table as there are many apid values per unit time.
This query is fast and uses the index:
select max(ts)
from table
where sat = 'XX';
While this query results in sequential scans and long execution times:
select sat, max(ts)
from histograms
where sat in ('A1', 'A2', 'S1', 'S2')
group by 1;
Is there any way to formulate this query to make it faster without
adding an additional index?
Thank you in advance,
Wayne
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index not being used
@ 2023-08-12 01:47 Erik Brandsberg <erik@heimdalldata.com>
parent: lists-pgsql@useunix.net
0 siblings, 0 replies; 11+ messages in thread
From: Erik Brandsberg @ 2023-08-12 01:47 UTC (permalink / raw)
To: lists-pgsql@useunix.net; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
If there are only a few values of sat, then a sequential scan may in fact
be the optimal path.
On Fri, Aug 11, 2023, 9:16 PM <lists-pgsql@useunix.net> wrote:
> I'm running an older PostgreSQL 9.1 database. I know it's old... an
> upgrade is planned.
>
> I have a table with the following columns.
>
> Column | Type | Modifiers | Storage | Description
> --------+---------+-----------+----------+-------------
> sat | text | not null | extended |
> ts | bigint | not null | plain |
> apid | integer | not null | plain |
> bin | integer | not null | plain |
> value | bigint | not null | plain |
>
> A unique index on (sat, ts, apid, bin).
>
> There are only a handful of unique sat values but there are about 20
> million rows in the table as there are many apid values per unit time.
>
> This query is fast and uses the index:
>
> select max(ts)
> from table
> where sat = 'XX';
>
> While this query results in sequential scans and long execution times:
>
> select sat, max(ts)
> from histograms
> where sat in ('A1', 'A2', 'S1', 'S2')
> group by 1;
>
> Is there any way to formulate this query to make it faster without
> adding an additional index?
>
> Thank you in advance,
> Wayne
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-08-12 01:47 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-14 19:33 Index not being used Matthew Price <pricem@juno.com>
2002-02-14 21:38 ` Josh Berkus <josh@agliodbs.com>
2002-02-14 22:04 Re: Index not being used Matthew Price <pricem@juno.com>
2002-02-14 22:38 ` Josh Berkus <josh@agliodbs.com>
2002-02-14 23:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-02-15 00:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-15 01:23 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-15 03:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-15 04:06 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2023-08-12 01:15 index not being used lists-pgsql@useunix.net
2023-08-12 01:47 ` Re: index not being used Erik Brandsberg <erik@heimdalldata.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox