Re: [PERFORM] Seqscan/Indexscan still a known issue?
On 27.01.2007, at 00:35, Russell Smith wrote: Guess 1 would be that your primary key is int8, but can't be certain that is what's causing the problem. Why could that be a problem? cug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Seqscan/Indexscan still a known issue?
> > Hi, > > I find various references in the list to this issue of queries > being too slow because the planner miscalculates things and > decides to go for a sequenctial scan when an index is available > and would lead to better performance. > > Is this still an issue with the latest version? I'm doing some > tests right now, but I have version 7.4 (and not sure when I will > be able to spend the effort to move our system to 8.2). > > When I force it via "set enable_seqscan to off", the index scan > takes about 0.1 msec (as reported by explain analyze), whereas > with the default, it chooses a seq. scan, for a total execution > time around 10 msec!! (yes: 100 times slower!). The table has > 20 thousand records, and the WHERE part of the query uses one > field that is part of the primary key (as in, the primary key > is the combination of field1,field2, and the query involves a > where field1=1 and some_other_field=2). I don't think I'm doing > something "wrong", and I find no reason not to expect the query > planner to choose an index scan. 1) I'm missing a very important part - information about the settings in postgresql.conf, especially effective cache size, random page cost, etc. What hw are you using (RAM size, disk speed etc.)? 2) Another thing I'm missing is enough information about the table and the query itself. What is the execution plan used? Was the table modified / vacuumed / analyzed recently? Without these information it's completely possible the postgresql is using invalid values and thus generating suboptimal execution plan. There are many cases when the sequential scan is better (faster, does less I/O etc.) than the index scan. For example if the table has grown and was not analyzed recently, the postgresql may still believe it's small and thus uses the sequential scan. Or maybe the effective case size is set improperly (too low in this case) thus the postgresql thinks just a small fraction of data is cached, which means a lot of scattered reads in case of the index - that's slower than sequential reads. There are many such cases - the common belief that index scan is always better than the sequential scan is incorrect. But most of these cases can be identified using explain analyze output (which is missing in your post). The data supplied by you are not a 'proof' the index scan is better than sequential scan in this case, as the data might be cached due to previous queries. The port to 8.x might help, as some of the settings in postgresql.conf use different default values and the stats used by the planner might be 'freshier' than those in the current database. My recommendation: 1) send us the execution plan, that is use the EXPLAIN ANALYZE and send us the output 2) try to use ANALYZE on the table and run the queries again 3) review the settings in postgresql - a nice starting point is here http://www.powerpostgresql.com/PerfList (Yes, it's for Pg 8.0 but the basics are the same). Tomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Seqscan/Indexscan still a known issue?
Guido Neitzer wrote: On 27.01.2007, at 00:35, Russell Smith wrote: Guess 1 would be that your primary key is int8, but can't be certain that is what's causing the problem. Why could that be a problem? Before 8.0, the planner would not choose an index scan if the types were different int8_col = const, int8_col = 4. 4 in this example is cast to int4. int8 != int4. So the planner will not choose an index scan. Regards Russell Smith cug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Seqscan/Indexscan still a known issue?
On Sat, 2007-01-27 at 21:44 +1100, Russell Smith wrote: > Guido Neitzer wrote: > > On 27.01.2007, at 00:35, Russell Smith wrote: > > > >> Guess 1 would be that your primary key is int8, but can't be certain > >> that is what's causing the problem. > > > > Why could that be a problem? > Before 8.0, the planner would not choose an index scan if the types were > different int8_col = const, int8_col = 4. > 4 in this example is cast to int4. int8 != int4. So the planner will > not choose an index scan. But, in 7.4 setting enable_seqscan off would not make it use that index. For the OP, the problem is likely either that the stats for the column are off, effective_cache_size is set too low, and / or random_page_cost is too high. there are other possibilities as well. FYI, I upgraded the server we use at work to scan a statistical db of our production performance, and the queries we run there, which take anywhere from a few seconds to 20-30 minutes, run much faster. About an hour after the upgrade I had a user ask what I'd done to the db to make it so much faster. The upgrade was 7.4 to 8.1 btw... still testing 8.2, and it looks very good. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Seqscan/Indexscan still a known issue?
Tomas Vondra wrote: When I force it via "set enable_seqscan to off", the index scan takes about 0.1 msec (as reported by explain analyze), whereas with the default, it chooses a seq. scan, for a total execution time around 10 msec!! (yes: 100 times slower!). The table has 20 thousand records, and the WHERE part of the query uses one field that is part of the primary key (as in, the primary key is the combination of field1,field2, and the query involves a where field1=1 and some_other_field=2). I don't think I'm doing something "wrong", and I find no reason not to expect the query planner to choose an index scan. 1) I'm missing a very important part - information about the settings in postgresql.conf, especially effective cache size, random page cost, etc. What hw are you using (RAM size, disk speed etc.)? show all; responds with (I'm leaving only the ones I think could be the relevant ones): client_encoding| SQL_ASCII commit_delay | 0 commit_siblings| 5 cpu_index_tuple_cost | 0.001 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 deadlock_timeout | 1000 effective_cache_size | 1000 enable_hashagg | on enable_hashjoin| on enable_indexscan | on enable_mergejoin | on enable_nestloop| on enable_seqscan | on enable_sort| on enable_tidscan | on from_collapse_limit| 8 fsync | on geqo | on geqo_effort| 1 geqo_generations | 0 geqo_pool_size | 0 geqo_selection_bias| 2 geqo_threshold | 11 join_collapse_limit| 8 max_connections| 100 max_expr_depth | 1 max_files_per_process | 1000 max_fsm_pages | 2 max_fsm_relations | 1000 max_locks_per_transaction | 64 pre_auth_delay | 0 random_page_cost | 4 regex_flavor | advanced server_encoding| SQL_ASCII server_version | 7.4.5 shared_buffers | 62000 sort_mem | 1024 statement_timeout | 0 vacuum_mem | 8192 virtual_host | unset wal_buffers| 8 wal_debug | 0 wal_sync_method| fdatasync Any obvious red flag on these? The HW/SW is: Fedora Core 2 running on a P4 3GHz HT, with 1GB of RAM and 120GB SATA drive. 2) Another thing I'm missing is enough information about the table and the query itself. What is the execution plan used? Was the table modified / vacuumed / analyzed recently? I vacuum analyze the entire DB daily, via a cron entry (at 4AM). But I think the problem is that this particular table had not been vacuum analyzed after having inserted the 2 records (the query planner was giving me seq. scan when the table had about a dozen records --- and seq. scan was, indeed, 10 times faster; as a test, to make sure that the query planner would do the right thing when the amount of records was high, I inserted 2 records, and tried again --- now the seq. scan was 100 times slower, but it was still chosen (at that point was that I did a search through the archives and then posted the question). But now, after reading the replies, I did a vacuum analyze for this table, and now the query planner is choosing the Index scan. Without these information it's completely possible the postgresql is using invalid values and thus generating suboptimal execution plan. There are many cases when the sequential scan is better (faster, does less I/O etc.) than the index scan. But as the tests yesterday revealed, this was not the case (explain analyze was reporting execution times showing index scan 100 times faster!) For example if the table has grown and was not analyzed recently Ok, now I'm quite sure that this is, indeed, the case (as you can see from my description above) postgresql may still believe it's small and thus uses the sequential scan. Or maybe the effective case size is set improperly (too low in this case) thus the postgresql thinks just a small fraction of data is cached, which means a lot of scattered reads in case of the index - that's slower than sequential reads. But these values are all defaults (I think I played with the shared buffers size, following some guidelines I read in the PostgreSQL documentation), which is why I felt that I was not doing something "wrong" which would be at fault for making the query planner do the wrong thing (well, nothing wrong in the query and the table definition --- there was indeed something wrong on my side). There are many such cases - the common belief that index scan is always better than the
Re: [PERFORM] Seqscan/Indexscan still a known issue?
Carlos Moreno <[EMAIL PROTECTED]> writes: > But I think the problem is that this particular table had not been > vacuum analyzed after having inserted the 2 records (the > query planner was giving me seq. scan when the table had about > a dozen records --- and seq. scan was, indeed, 10 times faster; > as a test, to make sure that the query planner would do the right > thing when the amount of records was high, I inserted 2 > records, and tried again --- now the seq. scan was 100 times > slower, but it was still chosen (at that point was that I did a > search through the archives and then posted the question). > But now, after reading the replies, I did a vacuum analyze for > this table, and now the query planner is choosing the Index > scan. One reason you might consider updating is that newer versions check the physical table size instead of unconditionally believing pg_class.relpages/reltuples. Thus, they're much less likely to get fooled when a table has grown substantially since it was last vacuumed or analyzed. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Seqscan/Indexscan still a known issue?
Tom Lane wrote: One reason you might consider updating is that newer versions check the physical table size instead of unconditionally believing pg_class.relpages/reltuples. Thus, they're much less likely to get fooled when a table has grown substantially since it was last vacuumed or analyzed. Sounds good. Obviously, there seem to be plenty of reasons to upgrade, as pointed out in several of the previous replies; I would not rank this one as one of the top reasons to upgrade, since every time I've encountered this issue (planner selecting seq. scan when I'm convinced it should choose an index scan), I can always get away with forcing it to use an index scan, even if it feels like the wrong solution. But still, I guess what you point out comes as part of an array of improvements that will contribute to much better performance anyway! I'm sure I've said it countless times, but it feels again like the right time to say it: thank you so much for all the help and all the effort the PG team has put in making this such a great product --- improvement after improvement! Thanks, Carlos -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] IN operator causes sequential scan (vs. multiple OR expressions)
PostgreSQL version: 8.2.1
OS: Windows Server 2003
I have a relatively simple query where the planner chooses a
sequential scan when using the IN operator but chooses an index scan
when using logically equivalent multiple OR expressions. Here is the
table structure and the two versions of the query:
CREATE TABLE pool_sample
(
id integer NOT NULL,
state character varying(25) NOT NULL,
not_pooled_reason character varying(25) NOT NULL,
"comment" character varying(255),
CONSTRAINT "pk_poolSample_id" PRIMARY KEY (id)
)
WITHOUT OIDS;
CREATE INDEX "idx_poolSample_state"
ON pool_sample
USING btree
(state);
The following query uses a sequential scan (i.e. filter) on the
"state" column and takes about 5 seconds to execute (up to ~45
seconds with an "empty" cache):
SELECT * FROM pool_sample ps
WHERE ps.state IN ('PENDING_REPOOL_REVIEW', 'READY_FOR_REPOOL');
This version of the query uses an index scan on "state" and takes
about 50 milliseconds:
SELECT * FROM pool_sample ps
WHERE ps.state = 'PENDING_REPOOL_REVIEW' OR ps.state =
'READY_FOR_REPOOL';
There are over 10 million rows in the pool_sample table and 518 rows
meet the given criteria. In the first query, the planner estimates
that nearly 10 million rows will be returned (i.e. almost all rows in
the table). In the second query, the planner estimates 6830 rows,
which seems close enough for the purposes of planning.
If I explicitly cast the state column to text, the IN query uses an
index scan and performs just as well as the multiple OR version:
SELECT * FROM pool_sample ps
WHERE ps.state::text IN ('PENDING_REPOOL_REVIEW', 'READY_FOR_REPOOL');
So it would appear that the planner automatically casts the state
column to text within an OR expression but does not perform the cast
in an IN expression.
Our SQL is generated from an O/R mapper, so it's non-trivial (or at
least undesirable) to hand tune every query like this with an
explicit type cast. The only option I've come up with is to define
the state column as text in the first place, thus avoiding the need
to cast. Would this work? Are there any other/better options?
Thanks,
-Ryan
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PERFORM] IN operator causes sequential scan (vs. multiple OR expressions)
Ryan Holmes <[EMAIL PROTECTED]> writes: > I have a relatively simple query where the planner chooses a > sequential scan when using the IN operator but chooses an index scan > when using logically equivalent multiple OR expressions. EXPLAIN ANALYZE for both, please? If you set enable_seqscan = off, does that force an indexscan, and if so what does EXPLAIN ANALYZE show in that case? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] IN operator causes sequential scan (vs. multiple OR expressions)
On Jan 27, 2007, at 3:53 PM, Tom Lane wrote:
Ryan Holmes <[EMAIL PROTECTED]> writes:
I have a relatively simple query where the planner chooses a
sequential scan when using the IN operator but chooses an index scan
when using logically equivalent multiple OR expressions.
EXPLAIN ANALYZE for both, please?
If you set enable_seqscan = off, does that force an indexscan, and
if so
what does EXPLAIN ANALYZE show in that case?
regards, tom lane
Wow, I didn't expect such a quick response -- thank you!
Note: I rebuilt the state column index and ran a VACUUM ANALYZE since
my original post, so the planner's "rows" estimate is now different
than the 6830 I mentioned. The planner estimate is actually *less*
accurate now, but still in the ballpark relatively speaking.
Here is the EXPLAIN ANALYZE for both queries with enable_seqscan = on :
EXPLAIN ANALYZE SELECT * FROM pool_sample ps
WHERE ps.state = 'PENDING_REPOOL_REVIEW' OR ps.state =
'READY_FOR_REPOOL';
Bitmap Heap Scan on pool_sample ps (cost=985.51..61397.50 rows=50022
width=539) (actual time=13.560..39.377 rows=518 loops=1)
Recheck Cond: (((state)::text = 'PENDING_REPOOL_REVIEW'::text) OR
((state)::text = 'READY_FOR_REPOOL'::text))
-> BitmapOr (cost=985.51..985.51 rows=50084 width=0) (actual
time=9.628..9.628 rows=0 loops=1)
-> Bitmap Index Scan on
"idx_poolSample_state" (cost=0.00..480.25 rows=25042 width=0)
(actual time=0.062..0.062 rows=4 loops=1)
Index Cond: ((state)::text =
'PENDING_REPOOL_REVIEW'::text)
-> Bitmap Index Scan on
"idx_poolSample_state" (cost=0.00..480.25 rows=25042 width=0)
(actual time=9.563..9.563 rows=514 loops=1)
Index Cond: ((state)::text = 'READY_FOR_REPOOL'::text)
Total runtime: 39.722 ms
EXPLAIN ANALYZE SELECT * FROM pool_sample ps
WHERE ps.state IN ('PENDING_REPOOL_REVIEW', 'READY_FOR_REPOOL');
Seq Scan on pool_sample ps (cost=0.00..331435.92 rows=9667461
width=539) (actual time=1060.472..47584.542 rows=518 loops=1)
Filter: ((state)::text = ANY
(('{PENDING_REPOOL_REVIEW,READY_FOR_REPOOL}'::character varying
[])::text[]))
Total runtime: 47584.698 ms
And now with enable_seqscan = off:
EXPLAIN ANALYZE SELECT * FROM pool_sample ps
WHERE ps.state = 'PENDING_REPOOL_REVIEW' OR ps.state =
'READY_FOR_REPOOL';
Bitmap Heap Scan on pool_sample ps (cost=985.51..61397.50 rows=50022
width=539) (actual time=0.324..0.601 rows=518 loops=1)
Recheck Cond: (((state)::text = 'PENDING_REPOOL_REVIEW'::text) OR
((state)::text = 'READY_FOR_REPOOL'::text))
-> BitmapOr (cost=985.51..985.51 rows=50084 width=0) (actual
time=0.287..0.287 rows=0 loops=1)
-> Bitmap Index Scan on
"idx_poolSample_state" (cost=0.00..480.25 rows=25042 width=0)
(actual time=0.109..0.109 rows=4 loops=1)
Index Cond: ((state)::text =
'PENDING_REPOOL_REVIEW'::text)
-> Bitmap Index Scan on
"idx_poolSample_state" (cost=0.00..480.25 rows=25042 width=0)
(actual time=0.176..0.176 rows=514 loops=1)
Index Cond: ((state)::text = 'READY_FOR_REPOOL'::text)
Total runtime: 0.779 ms
EXPLAIN ANALYZE SELECT * FROM pool_sample ps
WHERE ps.state IN ('PENDING_REPOOL_REVIEW', 'READY_FOR_REPOOL');
Bitmap Heap Scan on pool_sample ps (cost=150808.51..467822.04
rows=9667461 width=539) (actual time=0.159..0.296 rows=518 loops=1)
Recheck Cond: ((state)::text = ANY
(('{PENDING_REPOOL_REVIEW,READY_FOR_REPOOL}'::character varying
[])::text[]))
-> Bitmap Index Scan on
"idx_poolSample_state" (cost=0.00..148391.65 rows=9667461 width=0)
(actual time=0.148..0.148 rows=518 loops=1)
Index Cond: ((state)::text = ANY
(('{PENDING_REPOOL_REVIEW,READY_FOR_REPOOL}'::character varying
[])::text[]))
Total runtime: 0.445 ms
So, yes, disabling seqscan does force an index scan for the IN
version. My question now is, how do I get PostgreSQL to make the
"right" decision without disabling seqscan?
Here are the non-default resource usage and query tuning settings
from postgresql.conf:
shared_buffers = 512MB
work_mem = 6MB
maintenance_work_mem = 256MB
random_page_cost = 3.0
effective_cache_size = 1536MB
from_collapse_limit = 12
join_collapse_limit = 12
The server has 4GB RAM, 2 X 2.4GHz Opteron dual core procs, 5 x 15k
RPM disks in a RAID 5 array and runs Windows Server 2003 x64.
Thanks,
-Ryan
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] IN operator causes sequential scan (vs. multiple OR expressions)
Ryan Holmes <[EMAIL PROTECTED]> writes:
> So, yes, disabling seqscan does force an index scan for the IN
> version. My question now is, how do I get PostgreSQL to make the
> "right" decision without disabling seqscan?
I pinged you before because in a trivial test case I got
indexscans out of both text and varchar cases:
regression=# create table foo (f1 text unique, f2 varchar(25) unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f1_key" for
table "foo"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f2_key" for
table "foo"
CREATE TABLE
regression=# explain select * from foo where f1 in ('foo', 'bar');
QUERY PLAN
-
Bitmap Heap Scan on foo (cost=4.52..9.86 rows=2 width=61)
Recheck Cond: (f1 = ANY ('{foo,bar}'::text[]))
-> Bitmap Index Scan on foo_f1_key (cost=0.00..4.52 rows=2 width=0)
Index Cond: (f1 = ANY ('{foo,bar}'::text[]))
(4 rows)
regression=# explain select * from foo where f2 in ('foo', 'bar');
QUERY PLAN
-
Bitmap Heap Scan on foo (cost=6.59..17.27 rows=10 width=61)
Recheck Cond: ((f2)::text = ANY (('{foo,bar}'::character varying[])::text[]))
-> Bitmap Index Scan on foo_f2_key (cost=0.00..6.59 rows=10 width=0)
Index Cond: ((f2)::text = ANY (('{foo,bar}'::character
varying[])::text[]))
(4 rows)
But on closer inspection the second case is not doing the right thing:
notice the rowcount estimate is 10, whereas it should be only 2 because
of the unique index on f2. I poked into it and realized that in 8.2
scalararraysel() fails to deal with binary-compatible datatype cases,
instead falling back to a not-very-bright generic estimate.
I've committed a fix for 8.2.2, but in the meantime maybe you could
change your varchar column to text?
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] IN operator causes sequential scan (vs. multiple OR expressions)
On Jan 27, 2007, at 5:56 PM, Tom Lane wrote: I've committed a fix for 8.2.2, but in the meantime maybe you could change your varchar column to text? regards, tom lane Thank you for the help and the fix. We're just performance testing right now so minor data model changes are no problem. Thanks, -Ryan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
