pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSequential scan where Index scan expected.
9+ messages / 6 participants
[nested] [flat]
* Sequential scan where Index scan expected.
@ 2006-03-03 07:27 ` Bryce Nesbitt <bryce1@obviously.com>
2006-03-04 19:07 ` Re: Sequential scan where Index scan expected. Nikolay Samokhvalov <samokhvalov@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Bryce Nesbitt @ 2006-03-03 07:27 UTC (permalink / raw)
To: pgsql-sql
I'm getting sequential scans (and poor performance), on scans using my
primary keys.
Can anyone help figure out why?
demo=# \d xx_thing
Table "public.xx_thing"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------
thing_id | bigint | not null
thing_model | character varying(128) |
thing_color | character varying(128) |
thing_year | integer |
Indexes:
"xx_thing_pkey" primary key, btree (thing_id)
demo=# analyze verbose xx_thing_event;
INFO: analyzing "public.xx_thing_event"
INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
total rows
demo=# explain update xx_thing_event set thing_color='foo' where
thing_event_id=10000;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110)
Filter: (thing_event_id = 10000)
(2 rows)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sequential scan where Index scan expected.
2006-03-03 07:27 ` Sequential scan where Index scan expected. Bryce Nesbitt <bryce1@obviously.com>
@ 2006-03-04 19:07 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Nikolay Samokhvalov @ 2006-03-04 19:07 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "Bryce Nesbitt" <bryce1@obviously.com>
please, show '\d xx_thing_event' too :-)
On 3/3/06, Bryce Nesbitt <bryce1@obviously.com> wrote:
> I'm getting sequential scans (and poor performance), on scans using my
> primary keys.
> Can anyone help figure out why?
>
> demo=# \d xx_thing
> Table "public.xx_thing"
> Column | Type | Modifiers
> -------------------------+-----------------------------+-----------
> thing_id | bigint | not null
> thing_model | character varying(128) |
> thing_color | character varying(128) |
> thing_year | integer |
> Indexes:
> "xx_thing_pkey" primary key, btree (thing_id)
>
>
> demo=# analyze verbose xx_thing_event;
> INFO: analyzing "public.xx_thing_event"
> INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
> total rows
>
>
> demo=# explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000;
> QUERY PLAN
> ---------------------------------------------------------------------
> Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110)
> Filter: (thing_event_id = 10000)
> (2 rows)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Best regards,
Nikolay
^ permalink raw reply [nested|flat] 9+ messages in thread
* Sequential scan where Index scan expected (update)
@ 2006-03-03 07:28 ` Bryce Nesbitt <bryce1@obviously.com>
2006-03-03 09:31 ` Re: Sequential scan where Index scan expected (update) Ragnar <gnari@hive.is>
2006-03-03 12:47 ` Re: Sequential scan where Index scan expected (update) Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 2 replies; 9+ messages in thread
From: Bryce Nesbitt @ 2006-03-03 07:28 UTC (permalink / raw)
To: pgsql-sql
I'm getting sequential scans (and poor performance), on scans using my
primary keys. This is an older postgres.
Can anyone help figure out why?
demo=# \d xx_thing
Table "public.xx_thing"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------
thing_id | bigint | not null
thing_model | character varying(128) |
thing_color | character varying(128) |
thing_year | integer |
Indexes:
"xx_thing_pkey" primary key, btree (thing_id)
demo=# analyze verbose xx_thing_event;
INFO: analyzing "public.xx_thing_event"
INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
total rows
demo=# explain update xx_thing_event set thing_color='foo' where
thing_event_id=10000;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110)
Filter: (thing_event_id = 10000)
(2 rows)
demo=# select * from version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sequential scan where Index scan expected (update)
2006-03-03 07:28 ` Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
@ 2006-03-03 09:31 ` Ragnar <gnari@hive.is>
1 sibling, 0 replies; 9+ messages in thread
From: Ragnar @ 2006-03-03 09:31 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
On fim, 2006-03-02 at 23:28 -0800, Bryce Nesbitt wrote:
> I'm getting sequential scans (and poor performance), on scans using my
> primary keys. This is an older postgres.
> Can anyone help figure out why?
>
>
> demo=# \d xx_thing
> Table "public.xx_thing"
> Column | Type | Modifiers
> -------------------------+-----------------------------+-----------
> thing_id | bigint | not null
...
> demo=# explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000;
> QUERY PLAN
> ---------------------------------------------------------------------
> Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110)
...
> demo=# select * from version();
> version
> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
try one of:
... WHERE thing_event_id=10000::bigint
... WHERE thing_event_id='10000'
or upgrade to >= 8.0
your problem is that the 10000 in 'thing_event_id=10000' is parsed as an
integer but not a bigint, so the planner
does not find a matching index. so you either have to
scpecify ::bigint or ::int8 in the query or quote the number, which will
cause postgres to cast it to the
column's type.
if you try
... WHERE thing_event_id=10000000000
you will see the index used because this number cannot
be a int4 so is parsed as a bigint.
newer versions of Postgresql can deal with this.
gnari
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sequential scan where Index scan expected (update)
2006-03-03 07:28 ` Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
@ 2006-03-03 12:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-03 19:35 ` Re: Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 1 reply; 9+ messages in thread
From: Andrew Sullivan @ 2006-03-03 12:47 UTC (permalink / raw)
To: pgsql-sql
On Thu, Mar 02, 2006 at 11:28:49PM -0800, Bryce Nesbitt wrote:
> Can anyone help figure out why?
Well. . .
>
>
> demo=# \d xx_thing
^^^^^^^^
> -------------------------+-----------------------------+-----------
> thing_id | bigint | not null
^^^^^^
> demo=# explain update xx_thing_event set thing_color='foo' where
^^^^^^^^^^^^^^
. . .you haven't actually given us the right schema here, but if I
had to guess, I'd say you could put this
> thing_event_id=10000;
^^^^^
in quotes. The automatic int4-int8 coercion is probably your
problem. Also
> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
^^^^^
you need to upgrade PostgreSQL Right Now. There are serious problems
with earlier 7.4 releases. Get the latest, or risk data corruption.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sequential scan where Index scan expected (update)
2006-03-03 07:28 ` Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
2006-03-03 12:47 ` Re: Sequential scan where Index scan expected (update) Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-03-03 19:35 ` Bryce Nesbitt <bryce1@obviously.com>
2006-03-03 19:40 ` Re: Sequential scan where Index scan expected (update) Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 9+ messages in thread
From: Bryce Nesbitt @ 2006-03-03 19:35 UTC (permalink / raw)
To: pgsql-sql
Andrew Sullivan wrote:
>> thing_event_id=10000;
>>
> ^^^^^
>
> in quotes. The automatic int4-int8 coercion is probably your
> problem. Also
>
Yup that's it. But this project uses (ugh) Hibernate. I can't change
it. I may have to change
from BIGINT primary keys to INT.
Also:
Any hints on the table statistics? I turn them on, in a session, as
user postgres, but get nothing:
stage=# SHOW ALL;
name | setting
--------------------------------+----------------
...
stats_block_level | on
stats_command_string | on
stats_reset_on_server_start | on
stats_row_level | on
stats_start_collector | on
stage=# select * from pg_statio_user_indexes;
relid | indexrelid | schemaname | relname
| indexrelname | idx_blks_read | idx_blks_hit
----------+------------+------------+-----------------------------+----------------------------------+---------------+--------------
18810975 | 18811183 | public | eg_invoice |
ix22f7bc70c7de2059 | 0 | 0
18810979 | 18811184 | public | eg_order |
ix522779518edf278d | 0 | 0
18810984 | 18811185 | public | eg_invoice_tax |
ix2f10773c8edf278d | 0 | 0
18810986 | 18811186 | public | eg_order_line |
ixf8331222783867cc | 0 | 0
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sequential scan where Index scan expected (update)
2006-03-03 07:28 ` Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
2006-03-03 12:47 ` Re: Sequential scan where Index scan expected (update) Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-03 19:35 ` Re: Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
@ 2006-03-03 19:40 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Sullivan @ 2006-03-03 19:40 UTC (permalink / raw)
To: pgsql-sql
On Fri, Mar 03, 2006 at 11:35:55AM -0800, Bryce Nesbitt wrote:
> Yup that's it. But this project uses (ugh) Hibernate. I can't change
> it. I may have to change
> from BIGINT primary keys to INT.
>
Well, you could upgrade from 7.4.
> Also:
> Any hints on the table statistics? I turn them on, in a session, as
> user postgres, but get nothing:
You have to SIGHUP to get those to take effect, IIRC.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sequential scan where Index scan expected (update)
@ 2006-03-03 07:37 Gregory S. Williamson <gsw@globexplorer.com>
2006-03-03 09:46 ` Re: Sequential scan where Index scan expected (update) Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 9+ messages in thread
From: Gregory S. Williamson @ 2006-03-03 07:37 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
Perhaps it is clogged with dead tuples -- has it been vacuumed recently with enough FSM space ?
It seems unlikely but maybe try an explict cast for the thing_id call, e.g.
explain update xx_thing_event set thing_color='foo' where
thing_event_id=10000::bigint;
It may also be that 5842 rows is enough that the planner decides it is faster to do a sequential scan that the busier index scan (read index, get data row, versus just reading all the necessary pages in one faster sequential scan).
If you set the sequential scan parameter in the config file and reload postgres does the same query get faster ? (not suggesting this for real runtime use but it can be useful to diagnose issues).
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Bryce Nesbitt
Sent: Thu 3/2/2006 11:28 PM
To: pgsql-sql@postgresql.org
Cc:
Subject: [SQL] Sequential scan where Index scan expected (update)
I'm getting sequential scans (and poor performance), on scans using my
primary keys. This is an older postgres.
Can anyone help figure out why?
demo=# \d xx_thing
Table "public.xx_thing"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------
thing_id | bigint | not null
thing_model | character varying(128) |
thing_color | character varying(128) |
thing_year | integer |
Indexes:
"xx_thing_pkey" primary key, btree (thing_id)
demo=# analyze verbose xx_thing_event;
INFO: analyzing "public.xx_thing_event"
INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
total rows
demo=# explain update xx_thing_event set thing_color='foo' where
thing_event_id=10000;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110)
Filter: (thing_event_id = 10000)
(2 rows)
demo=# select * from version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 1: 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
!DSPAM:4407f048253644359117518!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sequential scan where Index scan expected (update)
2006-03-03 07:37 Re: Sequential scan where Index scan expected (update) Gregory S. Williamson <gsw@globexplorer.com>
@ 2006-03-03 09:46 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 9+ messages in thread
From: Michael Fuhr @ 2006-03-03 09:46 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
On Thu, Mar 02, 2006 at 11:37:53PM -0800, Gregory S. Williamson wrote:
> It seems unlikely but maybe try an explict cast for the thing_id call, e.g.
> explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000::bigint;
The server is pre-8.0 so it's likely that this is indeed the problem.
Unfortunately the table that was shown in the \d output (xx_thing)
isn't the same table as in the update command (xx_thing_event) so
we can only guess that thing_event_id is a bigint based on what
xx_thing looks like.
> It may also be that 5842 rows is enough that the planner decides
> it is faster to do a sequential scan that the busier index scan
> (read index, get data row, versus just reading all the necessary
> pages in one faster sequential scan).
[...]
> QUERY PLAN
> ---------------------------------------------------------------------
> Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110)
> Filter: (thing_event_id = 10000)
> (2 rows)
The 5842.48 figure isn't a row count, it's a cost estimate that
includes the number of pages and rows in the file. The row count
estimate is 1 row; if the cost estimate for a sequential scan is
5842.48 then an index scan to fetch one row would surely be faster.
If thing_event_id is a bigint and has an index then casting the
value to bigint should result in an index scan. Another way would
be to quote the value:
UPDATE xx_thing_event SET thing_color = 'foo'
WHERE thing_event_id = '10000';
--
Michael Fuhr
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-03-04 19:07 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-03 07:27 ` Bryce Nesbitt <bryce1@obviously.com>
2006-03-04 19:07 ` Nikolay Samokhvalov <samokhvalov@gmail.com>
2006-03-03 07:28 ` Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
2006-03-03 09:31 ` Re: Sequential scan where Index scan expected (update) Ragnar <gnari@hive.is>
2006-03-03 12:47 ` Re: Sequential scan where Index scan expected (update) Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-03 19:35 ` Re: Sequential scan where Index scan expected (update) Bryce Nesbitt <bryce1@obviously.com>
2006-03-03 19:40 ` Re: Sequential scan where Index scan expected (update) Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-03 07:37 Re: Sequential scan where Index scan expected (update) Gregory S. Williamson <gsw@globexplorer.com>
2006-03-03 09:46 ` Re: Sequential scan where Index scan expected (update) Michael Fuhr <mike@fuhr.org>
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