pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex usage
34+ messages / 16 participants
[nested] [flat]
* index usage
@ 2003-01-17 11:48 Timur Irmatov <thor@sarkor.com>
2003-01-17 14:57 ` Re: index usage Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 34+ messages in thread
From: Timur Irmatov @ 2003-01-17 11:48 UTC (permalink / raw)
To: pgsql-performance
Hi, everyone!
I have a simple query which takes almost 3 seconds to complete, but
disabling sequence scans leads to a new plan using index. This second
plan takes less than 1 millisecond to run.
So, I'd like to hear any comments and suggestions.
Details.
CREATE TABLE MediumStats (
year SMALLINT NOT NULL,
month SMALLINT NOT NULL,
day SMALLINT NOT NULL,
hour SMALLINT NOT NULL,
--- and then goes few data fields
figureId INTEGER NOT NULL,
typeId INTEGER NOT NULL
PRIMARY KEY (figureId, typeId, year, month, day, hour)
);
CREATE FUNCTION indexHelper (INT2, INT2, INT2, INT2)
RETURNS CHARACTER(10) AS '
return sprintf("%d%02d%02d%02d", @_);
' LANGUAGE 'plperl' WITH (isCachable);
CREATE INDEX timeIndex ON MediumStats (indexHelper(year,month,day,hour));
and that is the query:
SELECT * FROM MediumStats
WHERE indexHelper(year,month,day,hour) < '2002121500'
LIMIT 1;
First, original plan:
Limit (cost=0.00..0.09 rows=1 width=22) (actual time=2969.30..2969.30 rows=0 loops=1)
-> Seq Scan on mediumstats (cost=0.00..1332.33 rows=15185 width=22) (actual time=2969.29..2969.29 rows=0 loops=1)
Total runtime: 2969.39 msec
Second plan, seq scans disabled:
Limit (cost=0.00..0.19 rows=1 width=6) (actual time=0.43..0.43 rows=0 loops=1)
-> Index Scan using timeindex on mediumstats (cost=0.00..2898.96 rows=15185 width=6) (actual time=0.42..0.42 rows=0 loops=1)
Total runtime: 0.54 msec
Table MediumStats currently has 45000 rows, all rows belong to this
month.
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2003-01-17 11:48 index usage Timur Irmatov <thor@sarkor.com>
@ 2003-01-17 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-17 15:08 ` Re: index usage Timur Irmatov <thor@sarkor.com>
0 siblings, 1 reply; 34+ messages in thread
From: Tom Lane @ 2003-01-17 14:57 UTC (permalink / raw)
To: Timur Irmatov <thor@sarkor.com>; +Cc: pgsql-performance
Timur Irmatov <thor@sarkor.com> writes:
> Limit (cost=0.00..0.19 rows=1 width=6) (actual time=0.43..0.43 rows=0 loops=1)
> -> Index Scan using timeindex on mediumstats (cost=0.00..2898.96 rows=15185 width=6) (actual time=0.42..0.42 rows=0 loops=1)
The planner has absolutely no clue about the behavior of your function,
and so its estimate of the number of rows matched is way off, leading to
a poor estimate of the cost of an indexscan. There is not much to be
done about this in the current system (though I've speculated about the
possibility of computing statistics for functional indexes).
Just out of curiosity, why don't you lose all this year/month/day stuff
and use a timestamp column? Less space, more functionality.
regards, tom lane
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2003-01-17 11:48 index usage Timur Irmatov <thor@sarkor.com>
2003-01-17 14:57 ` Re: index usage Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-17 15:08 ` Timur Irmatov <thor@sarkor.com>
2003-01-17 15:33 ` Re: index usage Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 1 reply; 34+ messages in thread
From: Timur Irmatov @ 2003-01-17 15:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
TL> Timur Irmatov <thor@sarkor.com> writes:
>> Limit (cost=0.00..0.19 rows=1 width=6) (actual time=0.43..0.43 rows=0 loops=1)
>> -> Index Scan using timeindex on mediumstats (cost=0.00..2898.96 rows=15185 width=6) (actual time=0.42..0.42 rows=0 loops=1)
TL> The planner has absolutely no clue about the behavior of your function,
TL> and so its estimate of the number of rows matched is way off, leading to
TL> a poor estimate of the cost of an indexscan. There is not much to be
TL> done about this in the current system (though I've speculated about the
TL> possibility of computing statistics for functional indexes).
you're absolutely right.
thanks.
TL> Just out of curiosity, why don't you lose all this year/month/day stuff
TL> and use a timestamp column? Less space, more functionality.
:-)
Well, I've a seen a lot of people on pgsql-general mailing list with
problems with dates, timestamps, and I was just scared of using
PostreSQL date and time types and functions..
May be, I should just try it myself before doing it other way...
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2003-01-17 11:48 index usage Timur Irmatov <thor@sarkor.com>
2003-01-17 14:57 ` Re: index usage Tom Lane <tgl@sss.pgh.pa.us>
2003-01-17 15:08 ` Re: index usage Timur Irmatov <thor@sarkor.com>
@ 2003-01-17 15:33 ` Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 0 replies; 34+ messages in thread
From: Andrew Sullivan @ 2003-01-17 15:33 UTC (permalink / raw)
To: pgsql-performance
On Fri, Jan 17, 2003 at 08:08:14PM +0500, Timur Irmatov wrote:
> Well, I've a seen a lot of people on pgsql-general mailing list with
> problems with dates, timestamps, and I was just scared of using
> PostreSQL date and time types and functions..
What problems? The only problems I know of with datetime stuff are
on those machines with the utterly silly glibc hobbling, and even
that has been worked around in recent releases. I think the date and
time handling in PostgreSQL beats most systems. It just works, and
handles all the time-zone conversions for you and everything.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 34+ messages in thread
* index usage
@ 2004-04-23 22:21 brad-pgperf@duttonbros.com
2004-04-26 18:58 ` Re: index usage Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 34+ messages in thread
From: brad-pgperf@duttonbros.com @ 2004-04-23 22:21 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a query which I think should be using an index all of the time but
postgres only uses the index part of the time. The index
(ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
first followed by the selected column (support_person_id). Wouldn't the
most efficient plan be to scan the index each time because the only columns
needed are in the index? Below is the table, 2 queries showing the
difference in plans, followed by the record distribution of ticket_crm_map.
I first did a 'vacuum analyze' to update the statistics.
Thanks,
Brad
athenapost=> \d ticket_crm_map
Table "public.ticket_crm_map"
Column | Type |
Modifiers
------------------------+-----------------------------+---------------------
-----------------------
tcrm_map_id | integer | not null
ticket_id | integer | not null
crm_id | integer | not null
support_person_id | integer | not null
escalated_to_person_id | integer | not null
status | character varying(50) | not null default
'Open'::character varying
close_date | timestamp without time zone |
updated_date | timestamp without time zone |
updated_by | character varying(255) |
created_date | timestamp without time zone |
created_by | character varying(255) |
additional_info | text |
subject | character varying(255) |
Indexes:
"ticket_crm_map_pkey" primary key, btree (tcrm_map_id)
"ticket_crm_map_crm_id_key" unique, btree (crm_id, ticket_id)
"ticket_crm_map_crm_id_suppid" btree (crm_id, support_person_id)
"ticket_crm_map_status" btree (status)
"ticket_crm_map_ticket_id" btree (ticket_id)
Foreign-key constraints:
"$1" FOREIGN KEY (ticket_id) REFERENCES ticket(ticket_id)
"$2" FOREIGN KEY (crm_id) REFERENCES company_crm(crm_id)
"$3" FOREIGN KEY (support_person_id) REFERENCES person(person_id)
"$4" FOREIGN KEY (escalated_to_person_id) REFERENCES person(person_id)
"$5" FOREIGN KEY (status) REFERENCES ticket_status(status)
athenapost=> explain analyze select distinct support_person_id from
ticket_crm_map where crm_id = 7;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------
Unique (cost=1262.99..1265.27 rows=1 width=4) (actual time=15.335..18.245
rows=20 loops=1)
-> Sort (cost=1262.99..1264.13 rows=456 width=4) (actual
time=15.332..16.605 rows=2275 loops=1)
Sort Key: support_person_id
-> Index Scan using ticket_crm_map_crm_id_suppid on ticket_crm_map
(cost=0.00..1242.85 rows=456 width=4) (actual time=0.055..11.281 rows=2275
loops=1)
Index Cond: (crm_id = 7)
Total runtime: 18.553 ms
(6 rows)
Time: 20.598 ms
athenapost=> explain analyze select distinct support_person_id from
ticket_crm_map where crm_id = 1;
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------------------
Unique (cost=10911.12..11349.26 rows=32 width=4) (actual
time=659.102..791.517 rows=24 loops=1)
-> Sort (cost=10911.12..11130.19 rows=87628 width=4) (actual
time=659.090..713.285 rows=93889 loops=1)
Sort Key: support_person_id
-> Seq Scan on ticket_crm_map (cost=0.00..3717.25 rows=87628
width=4) (actual time=0.027..359.299 rows=93889 loops=1)
Filter: (crm_id = 1)
Total runtime: 814.601 ms
(6 rows)
Time: 817.095 ms
athenapost=> select count(*), crm_id from ticket_crm_map group by crm_id;
count | crm_id
-------+--------
2554 | 63
129 | 25
17 | 24
110 | 23
74 | 22
69 | 21
2 | 20
53 | 82
10 | 17
16 | 81
46637 | 16
14 | 80
2 | 15
1062 | 79
87 | 78
93 | 77
60 | 44
363 | 76
225 | 10
4 | 74
83 | 9
27 | 73
182 | 8
2275 | 7
15 | 71
554 | 6
44 | 70
631 | 5
37 | 4
190 | 3
112 | 2
93889 | 1
(32 rows)
Time: 436.697 ms
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2004-04-23 22:21 index usage brad-pgperf@duttonbros.com
@ 2004-04-26 18:58 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-26 19:16 ` Re: index usage brad-pgperf@duttonbros.com
2004-04-28 15:40 ` Re: index usage scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 2 replies; 34+ messages in thread
From: Stephan Szabo @ 2004-04-26 18:58 UTC (permalink / raw)
To: brad-pgperf@duttonbros.com; +Cc: pgsql-performance
On Fri, 23 Apr 2004 brad-pgperf@duttonbros.com wrote:
> I have a query which I think should be using an index all of the time but
> postgres only uses the index part of the time. The index
> (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
> first followed by the selected column (support_person_id). Wouldn't the
> most efficient plan be to scan the index each time because the only columns
> needed are in the index? Below is the table, 2 queries showing the
Not necessarily. The rows in the actual file still need to be checked to
see if they're visible to the select and if it's expected that the entire
file (or a reasonable % of the pages anyway) will need to be loaded using
the index isn't necessarily a win.
> athenapost=> explain analyze select distinct support_person_id from
> ticket_crm_map where crm_id = 1;
> QUERY PLAN
> ----------------------------------------------------------------------------
> -----------------------------------------------------
> Unique (cost=10911.12..11349.26 rows=32 width=4) (actual
> time=659.102..791.517 rows=24 loops=1)
> -> Sort (cost=10911.12..11130.19 rows=87628 width=4) (actual
> time=659.090..713.285 rows=93889 loops=1)
> Sort Key: support_person_id
> -> Seq Scan on ticket_crm_map (cost=0.00..3717.25 rows=87628
> width=4) (actual time=0.027..359.299 rows=93889 loops=1)
> Filter: (crm_id = 1)
> Total runtime: 814.601 ms
How far off is this from the index scan version in time? Try doing
set enable_seqscan=off; and then explain analyzing again.
It's possible that you may wish to lower random_page_cost to change the
estimated effect of how much more expensive random reads are compared to
sequential ones.
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2004-04-23 22:21 index usage brad-pgperf@duttonbros.com
2004-04-26 18:58 ` Re: index usage Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-04-26 19:16 ` brad-pgperf@duttonbros.com
1 sibling, 0 replies; 34+ messages in thread
From: brad-pgperf@duttonbros.com @ 2004-04-26 19:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-performance
When checking an index in postgres the original table has to be checked for
each result to find if the index entry is still valid? In which case you
can't blindly scan the whole index and assume the data is good. I was used
to Oracle behavior where the index is up to date so it can do the scan
without hitting the original table.
Does this sound correct to anyone?
Thanks,
Brad
Stephan Szabo writes:
> On Fri, 23 Apr 2004 brad-pgperf@duttonbros.com wrote:
>
>> I have a query which I think should be using an index all of the time but
>> postgres only uses the index part of the time. The index
>> (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
>> first followed by the selected column (support_person_id). Wouldn't the
>> most efficient plan be to scan the index each time because the only columns
>> needed are in the index? Below is the table, 2 queries showing the
>
> Not necessarily. The rows in the actual file still need to be checked to
> see if they're visible to the select and if it's expected that the entire
> file (or a reasonable % of the pages anyway) will need to be loaded using
> the index isn't necessarily a win.
>
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2004-04-23 22:21 index usage brad-pgperf@duttonbros.com
2004-04-26 18:58 ` Re: index usage Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-04-28 15:40 ` scott.marlowe <scott.marlowe@ihs.com>
2004-04-28 16:41 ` Re: index usage Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 34+ messages in thread
From: scott.marlowe @ 2004-04-28 15:40 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: brad-pgperf@duttonbros.com; pgsql-performance
On Mon, 26 Apr 2004, Stephan Szabo wrote:
>
> On Fri, 23 Apr 2004 brad-pgperf@duttonbros.com wrote:
>
> > I have a query which I think should be using an index all of the time but
> > postgres only uses the index part of the time. The index
> > (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
> > first followed by the selected column (support_person_id). Wouldn't the
> > most efficient plan be to scan the index each time because the only columns
> > needed are in the index? Below is the table, 2 queries showing the
>
> Not necessarily. The rows in the actual file still need to be checked to
> see if they're visible to the select and if it's expected that the entire
> file (or a reasonable % of the pages anyway) will need to be loaded using
> the index isn't necessarily a win.
While those of us familiar with PostgreSQL are well aware of the fact that
indexes can't be used directly to garner information, but only as a lookup
to a tuple in the table, it seems this misconception is quite common among
those coming to postgreSQL from other databases.
Is there any information that directly reflects this issue in the docs?
There are tons of hints that it works this way in how they're written, but
nothing that just comes out and says that with pgsql's mvcc
implementation, an index scan still has to hit the pages that contain the
tuples, so often in pgsql a seq scan is a win where in other databases and
index scan would have been a win?
If not, where would I add it if I were going to write something up for the
docs? Just wondering...
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2004-04-23 22:21 index usage brad-pgperf@duttonbros.com
2004-04-26 18:58 ` Re: index usage Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-28 15:40 ` Re: index usage scott.marlowe <scott.marlowe@ihs.com>
@ 2004-04-28 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 34+ messages in thread
From: Tom Lane @ 2004-04-28 16:41 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; brad-pgperf@duttonbros.com; pgsql-performance
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> There are tons of hints that it works this way in how they're written, but
> nothing that just comes out and says that with pgsql's mvcc
> implementation, an index scan still has to hit the pages that contain the
> tuples, so often in pgsql a seq scan is a win where in other databases and
> index scan would have been a win?
> If not, where would I add it if I were going to write something up for the
> docs? Just wondering...
AFAIR the only place in the docs that mentions seqscan or indexscan at
all is the discussion of EXPLAIN in "Performance Tips". Perhaps a
suitably-enlarged version of that section could cover this.
regards, tom lane
^ permalink raw reply [nested|flat] 34+ messages in thread
* FW: Index usage
@ 2004-11-24 06:52 BBI Edwin Punzalan <edwin@bluebamboo.ph>
0 siblings, 0 replies; 34+ messages in thread
From: BBI Edwin Punzalan @ 2004-11-24 06:52 UTC (permalink / raw)
To: pgsql-performance
Hi everyone,
Can anyone please explain postgres' behavior on our index.
I did the following query tests on our database:
====================
db=# create index chatlogs_date_idx on chatlogs (date);
CREATE
db=# explain select date from chatlogs where date>='11/23/04';
NOTICE: QUERY PLAN:
Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..144.11 rows=36
width=4)
EXPLAIN
db=# explain select date from chatlogs where date>='10/23/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..23938.06 rows=253442 width=4)
EXPLAIN====================
Date's datatype is date. Its just odd that I just change the actual date of
search and the index is not being used anymore.
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
@ 2004-11-24 07:34 Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
0 siblings, 0 replies; 34+ messages in thread
From: Leeuw van der, Tim @ 2004-11-24 07:34 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; pgsql-performance
Well you just selected a whole lot more rows... What's the total number of rows in the table?
In general, what I remember from reading on the list, is that when there's no upper bound on a query like this, the planner is more likely to choose a seq. scan than an index scan.
Try to give your query an upper bound like:
select date from chatlogs where date>='11/23/04' and date < '12/31/99';
select date from chatlogs where date>='10/23/04' and date < '12/31/99';
This should make it easier for the planner to give a proper estimate of the number of rows returned. If it doesn't help yet, please post 'explain analyze' output rather than 'explain' output, for it allows much better investigation into why the planner chooses what it chooses.
cheers,
--Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of BBI Edwin Punzalan
Sent: Wednesday, November 24, 2004 7:52 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] FW: Index usage
Hi everyone,
Can anyone please explain postgres' behavior on our index.
I did the following query tests on our database:
====================
db=# create index chatlogs_date_idx on chatlogs (date);
CREATE
db=# explain select date from chatlogs where date>='11/23/04';
NOTICE: QUERY PLAN:
Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..144.11 rows=36
width=4)
EXPLAIN
db=# explain select date from chatlogs where date>='10/23/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..23938.06 rows=253442 width=4)
EXPLAIN====================
Date's datatype is date. Its just odd that I just change the actual date of
search and the index is not being used anymore.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 34+ messages in thread
* FW: FW: Index usage
@ 2004-11-24 08:07 BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 10:16 ` Re: FW: FW: Index usage Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 34+ messages in thread
From: BBI Edwin Punzalan @ 2004-11-24 08:07 UTC (permalink / raw)
To: pgsql-performance
Thanks, Tim.
I tried adding an upper limit and its still the same as follows:
==============
db=# explain analyze select date from chatlogs where date>='11/24/04';
NOTICE: QUERY PLAN:
Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72 rows=37
width=4) (actual time=0.18..239.69 rows=10737 loops=1)
Total runtime: 246.22 msec
EXPLAIN
db=# explain analyze select date from chatlogs where date>='11/23/04' and
date<'11/24/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual
time=0.44..4447.01 rows=13029 loops=1)
Total runtime: 4455.56 msec
EXPLAIN
db=# explain analyze select date from chatlogs where date>='11/23/04' and
date<'11/25/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual
time=0.45..4268.00 rows=23787 loops=1)
Total runtime: 4282.81 msec
==============
How come a query on the current date filter uses an index and the others
does not? This makes indexing to speed up queries quite difficult.
-----Original Message-----
From: Leeuw van der, Tim [mailto:tim.leeuwvander@nl.unisys.com]
Sent: Wednesday, November 24, 2004 3:35 PM
To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] FW: Index usage
Well you just selected a whole lot more rows... What's the total number of
rows in the table?
In general, what I remember from reading on the list, is that when there's
no upper bound on a query like this, the planner is more likely to choose a
seq. scan than an index scan. Try to give your query an upper bound like:
select date from chatlogs where date>='11/23/04' and date < '12/31/99';
select date from chatlogs where date>='10/23/04' and date < '12/31/99';
This should make it easier for the planner to give a proper estimate of the
number of rows returned. If it doesn't help yet, please post 'explain
analyze' output rather than 'explain' output, for it allows much better
investigation into why the planner chooses what it chooses.
cheers,
--Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of BBI Edwin
Punzalan
Sent: Wednesday, November 24, 2004 7:52 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] FW: Index usage
Hi everyone,
Can anyone please explain postgres' behavior on our index.
I did the following query tests on our database:
====================
db=# create index chatlogs_date_idx on chatlogs (date);
CREATE
db=# explain select date from chatlogs where date>='11/23/04';
NOTICE: QUERY PLAN:
Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..144.11 rows=36
width=4)
EXPLAIN
db=# explain select date from chatlogs where date>='10/23/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..23938.06 rows=253442 width=4)
EXPLAIN====================
Date's datatype is date. Its just odd that I just change the actual date of
search and the index is not being used anymore.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-11-24 08:34 ` gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
1 sibling, 1 reply; 34+ messages in thread
From: gnari @ 2004-11-24 08:34 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; pgsql-performance
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
> db=# explain analyze select date from chatlogs where date>='11/23/04' and
> date<'11/25/04';
> NOTICE: QUERY PLAN:
>
> Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual
> time=0.45..4268.00 rows=23787 loops=1)
> Total runtime: 4282.81 msec
> ==============
>
> How come a query on the current date filter uses an index and the others
> does not? This makes indexing to speed up queries quite difficult.
have you ANALYZED the table lately ?
what version postgres are you using ?
gnari
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
@ 2004-11-24 09:43 ` BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
0 siblings, 1 reply; 34+ messages in thread
From: BBI Edwin Punzalan @ 2004-11-24 09:43 UTC (permalink / raw)
To: 'gnari' <gnari@simnet.is>; pgsql-performance
Yes, the database is being vacuum-ed and analyzed on a daily basis.
Our version is 7.2.1
-----Original Message-----
From: gnari [mailto:gnari@simnet.is]
Sent: Wednesday, November 24, 2004 4:35 PM
To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] FW: Index usage
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
> db=# explain analyze select date from chatlogs where date>='11/23/04'
> and date<'11/25/04';
> NOTICE: QUERY PLAN:
>
> Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual
> time=0.45..4268.00 rows=23787 loops=1) Total runtime: 4282.81 msec
> ==============
>
> How come a query on the current date filter uses an index and the
> others does not? This makes indexing to speed up queries quite
> difficult.
have you ANALYZED the table lately ?
what version postgres are you using ?
gnari
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-11-24 19:12 ` gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
0 siblings, 1 reply; 34+ messages in thread
From: gnari @ 2004-11-24 19:12 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; pgsql-performance
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>
> Yes, the database is being vacuum-ed and analyzed on a daily basis.
>
then you should consider increating the statistics on the date column,
as the estimates were a bit off in the plan
> Our version is 7.2.1
upgrade time ?
gnari
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
@ 2004-12-01 01:50 ` BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 02:07 ` Re: FW: Index usage gnari <gnari@simnet.is>
0 siblings, 1 reply; 34+ messages in thread
From: BBI Edwin Punzalan @ 2004-12-01 01:50 UTC (permalink / raw)
To: 'gnari' <gnari@simnet.is>; pgsql-performance
Hi, what do you mean by increasing the statistics on the date column?
We never had any upgrade on it.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of gnari
Sent: Thursday, November 25, 2004 3:13 AM
To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] FW: Index usage
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>
> Yes, the database is being vacuum-ed and analyzed on a daily basis.
>
then you should consider increating the statistics on the date column, as
the estimates were a bit off in the plan
> Our version is 7.2.1
upgrade time ?
gnari
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-12-01 02:07 ` gnari <gnari@simnet.is>
2004-12-01 02:33 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
0 siblings, 1 reply; 34+ messages in thread
From: gnari @ 2004-12-01 02:07 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; pgsql-performance
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>
> Hi, what do you mean by increasing the statistics on the date column?
alter table chatlogs alter column date set statistics 300;
analyze chatlogs;
> > > Our version is 7.2.1
> >
> > upgrade time ?
>
> We never had any upgrade on it.
7.2 is a bit dated now that 8.0 is in beta
if you want to stay with 7.2, you should at least upgrade
to the latest point release (7.2.6 ?), as several serious bugs
have been fixed
gnari
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 02:07 ` Re: FW: Index usage gnari <gnari@simnet.is>
@ 2004-12-01 02:33 ` BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 04:00 ` Re: FW: Index usage Iain <iain@mst.co.jp>
2004-12-01 08:24 ` Re: FW: Index usage gnari <gnari@simnet.is>
0 siblings, 2 replies; 34+ messages in thread
From: BBI Edwin Punzalan @ 2004-12-01 02:33 UTC (permalink / raw)
To: 'gnari' <gnari@simnet.is>; pgsql-performance
Thanks but whatever it does, it didn't work. :D
Do you think upgrading will fix this problem?
=========================
db=# alter table chatlogs alter column date set statistics 300;
ALTER
db=# analyze chatlogs;
ANALYZE
db=# explain analyze select * from chatlogs where date >= '12/1/04';
NOTICE: QUERY PLAN:
Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61
rows=3357 width=212) (actual time=22.14..138.53 rows=1312
loops=1)
Total runtime: 139.42 msec
EXPLAIN
morphTv=# explain analyze select * from chatlogs where date >= '11/03/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212) (actual
time=12.24..13419.36 rows=257137 loops=1)
Total runtime: 13573.70 msec
EXPLAIN
=========================
-----Original Message-----
From: gnari [mailto:gnari@simnet.is]
Sent: Wednesday, December 01, 2004 10:08 AM
To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] FW: Index usage
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>
> Hi, what do you mean by increasing the statistics on the date column?
alter table chatlogs alter column date set statistics 300; analyze chatlogs;
> > > Our version is 7.2.1
> >
> > upgrade time ?
>
> We never had any upgrade on it.
7.2 is a bit dated now that 8.0 is in beta
if you want to stay with 7.2, you should at least upgrade
to the latest point release (7.2.6 ?), as several serious bugs have been
fixed
gnari
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 02:07 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 02:33 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-12-01 04:00 ` Iain <iain@mst.co.jp>
2004-12-01 04:05 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
1 sibling, 1 reply; 34+ messages in thread
From: Iain @ 2004-12-01 04:00 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; 'gnari' <gnari@simnet.is>; pgsql-performance
If it's any help, i just ran this test on 7.4.6, my table has about 7000000
rows and the index is an integer.
The item id ranges from 1 to 20000.
As you can see from the following plans, the optimizer changed it's plan
depending on the value of the item id condition, and will use an index when
it determines that the number of values that will be returned is a low % of
the total table size.
The item_id is an integer, but It looked like you are using a character
field to store date information. Also, the dates you entered in your test
case seem to be in the format DD/MM/YY which won't be amenable to useful
comparative searching (I didn't read any of the earlier posts so if that
isn't the case, just ignore this). If this is the case, try storing the data
in a date column and see what happens then.
regards
Iain
test=# explain analyse select * from bigtable where item_id <= 1000;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------
Index Scan using d_bigtable_idx2 on bigtable (cost=0.00..118753.57
rows=59553 width=80) (actual
time=0.069..704.401 rows=58102 loops=1)
Index Cond: ((item_id)::integer <= 1000)
Total runtime: 740.786 ms
(3 rows)
test=# explain analyse select * from bigtable where item_id <= 100000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
---------------
Seq Scan on d_hi_mise_item_uri (cost=0.00..194285.15 rows=7140589
width=80) (actual time=0.027..18599.032 rows=71
14844 loops=1)
Filter: ((item_id)::integer <= 100000000)
Total runtime: 23024.986 ms
----- Original Message -----
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
To: "'gnari'" <gnari@simnet.is>; <pgsql-performance@postgresql.org>
Sent: Wednesday, December 01, 2004 11:33 AM
Subject: Re: [PERFORM] FW: Index usage
>
> Thanks but whatever it does, it didn't work. :D
>
> Do you think upgrading will fix this problem?
>
> =========================
> db=# alter table chatlogs alter column date set statistics 300;
> ALTER
> db=# analyze chatlogs;
> ANALYZE
> db=# explain analyze select * from chatlogs where date >= '12/1/04';
> NOTICE: QUERY PLAN:
>
> Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61
> rows=3357 width=212) (actual time=22.14..138.53 rows=1312
> loops=1)
> Total runtime: 139.42 msec
>
> EXPLAIN
> morphTv=# explain analyze select * from chatlogs where date >= '11/03/04';
> NOTICE: QUERY PLAN:
>
> Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212) (actual
> time=12.24..13419.36 rows=257137 loops=1)
> Total runtime: 13573.70 msec
>
> EXPLAIN
> =========================
>
>
>
> -----Original Message-----
> From: gnari [mailto:gnari@simnet.is]
> Sent: Wednesday, December 01, 2004 10:08 AM
> To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] FW: Index usage
>
>
> From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>
>
>>
>> Hi, what do you mean by increasing the statistics on the date column?
>
> alter table chatlogs alter column date set statistics 300; analyze
> chatlogs;
>
>> > > Our version is 7.2.1
>> >
>> > upgrade time ?
>>
>> We never had any upgrade on it.
>
> 7.2 is a bit dated now that 8.0 is in beta
>
> if you want to stay with 7.2, you should at least upgrade
> to the latest point release (7.2.6 ?), as several serious bugs have been
> fixed
>
> gnari
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 02:07 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 02:33 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 04:00 ` Re: FW: Index usage Iain <iain@mst.co.jp>
@ 2004-12-01 04:05 ` BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 04:18 ` Re: FW: Index usage Iain <iain@mst.co.jp>
0 siblings, 1 reply; 34+ messages in thread
From: BBI Edwin Punzalan @ 2004-12-01 04:05 UTC (permalink / raw)
To: 'Iain' <iain@mst.co.jp>; 'gnari' <gnari@simnet.is>; pgsql-performance
Hi. Thanks for your reply. The date column data type is date already. :D
-----Original Message-----
From: Iain [mailto:iain@mst.co.jp]
Sent: Wednesday, December 01, 2004 12:00 PM
To: BBI Edwin Punzalan; 'gnari'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] FW: Index usage
If it's any help, i just ran this test on 7.4.6, my table has about 7000000
rows and the index is an integer.
The item id ranges from 1 to 20000.
As you can see from the following plans, the optimizer changed it's plan
depending on the value of the item id condition, and will use an index when
it determines that the number of values that will be returned is a low % of
the total table size.
The item_id is an integer, but It looked like you are using a character
field to store date information. Also, the dates you entered in your test
case seem to be in the format DD/MM/YY which won't be amenable to useful
comparative searching (I didn't read any of the earlier posts so if that
isn't the case, just ignore this). If this is the case, try storing the data
in a date column and see what happens then.
regards
Iain
test=# explain analyse select * from bigtable where item_id <= 1000;
QUERY
PLAN
----------------------------------------------------------------------------
---------------------------------------
--------------------------------------------
Index Scan using d_bigtable_idx2 on bigtable (cost=0.00..118753.57
rows=59553 width=80) (actual
time=0.069..704.401 rows=58102 loops=1)
Index Cond: ((item_id)::integer <= 1000)
Total runtime: 740.786 ms
(3 rows)
test=# explain analyse select * from bigtable where item_id <= 100000000;
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------
---------------
Seq Scan on d_hi_mise_item_uri (cost=0.00..194285.15 rows=7140589
width=80) (actual time=0.027..18599.032 rows=71
14844 loops=1)
Filter: ((item_id)::integer <= 100000000)
Total runtime: 23024.986 ms
----- Original Message -----
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
To: "'gnari'" <gnari@simnet.is>; <pgsql-performance@postgresql.org>
Sent: Wednesday, December 01, 2004 11:33 AM
Subject: Re: [PERFORM] FW: Index usage
>
> Thanks but whatever it does, it didn't work. :D
>
> Do you think upgrading will fix this problem?
>
> =========================
> db=# alter table chatlogs alter column date set statistics 300; ALTER
> db=# analyze chatlogs;
> ANALYZE
> db=# explain analyze select * from chatlogs where date >= '12/1/04';
> NOTICE: QUERY PLAN:
>
> Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61
> rows=3357 width=212) (actual time=22.14..138.53 rows=1312
> loops=1)
> Total runtime: 139.42 msec
>
> EXPLAIN
> morphTv=# explain analyze select * from chatlogs where date >=
> '11/03/04';
> NOTICE: QUERY PLAN:
>
> Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212)
> (actual time=12.24..13419.36 rows=257137 loops=1) Total runtime:
> 13573.70 msec
>
> EXPLAIN
> =========================
>
>
>
> -----Original Message-----
> From: gnari [mailto:gnari@simnet.is]
> Sent: Wednesday, December 01, 2004 10:08 AM
> To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] FW: Index usage
>
>
> From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>
>
>>
>> Hi, what do you mean by increasing the statistics on the date column?
>
> alter table chatlogs alter column date set statistics 300; analyze
> chatlogs;
>
>> > > Our version is 7.2.1
>> >
>> > upgrade time ?
>>
>> We never had any upgrade on it.
>
> 7.2 is a bit dated now that 8.0 is in beta
>
> if you want to stay with 7.2, you should at least upgrade
> to the latest point release (7.2.6 ?), as several serious bugs have
> been fixed
>
> gnari
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 02:07 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 02:33 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 04:00 ` Re: FW: Index usage Iain <iain@mst.co.jp>
2004-12-01 04:05 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-12-01 04:18 ` Iain <iain@mst.co.jp>
0 siblings, 0 replies; 34+ messages in thread
From: Iain @ 2004-12-01 04:18 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; 'gnari' <gnari@simnet.is>; pgsql-performance
Sorry, i can't check this easily as I don't have any date fields in my data
(they all held has character strings - do as i say, not as i do) but maybe
you should cast or convert the string representation of the date to a date
in the where clause. Postgres might be doing some implicit conversion but if
it is, I'd expect it to use a YYYY-MM-DD format which is what I see here.
Something like ... WHERE date>= to_date('11/03/04','DD/MM/YY')
regards
Iain
----- Original Message -----
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
To: "'Iain'" <iain@mst.co.jp>; "'gnari'" <gnari@simnet.is>;
<pgsql-performance@postgresql.org>
Sent: Wednesday, December 01, 2004 1:05 PM
Subject: RE: [PERFORM] FW: Index usage
>
> Hi. Thanks for your reply. The date column data type is date already. :D
>
> -----Original Message-----
> From: Iain [mailto:iain@mst.co.jp]
> Sent: Wednesday, December 01, 2004 12:00 PM
> To: BBI Edwin Punzalan; 'gnari'; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] FW: Index usage
>
>
> If it's any help, i just ran this test on 7.4.6, my table has about
> 7000000
> rows and the index is an integer.
>
> The item id ranges from 1 to 20000.
>
> As you can see from the following plans, the optimizer changed it's plan
> depending on the value of the item id condition, and will use an index
> when
> it determines that the number of values that will be returned is a low %
> of
> the total table size.
>
> The item_id is an integer, but It looked like you are using a character
> field to store date information. Also, the dates you entered in your test
> case seem to be in the format DD/MM/YY which won't be amenable to useful
> comparative searching (I didn't read any of the earlier posts so if that
> isn't the case, just ignore this). If this is the case, try storing the
> data
>
> in a date column and see what happens then.
>
> regards
> Iain
>
> test=# explain analyse select * from bigtable where item_id <= 1000;
>
> QUERY
> PLAN
>
> ----------------------------------------------------------------------------
> ---------------------------------------
> --------------------------------------------
> Index Scan using d_bigtable_idx2 on bigtable (cost=0.00..118753.57
> rows=59553 width=80) (actual
> time=0.069..704.401 rows=58102 loops=1)
> Index Cond: ((item_id)::integer <= 1000)
> Total runtime: 740.786 ms
> (3 rows)
>
>
> test=# explain analyse select * from bigtable where item_id <= 100000000;
> QUERY PLAN
>
> ----------------------------------------------------------------------------
> ---------------------------------------
> ---------------
> Seq Scan on d_hi_mise_item_uri (cost=0.00..194285.15 rows=7140589
> width=80) (actual time=0.027..18599.032 rows=71
> 14844 loops=1)
> Filter: ((item_id)::integer <= 100000000)
> Total runtime: 23024.986 ms
>
> ----- Original Message -----
> From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
> To: "'gnari'" <gnari@simnet.is>; <pgsql-performance@postgresql.org>
> Sent: Wednesday, December 01, 2004 11:33 AM
> Subject: Re: [PERFORM] FW: Index usage
>
>
>>
>> Thanks but whatever it does, it didn't work. :D
>>
>> Do you think upgrading will fix this problem?
>>
>> =========================
>> db=# alter table chatlogs alter column date set statistics 300; ALTER
>> db=# analyze chatlogs;
>> ANALYZE
>> db=# explain analyze select * from chatlogs where date >= '12/1/04';
>> NOTICE: QUERY PLAN:
>>
>> Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61
>> rows=3357 width=212) (actual time=22.14..138.53 rows=1312
>> loops=1)
>> Total runtime: 139.42 msec
>>
>> EXPLAIN
>> morphTv=# explain analyze select * from chatlogs where date >=
>> '11/03/04';
>> NOTICE: QUERY PLAN:
>>
>> Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212)
>> (actual time=12.24..13419.36 rows=257137 loops=1) Total runtime:
>> 13573.70 msec
>>
>> EXPLAIN
>> =========================
>>
>>
>>
>> -----Original Message-----
>> From: gnari [mailto:gnari@simnet.is]
>> Sent: Wednesday, December 01, 2004 10:08 AM
>> To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] FW: Index usage
>>
>>
>> From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>>
>>
>>>
>>> Hi, what do you mean by increasing the statistics on the date column?
>>
>> alter table chatlogs alter column date set statistics 300; analyze
>> chatlogs;
>>
>>> > > Our version is 7.2.1
>>> >
>>> > upgrade time ?
>>>
>>> We never had any upgrade on it.
>>
>> 7.2 is a bit dated now that 8.0 is in beta
>>
>> if you want to stay with 7.2, you should at least upgrade
>> to the latest point release (7.2.6 ?), as several serious bugs have
>> been fixed
>>
>> gnari
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 02:07 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 02:33 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-12-01 08:24 ` gnari <gnari@simnet.is>
1 sibling, 0 replies; 34+ messages in thread
From: gnari @ 2004-12-01 08:24 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; pgsql-performance
From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
> Thanks but whatever it does, it didn't work. :
> Do you think upgrading will fix this problem?
are you sure there is a problem here to solve ?
> Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212) (actual
> time=12.24..13419.36 rows=257137 loops=1)
you see that the actual rowcount matches the estimate,
so the planner is not being misled by wrong statistics.
you realize that an indexscan is not allways faster than
sequential scan unless the number of rows are a small
percentage of the total number of rows
did you try to add a 'order by date' clause to your query ?
gnari
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-11-24 10:16 ` Richard Huxton <dev@archonet.com>
2004-11-24 10:39 ` Re: FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
1 sibling, 1 reply; 34+ messages in thread
From: Richard Huxton @ 2004-11-24 10:16 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; +Cc: pgsql-performance
BBI Edwin Punzalan wrote:
> Thanks, Tim.
>
> I tried adding an upper limit and its still the same as follows:
>
> ==============
> db=# explain analyze select date from chatlogs where date>='11/24/04';
> NOTICE: QUERY PLAN:
>
> Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72 rows=37
> width=4) (actual time=0.18..239.69 rows=10737 loops=1)
> Total runtime: 246.22 msec
>
> EXPLAIN
> db=# explain analyze select date from chatlogs where date>='11/23/04' and
> date<'11/24/04';
> NOTICE: QUERY PLAN:
>
> Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual
> time=0.44..4447.01 rows=13029 loops=1)
> Total runtime: 4455.56 msec
We have two issues here
1. In the first example it only picks an index because it thinks it is
going to get 37 rows, it actually gets 10737
2. It's taking 4455ms to run a seq-scan but only 246ms to run an
index-scan over 10737 rows (and then fetch the rows too).
Questions:
1. How many rows do you have in chatlogs?
2. Is this the only problem you are experiencing, or just one from many?
3. Have you tuned any configuration settings? e.g. as suggested in:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 10:16 ` Re: FW: FW: Index usage Richard Huxton <dev@archonet.com>
@ 2004-11-24 10:39 ` BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 11:02 ` Re: FW: FW: Index usage Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 34+ messages in thread
From: BBI Edwin Punzalan @ 2004-11-24 10:39 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-performance
Hi.
1) chatlogs rows increases every now and then (its in a live environment)
and currently have 538,696 rows
2) this is the only problem we experienced. So far, all our other indexes
are being used correctly.
3) I don't remember tuning any post-installation configuration of our
postgreSQL except setting fsync to false.
Thanks for taking a look at our problem. :D
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, November 24, 2004 6:17 PM
To: BBI Edwin Punzalan
Cc: pgsql-performance@postgresql.org
Subject: Re: FW: [PERFORM] FW: Index usage
BBI Edwin Punzalan wrote:
> Thanks, Tim.
>
> I tried adding an upper limit and its still the same as follows:
>
> ==============
> db=# explain analyze select date from chatlogs where date>='11/24/04';
> NOTICE: QUERY PLAN:
>
> Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72
> rows=37
> width=4) (actual time=0.18..239.69 rows=10737 loops=1)
> Total runtime: 246.22 msec
>
> EXPLAIN
> db=# explain analyze select date from chatlogs where date>='11/23/04'
> and date<'11/24/04';
> NOTICE: QUERY PLAN:
>
> Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual
> time=0.44..4447.01 rows=13029 loops=1) Total runtime: 4455.56 msec
We have two issues here
1. In the first example it only picks an index because it thinks it is
going to get 37 rows, it actually gets 10737
2. It's taking 4455ms to run a seq-scan but only 246ms to run an
index-scan over 10737 rows (and then fetch the rows too).
Questions:
1. How many rows do you have in chatlogs?
2. Is this the only problem you are experiencing, or just one from many? 3.
Have you tuned any configuration settings? e.g. as suggested in:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: FW: FW: Index usage
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 10:16 ` Re: FW: FW: Index usage Richard Huxton <dev@archonet.com>
2004-11-24 10:39 ` Re: FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
@ 2004-11-24 11:02 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 34+ messages in thread
From: Richard Huxton @ 2004-11-24 11:02 UTC (permalink / raw)
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>; +Cc: pgsql-performance
BBI Edwin Punzalan wrote:
> Hi.
>
> 1) chatlogs rows increases every now and then (its in a live environment)
> and currently have 538,696 rows
OK, so as a rule of thumb I'd say if you were fetching less than 5000
rows it's bound to use an index. If more than 50,000 always use a
seqscan, otherwise it'll depend on configuration settings. It looks like
you settings are suggesting the cost of an index-scan vs seq-scan are
greater than they are.
> 2) this is the only problem we experienced. So far, all our other indexes
> are being used correctly.
Good.
> 3) I don't remember tuning any post-installation configuration of our
> postgreSQL except setting fsync to false.
So long as you know why this can cause data loss. It won't affect this
problem.
Read that performance article I linked to in the last message, it's
written by two people who know what they're talking about. The standard
configuration settings are designed to work on any machine, not provide
good performance. Work through the basics there and we can look at
random_page_cost etc. if it's still causing you problems.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 34+ messages in thread
* index usage
@ 2006-07-28 19:21 Ben <bench@silentmedia.com>
0 siblings, 0 replies; 34+ messages in thread
From: Ben @ 2006-07-28 19:21 UTC (permalink / raw)
To: pgsql-performance
I have a table with 37000 rows, an integer column, and an index on that
column. I've got a function that returns an integer. When I do a select
where I restrict that column to being equal to a static number, explain
tells me the index will be used. When I do the same thing but use the
function instead of a static number, explain shows me a full scan on the
table.
I must be missing something, because my understanding is that the function
will be evaluated once for the statement and then collapsed into a static
number for the filtering. But the results of the explain seem to imply
that's not the case....?
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
@ 2006-07-28 19:23 Daniel Caune <daniel.caune@ubisoft.com>
2006-07-28 20:59 ` Re: index usage Ben <bench@silentmedia.com>
0 siblings, 1 reply; 34+ messages in thread
From: Daniel Caune @ 2006-07-28 19:23 UTC (permalink / raw)
To: Ben <bench@silentmedia.com>; pgsql-performance
> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] De la part de Ben
> Envoyé : vendredi, juillet 28, 2006 15:21
> À : pgsql-performance@postgresql.org
> Objet : [PERFORM] index usage
>
> I have a table with 37000 rows, an integer column, and an index on that
> column. I've got a function that returns an integer. When I do a select
> where I restrict that column to being equal to a static number, explain
> tells me the index will be used. When I do the same thing but use the
> function instead of a static number, explain shows me a full scan on the
> table.
>
> I must be missing something, because my understanding is that the function
> will be evaluated once for the statement and then collapsed into a static
> number for the filtering. But the results of the explain seem to imply
> that's not the case....?
>
Is your function IMMUTABLE, STABLE or VOLATILE?
--
Daniel
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2006-07-28 19:23 Re: index usage Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-07-28 20:59 ` Ben <bench@silentmedia.com>
2006-07-28 21:06 ` Re: index usage Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 34+ messages in thread
From: Ben @ 2006-07-28 20:59 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-performance
It's volatile, but it will always return an integer.
On Fri, 28 Jul 2006, Daniel Caune wrote:
>> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
>> owner@postgresql.org] De la part de Ben
>> Envoyé : vendredi, juillet 28, 2006 15:21
>> À : pgsql-performance@postgresql.org
>> Objet : [PERFORM] index usage
>>
>> I have a table with 37000 rows, an integer column, and an index on that
>> column. I've got a function that returns an integer. When I do a select
>> where I restrict that column to being equal to a static number, explain
>> tells me the index will be used. When I do the same thing but use the
>> function instead of a static number, explain shows me a full scan on the
>> table.
>>
>> I must be missing something, because my understanding is that the function
>> will be evaluated once for the statement and then collapsed into a static
>> number for the filtering. But the results of the explain seem to imply
>> that's not the case....?
>>
>
> Is your function IMMUTABLE, STABLE or VOLATILE?
>
> --
> Daniel
>
From pgsql-performance-owner@postgresql.org Fri Jul 28 18:02:18 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
by postgresql.org (Postfix) with ESMTP id 15F6A9FB270
for <pgsql-performance-postgresql.org@postgresql.org>; Fri, 28 Jul 2006 18:02:18 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
with ESMTP id 88817-04 for <pgsql-performance-postgresql.org@postgresql.org>;
Fri, 28 Jul 2006 18:02:02 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mir3-fs.mir3.com (mail.mir3.com [65.208.188.100])
by postgresql.org (Postfix) with ESMTP id BCD2D9FB27A
for <pgsql-performance@postgresql.org>; Fri, 28 Jul 2006 18:01:21 -0300 (ADT)
Received: mir3-fs.mir3.com 172.16.1.11 from 172.16.2.68 172.16.2.68 via HTTP with MS-WebStorage 6.0.6249
Received: from archimedes.mirlogic.com by mir3-fs.mir3.com; 28 Jul 2006 14:01:18 -0700
Subject: Re: index usage
From: Mark Lewis <mark.lewis@mir3.com>
To: Ben <bench@silentmedia.com>
Cc: Daniel Caune <daniel.caune@ubisoft.com>, pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.64.0607281359030.11400@GRD.cube42.tai.silentmedia.com>
References: <1E293D3FF63A3740B10AD5AAD88535D202B655F1@UBIMAIL1.ubisoft.org>
<Pine.LNX.4.64.0607281359030.11400@GRD.cube42.tai.silentmedia.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Organization: MIR3, Inc.
Date: Fri, 28 Jul 2006 14:01:18 -0700
Message-Id: <camel@archimedes/">1154120478.1634.666.camel@archimedes>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-27)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL,
UNPARSEABLE_RELAY
X-Spam-Level:
X-Archive-Number: 200607/274
X-Sequence-Number: 20134
A volatile function has may return a different result for each row;
think of the random() or nextval() functions for example. You wouldn't
want them to return the same value for each row returned.
-- Mark Lewis
On Fri, 2006-07-28 at 13:59 -0700, Ben wrote:
> It's volatile, but it will always return an integer.
>=20
> On Fri, 28 Jul 2006, Daniel Caune wrote:
>=20
> >> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> >> owner@postgresql.org] De la part de Ben
> >> Envoy=C3=A9 : vendredi, juillet 28, 2006 15:21
> >> =C3=80 : pgsql-performance@postgresql.org
> >> Objet : [PERFORM] index usage
> >>
> >> I have a table with 37000 rows, an integer column, and an index on tha=
t
> >> column. I've got a function that returns an integer. When I do a selec=
t
> >> where I restrict that column to being equal to a static number, explai=
n
> >> tells me the index will be used. When I do the same thing but use the
> >> function instead of a static number, explain shows me a full scan on t=
he
> >> table.
> >>
> >> I must be missing something, because my understanding is that the func=
tion
> >> will be evaluated once for the statement and then collapsed into a sta=
tic
> >> number for the filtering. But the results of the explain seem to imply
> >> that's not the case....?
> >>
> >
> > Is your function IMMUTABLE, STABLE or VOLATILE?
> >
> > --
> > Daniel
> >
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2006-07-28 19:23 Re: index usage Daniel Caune <daniel.caune@ubisoft.com>
2006-07-28 20:59 ` Re: index usage Ben <bench@silentmedia.com>
@ 2006-07-28 21:06 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 34+ messages in thread
From: Tom Lane @ 2006-07-28 21:06 UTC (permalink / raw)
To: Ben <bench@silentmedia.com>; +Cc: Daniel Caune <daniel.caune@ubisoft.com>; pgsql-performance
Ben <bench@silentmedia.com> writes:
> It's volatile, but it will always return an integer.
If it's volatile then it can't be used for an index condition.
regards, tom lane
^ permalink raw reply [nested|flat] 34+ messages in thread
* Index usage
@ 2006-08-21 17:35 Scott Matseas <smatseas@intrusic.com>
2006-08-21 18:26 ` Re: Index usage Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 34+ messages in thread
From: Scott Matseas @ 2006-08-21 17:35 UTC (permalink / raw)
To: pgsql-performance
We're having a problem with one of our queries being slow. It appears to be due
to the index being used to go from tableA to tableB.
Here are the tables:
CREATE TABLE tableA
(
table_idA int8 NOT NULL DEFAULT nextval('tableA_id_seq'::regclass),
CONSTRAINT table_idA_pk PRIMARY KEY (table_idA),
)
WITHOUT OIDS;
CREATE TABLE tableB
(
table_idB int8 NOT NULL DEFAULT nextval('tableB_id_seq'::regclass),
table_idA int8 NOT NULL,
direction char NOT NULL,
CONSTRAINT tableB_pk PRIMARY KEY (table_idB),
CONSTRAINT tableB_unq UNIQUE (table_idA, direction),
)
WITHOUT OIDS;
CREATE TABLE last_summarized
(
summary_name varchar(64) NOT NULL,
summarized_id int8,
max_session_id int8,
CONSTRAINT last_summarized_pk PRIMARY KEY (summary_name)
)
WITHOUT OIDS;
Here is the query:
explain
SELECT * FROM
last_summarized ls
JOIN tableA s ON s.table_idA > ls.summarized_id AND s.table_idA
<= ls.max_session_id
LEFT JOIN tableB sf ON s.table_idA = sf.table_idA AND sf.direction = 'a'::"char"
LEFT JOIN tableB sfb ON s.table_idA = sfb.table_idA AND sfb.direction = 'b'::"char"
WHERE ls.summary_name::text = 'summary'::text
Size of tables in # of rows
tableA: 9,244,816
tableB: 15,398,497
last_summarized: 1
Explain of the above query:
"Hash Left Join (cost=1811349.31..18546527.89 rows=1029087 width=294)"
" Hash Cond: ("outer".table_idA = "inner".table_idA)"
" -> Hash Left Join (cost=915760.88..7519203.61 rows=1029087 width=219)"
" Hash Cond: ("outer".table_idA = "inner".table_idA)"
" -> Nested Loop (cost=0.00..126328.57 rows=1029087 width=144)"
" -> Index Scan using last_summarized_pk on last_summarized ls (cost=0.00..5.98 rows=1 width=82)"
" Index Cond: ((summary_name)::text = 'summary'::text)"
" -> Index Scan using table_idA_pk on tableA s (cost=0.00..110886.29 rows=1029087 width=62)"
" Index Cond: ((s.table_idA > "outer".summarized_id) AND (s.table_idA <= "outer".max_session_id))"
" -> Hash (cost=784763.16..784763.16 rows=8100289 width=75)"
" -> Bitmap Heap Scan on tableB sf (cost=216418.55..784763.16 rows=8100289 width=75)"
" Recheck Cond: (direction = 'a'::"char")"
" -> Bitmap Index Scan on tableB_unq (cost=0.00..216418.55 rows=8100289 width=0)"
" Index Cond: (direction = 'a'::"char")" <------ USING part of Index
" -> Hash (cost=775968.61..775968.61 rows=7396725 width=75)"
" -> Bitmap Heap Scan on tableB sfb (cost=216418.55..775968.61 rows=7396725 width=75)"
" Recheck Cond: (direction = 'b'::"char")"
" -> Bitmap Index Scan on tableB_unq (cost=0.00..216418.55 rows=7396725 width=0)"
" Index Cond: (direction = 'b'::"char")" <------ USING part of Index
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Index usage
2006-08-21 17:35 Index usage Scott Matseas <smatseas@intrusic.com>
@ 2006-08-21 18:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-21 20:25 ` Re: Index usage Scott Matseas <smatseas@intrusic.com>
0 siblings, 1 reply; 34+ messages in thread
From: Tom Lane @ 2006-08-21 18:26 UTC (permalink / raw)
To: Scott Matseas <smatseas@intrusic.com>; +Cc: pgsql-performance
Scott Matseas <smatseas@intrusic.com> writes:
> If I enable sequential scan the Index Cond in
> question gets replaced with a Seq scan.
What other planner parameters have you been fooling with?
With no data in the tables, I get a reasonably sane-looking plan,
so I'm thinking you've chosen bad values for something or other
(starting with enable_seqscan = off ;-))
explain
SELECT * FROM
last_summarized ls
JOIN tableA s ON s.table_idA > ls.summarized_id AND s.table_idA
<= ls.max_session_id
LEFT JOIN tableB sf ON s.table_idA = sf.table_idA AND sf.direction = 'a'::char
LEFT JOIN tableB sfb ON s.table_idA = sfb.table_idA AND sfb.direction = 'b'::char
WHERE ls.summary_name::text = 'summary'::text ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=6.16..54.51 rows=216 width=116)
-> Nested Loop Left Join (cost=6.16..42.05 rows=216 width=95)
-> Nested Loop (cost=6.16..29.58 rows=216 width=74)
-> Index Scan using last_summarized_pk on last_summarized ls (cost=0.00..8.02 rows=1 width=66)
Index Cond: ((summary_name)::text = 'summary'::text)
-> Bitmap Heap Scan on tablea s (cost=6.16..18.32 rows=216 width=8)
Recheck Cond: ((s.table_ida > ls.summarized_id) AND (s.table_ida <= ls.max_session_id))
-> Bitmap Index Scan on table_ida_pk (cost=0.00..6.16 rows=216 width=0)
Index Cond: ((s.table_ida > ls.summarized_id) AND (s.table_ida <= ls.max_session_id))
-> Index Scan using tableb_unq on tableb sfb (cost=0.00..0.05 rows=1 width=21)
Index Cond: ((s.table_ida = sfb.table_ida) AND (sfb.direction = 'b'::bpchar))
-> Index Scan using tableb_unq on tableb sf (cost=0.00..0.05 rows=1 width=21)
Index Cond: ((s.table_ida = sf.table_ida) AND (sf.direction = 'a'::bpchar))
(13 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: Index usage
2006-08-21 17:35 Index usage Scott Matseas <smatseas@intrusic.com>
2006-08-21 18:26 ` Re: Index usage Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-08-21 20:25 ` Scott Matseas <smatseas@intrusic.com>
0 siblings, 0 replies; 34+ messages in thread
From: Scott Matseas @ 2006-08-21 20:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> What other planner parameters have you been fooling with?
Hi Tom,
The other parameters that have been changed are:
set join_collapse_limit to 1
set enable_sort to off
We are using version 8.1.3. We've noticed the query plan changing depending
on the amount of data in the tables especially when the query looks at
more rows
in tableA. The parameter work_mem is set to 262,144.
Thanks,
Scott
^ permalink raw reply [nested|flat] 34+ messages in thread
* index usage
@ 2007-04-23 17:20 Arkadiusz Raj <arek@raj.priv.pl>
2007-04-23 17:58 ` Re: index usage Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 34+ messages in thread
From: Arkadiusz Raj @ 2007-04-23 17:20 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a table in my database that is updated every minute with new acquired
data. Anyway there is a query to get latest values to be displayed on
screen. I have postgresql 7.4.2 that work very fine. The problem was that
after hdd crash I have rebuild database from the archive and... Execution
time of this query starts to be unacceptable. And I found funny thing. Using
static value in place expression remove this problem. Query started to be
executed fast again.
I did not change any settings in postgresql configuration. Just had to
restart all the services.
Can someone tell me why the optimizer stopped to choose index? I had seqscan
disabled already.
One note about those two outputs below: there are different number of
touples returned due to the fact that in fact the timestamp is chosen
differently.
Regards,
/Arek
------------------------------------------------------------------
explain analyze SELECT distinct on (index) index, status, value FROM _values
WHERE device=1 and timestamp>(now()-5*interval '1 min') ORDER by index,
timestamp desc;
QUERY
PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------
Unique (cost=100117679.93..100117756.29 rows=1 width=24) (actual
time=5279.262..5279.308 rows=10 loops=1)
-> Sort (cost=100117679.93..100117718.11 rows=15272 width=24) (actual
time=5279.260..5279.275 rows=21 loops=1)
Sort Key: "index", "timestamp"
-> Seq Scan on _values (cost=100000000.00..100116618.64
rows=15272 width=24) (actual time=5277.596..5279.184 rows=21 loops=1)
Filter: ((device = 1) AND (("timestamp")::timestamp with time
zone > (now() - '00:05:00'::interval)))
Total runtime: 5279.391 ms
(6 rows)
explain analyze SELECT distinct on (index) index, status, value FROM _values
WHERE device=1 and timestamp>'2007-04-22 21:20' ORDER by index, timestamp
desc;
QUERY
PLAN
----------------------------------------------------------------------------
---------------------------------------------------------------
Unique (cost=703.45..703.47 rows=1 width=24) (actual time=4.807..4.867
rows=10 loops=1)
-> Sort (cost=703.45..703.46 rows=5 width=24) (actual time=4.804..4.827
rows=31 loops=1)
Sort Key: "index", "timestamp"
-> Index Scan using _values_dbidx_idx on _values
(cost=0.00..703.39 rows=5 width=24) (actual time=0.260..4.728 rows=31
loops=1)
Index Cond: ("timestamp" > '2007-04-22 21:20:00'::timestamp
without time zone)
Filter: (device = 1)
Total runtime: 4.958 ms
(7 rows)
--
List przeskanowano programem ArcaMail, ArcaVir 2007
przeskanowano 2007-04-23 19:20:29, silnik: 2007.01.01 12:00:00, bazy: 2007.04.15 09:21:20
This message has been scanned by ArcaMail, ArcaVir 2007
scanned 2007-04-23 19:20:29, engine: 2007.01.01 12:00:00, base: 2007.04.15 09:21:20
http://www.arcabit.com
^ permalink raw reply [nested|flat] 34+ messages in thread
* Re: index usage
2007-04-23 17:20 index usage Arkadiusz Raj <arek@raj.priv.pl>
@ 2007-04-23 17:58 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 34+ messages in thread
From: Steinar H. Gunderson @ 2007-04-23 17:58 UTC (permalink / raw)
To: Arkadiusz Raj <arek@raj.priv.pl>; +Cc: pgsql-performance
On Mon, Apr 23, 2007 at 07:20:29PM +0200, Arkadiusz Raj wrote:
> I have a table in my database that is updated every minute with new acquired
> data. Anyway there is a query to get latest values to be displayed on
> screen. I have postgresql 7.4.2 that work very fine.
You want _at least_ the latest 7.4 version -- ideally, the latest 8.2
version.
> The problem was that
> after hdd crash I have rebuild database from the archive and... Execution
> time of this query starts to be unacceptable.
Have you re-ANALYZEd after the data load?
Anyhow, the issue with the planner not knowing how to estimate expressions
like "now() - interval '5 minutes'" correctly is a known 7.4 issue, and it's
fixed in later versions. It might have worked more or less by accident
earlier, although it seems odd that it wouldn't even have considered the
index scan...
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 34+ messages in thread
end of thread, other threads:[~2007-04-23 17:58 UTC | newest]
Thread overview: 34+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-17 11:48 index usage Timur Irmatov <thor@sarkor.com>
2003-01-17 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-17 15:08 ` Timur Irmatov <thor@sarkor.com>
2003-01-17 15:33 ` Andrew Sullivan <andrew@libertyrms.info>
2004-04-23 22:21 index usage brad-pgperf@duttonbros.com
2004-04-26 18:58 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-26 19:16 ` brad-pgperf@duttonbros.com
2004-04-28 15:40 ` scott.marlowe <scott.marlowe@ihs.com>
2004-04-28 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-24 06:52 FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 07:34 Re: FW: Index usage Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2004-11-24 08:07 FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 08:34 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 09:43 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 19:12 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 01:50 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 02:07 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-12-01 02:33 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 04:00 ` Re: FW: Index usage Iain <iain@mst.co.jp>
2004-12-01 04:05 ` Re: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-12-01 04:18 ` Re: FW: Index usage Iain <iain@mst.co.jp>
2004-12-01 08:24 ` Re: FW: Index usage gnari <gnari@simnet.is>
2004-11-24 10:16 ` Re: FW: FW: Index usage Richard Huxton <dev@archonet.com>
2004-11-24 10:39 ` Re: FW: FW: Index usage BBI Edwin Punzalan <edwin@bluebamboo.ph>
2004-11-24 11:02 ` Re: FW: FW: Index usage Richard Huxton <dev@archonet.com>
2006-07-28 19:21 index usage Ben <bench@silentmedia.com>
2006-07-28 19:23 Re: index usage Daniel Caune <daniel.caune@ubisoft.com>
2006-07-28 20:59 ` Ben <bench@silentmedia.com>
2006-07-28 21:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-21 17:35 Index usage Scott Matseas <smatseas@intrusic.com>
2006-08-21 18:26 ` Re: Index usage Tom Lane <tgl@sss.pgh.pa.us>
2006-08-21 20:25 ` Re: Index usage Scott Matseas <smatseas@intrusic.com>
2007-04-23 17:20 index usage Arkadiusz Raj <arek@raj.priv.pl>
2007-04-23 17:58 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
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