Re: [PERFORM] Stuck using Sequential Scan
On Tue, 2004-09-07 at 22:32, Jeremy M. Guthrie wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I have a problem where I have the table format listed below. I have the > primary key tsyslog_id and the index built against it. However, when I > select a unique row, it will only ever do a seq scan even after I turn off > all other types except indexscan. I understand you cannot fully turn off seq > scan. ... > I cannot run vacuum more than once a day because of its heavy IO penalty. I > run analyze once an hour. However, if I run analyze then explain, I see no > difference in the planners decisions. What am I missing? > > > TSyslog=# \d syslog_tarchive; > Table "public.syslog_tarchive" >Column | Type | > Modifiers > - > ++- > tsyslog_id | bigint | not null default ... > > TSyslog=# explain select * from tsyslog where tsyslog_id=431650835; That constant is INTEGER, whereas the column is BIGINT; there is no automatic conversion in this case, so the planner does not realise the index is usable for this query (I think 8.0 solves this). Try: select * from tsyslog where tsyslog_id=431650835::BIGINT; -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me." Galatians 2:20 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?
Mischa Sandberg wrote: Coming from the MSSQL world, I'm used to the first step in optimization to be, choose your clustered index and choose it well. I see that PG has a one-shot CLUSTER command, but doesn't support continuously-updated clustered indexes. What I infer from newsgroup browsing is, such an index is impossible, given the MVCC versioning of records (happy to learn I'm wrong). I'd be curious to know what other people, who've crossed this same bridge from MSSQL or Oracle or Sybase to PG, have devised, faced with the same kind of desired performance gain for retrieving blocks of rows with the same partial key. Just to let people know, after trying various options, this looks the most promising: - segment the original table into four tables (call them A,B,C,D) - all insertions go into A. - longterm data lives in B. - primary keys of all requests to delete rows from (B) go into D -- no actual deletions are done against B. Deletions against A happen as normal. - all queries are made against a view: a union of A and B and (not exists) D. - daily merge A,B and (where not exists...) D, into C - run cluster on C, then swap names on B and C, truncate A and D. Not rocket science, but it seems to give the payback of normal clustering without locking the table for long periods of time. It also saves on VACUUM FULL time. At present, we're only at 1M rows in B on this. More when I know it. Advance warning on any gotchas with this approach would be much appreciated. Making a complete copy of (B) is a bit of an ouch. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Problem with large query
by the way, this reminds me: I just ran a performance study at a company doing an oracle-to-postgres conversion, and FYI converting from numeric and decimal to integer/bigint/real saved roughly 3x on space and 2x on performance. Obviously, YMMV. adam Tom Lane wrote: Marc Cousin <[EMAIL PROTECTED]> writes: I'm having trouble with a (quite big) query, and can't find a way to make it faster. Seems like it might help if the thing could use a HashAggregate instead of sort/group. Numeric is not hashable, so having those TO_NUMBER constants in GROUP BY destroys this option instantly ... but why in the world are you grouping by constants anyway? You didn't say what the datatypes of the other columns were... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Stuck using Sequential Scan
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I have a problem where I have the table format listed below. I have the
primary key tsyslog_id and the index built against it. However, when I
select a unique row, it will only ever do a seq scan even after I turn off
all other types except indexscan. I understand you cannot fully turn off seq
scan.
Syslog_TArchive size: 1,426,472,960 bytes
syslog_tarchive_pkey size: 132,833,280 bytes
archhost_idx size: 300,802,048 bytes
tarchdatetime_idx size: 159,293,440 bytes
tarchhostid_idx size: 362,323,968 bytes
I cannot run vacuum more than once a day because of its heavy IO penalty. I
run analyze once an hour. However, if I run analyze then explain, I see no
difference in the planners decisions. What am I missing?
TSyslog=# \d syslog_tarchive;
Table "public.syslog_tarchive"
Column | Type |
Modifiers
-
++-
tsyslog_id | bigint | not null default
nextval('public.syslog_tarchive_tsyslog_id_seq'::text)
facility | integer|
severity | integer|
date | date |
time | time without time zone |
host | character varying(128) |
message| text |
Indexes:
"syslog_tarchive_pkey" primary key, btree (tsyslog_id)
"archhost_idx" btree (host)
"tarchdatetime_idx" btree (date, "time")
"tarchhostid_idx" btree (tsyslog_id, host)
TSyslog=# explain select * from tsyslog where tsyslog_id=431650835;
QUERY PLAN
- -
Seq Scan on tsyslog (cost=1.00..10058.20 rows=2 width=187)
Filter: (tsyslog_id = 431650835)
(2 rows)
- --
- --
Jeremy M. Guthrie[EMAIL PROTECTED]
Senior Network EngineerPhone: 608-298-1061
Berbee Fax: 608-288-3007
5520 Research Park Drive NOC: 608-298-1102
Madison, WI 53711
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFBPijTqtjaBHGZBeURAndgAJ4rT2NpG9aGAdogoZaV+BvUfF6TjACfaexf
LrBzhDQK72u8dCUuPOSHB+Y=
=DSxi
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Interesting performance behaviour
Joey Smith <[EMAIL PROTECTED]> writes: > EXPLAIN ANALYZE SELECT message_idnr FROM messages WHERE mailbox_idnr > = 1746::bigint AND status<2::smallint AND seen_flag = 0 AND unique_id > != '' ORDER BY message_idnr ASC LIMIT 1; > QUERY PLAN > --- > Limit (cost=0.00..848.36 rows=1 width=8) (actual > time=1173.949..1173.953 rows=1 loops=1) >-> Index Scan using messages_pkey on messages > (cost=0.00..367338.15 rows=433 width=8) (actual > time=1173.939..1173.939 rows=1 loops=1) > Filter: ((mailbox_idnr = 1746::bigint) AND (status < > 2::smallint) AND (seen_flag = 0) AND ((unique_id)::text <> ''::text)) > Total runtime: 1174.012 ms The planner is correctly estimating that this plan is very expensive overall --- but it is guessing that the indexscan will only need to be run 1/433'd of the way to completion before the single required row is found. So that makes it look like a slightly better bet than the more conventional indexscan-on-mailbox_idnr-and-then-sort plan. If you ask for a few more than one row, though, it stops looking like a good bet, since each additional row is estimated to cost another 1/433'd of the total cost. Part of the estimation error is that there are only 56 matching rows not 433, so the real cost-per-row ought to be 1/56'th of the total indexscan cost. I suspect also that there is some correlation between message_idnr and mailbox_idnr, which results in having to scan much more than the expected 1/56'th of the index before finding a matching row. The planner has no stats about intercolumn correlation so it's not going to be able to recognize the correlation risk, but if you could get the rowcount estimate closer to reality that would be enough to tilt the scales to the better plan. Increasing ANALYZE's stats target for mailbox_idnr would be worth trying. Also, I suspect that there is a strong correlation between seen_flag and status, no? This again is something you can't expect the planner to realize directly, but you might be able to finesse the problem (and save some storage as well) if you could merge the seen_flag into the status column and do just one comparison to cover both conditions. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] effective_cache_size in 7.3.4?
Otis, > I saw a few mentions of 'effective_cache_size' parameter. Is this a > new PG 7.4 option? I have PG 7.3.4 and didn't see that parameter in my > postgresql.conf. Nope. AFAIK, it's been around since 7.0.Maybe you accidentally cut it out of your postgresql.conf? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Fwd: [PERFORM] Interesting performance behaviour
Accidentally sent directly to Josh. -- Forwarded message -- From: Joey Smith <[EMAIL PROTECTED]> Date: Fri, 10 Sep 2004 15:57:49 -0600 Subject: Re: [PERFORM] Interesting performance behaviour To: [EMAIL PROTECTED] > > I see a similar speedup (and change in query plan) using "LIMIT 1 > > OFFSET ". > > So what's your problem? The problem is that "LIMIT 1 OFFSET 0" has such poor performance. I'm not so much worried about the query time (it's still low enough to be acceptable), but the fact that it behaves oddly raised the question of whether this was correct behaviour or not. I'll try it with a saner value for effective_cache_size. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Interesting performance behaviour
> > shared_buffers = 1000 > > sort_mem = 1024 > > effective_cache_size = 1000 > > effective_cache_size should be much higher, like 3/4 of your available RAM. > This is probably the essence of your planner problem; the planner thinks you > have no RAM. I set effective_cache_size to 64000 on a machine with 2GB of physical RAM, and the behaviour is exactly the same. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] effective_cache_size in 7.3.4?
Hello, I saw a few mentions of 'effective_cache_size' parameter. Is this a new PG 7.4 option? I have PG 7.3.4 and didn't see that parameter in my postgresql.conf. Thanks, Otis ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Interesting performance behaviour
Joey, > shared_buffers = 1000 > sort_mem = 1024 > effective_cache_size = 1000 effective_cache_size should be much higher, like 3/4 of your available RAM. This is probably the essence of your planner problem; the planner thinks you have no RAM. > I see a similar speedup (and change in query plan) using "LIMIT 1 > OFFSET ". So what's your problem? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Interesting performance behaviour
#postgresql on Freenode recommended I post this here.
I'm seeing some odd behaviour with LIMIT. The query plans are included
here, as are the applicable table and index definitions. All table,
index, and query information can be found in a standard dbmail 1.2.6
install, if anyone wants to try setting up an exactly similar system.
Version: PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC
i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-3)
OS: Debian Linux, "unstable" tree
Some settings that I was told to include (as far as I am aware, these
are debian default values):
shared_buffers = 1000
sort_mem = 1024
effective_cache_size = 1000
Table/index definitions:
Table "public.messages"
Column | Type | Modifiers
---++
message_idnr | bigint | not null default
nextval('message_idnr_seq'::text)
mailbox_idnr | bigint | not null default 0
messagesize | bigint | not null default 0
seen_flag | smallint | not null default 0
answered_flag | smallint | not null default 0
deleted_flag | smallint | not null default 0
flagged_flag | smallint | not null default 0
recent_flag | smallint | not null default 0
draft_flag| smallint | not null default 0
unique_id | character varying(70) | not null
internal_date | timestamp(6) without time zone |
status| smallint | not null default 0
rfcsize | bigint | not null default 0
queue_id | character varying(40) | not null default
''::character varying
Indexes:
"messages_pkey" primary key, btree (message_idnr)
"idx_mailbox_idnr_queue_id" btree (mailbox_idnr, queue_id)
Foreign-key constraints:
"ref141" FOREIGN KEY (mailbox_idnr) REFERENCES
mailboxes(mailbox_idnr) ON UPDATE CASCADE ON DELETE CASCADE
EXPLAIN ANALYZE results:
EXPLAIN ANALYZE SELECT message_idnr FROM messages WHERE mailbox_idnr
= 1746::bigint AND status<2::smallint AND seen_flag = 0 AND unique_id
!= '' ORDER BY message_idnr ASC LIMIT 1;
QUERY PLAN
---
Limit (cost=0.00..848.36 rows=1 width=8) (actual
time=1173.949..1173.953 rows=1 loops=1)
-> Index Scan using messages_pkey on messages
(cost=0.00..367338.15 rows=433 width=8) (actual
time=1173.939..1173.939 rows=1 loops=1)
Filter: ((mailbox_idnr = 1746::bigint) AND (status <
2::smallint) AND (seen_flag = 0) AND ((unique_id)::text <> ''::text))
Total runtime: 1174.012 ms
EXPLAIN ANALYZE SELECT message_idnr FROM messages WHERE mailbox_idnr =
1746::bigint AND status<2::smallint AND seen_flag = 0 AND unique_id !=
'' ORDER BY message_idnr ASC ;
QUERY PLAN
Sort (cost=2975.42..2976.50 rows=433 width=8) (actual
time=2.357..2.545 rows=56 loops=1)
Sort Key: message_idnr
-> Index Scan using idx_mailbox_idnr_queue_id on messages
(cost=0.00..2956.46 rows=433 width=8) (actual time=0.212..2.124
rows=56 loops=1)
Index Cond: (mailbox_idnr = 1746::bigint)
Filter: ((status < 2::smallint) AND (seen_flag = 0) AND
((unique_id)::text <> ''::text))
Total runtime: 2.798 ms
I see a similar speedup (and change in query plan) using "LIMIT 1
OFFSET ".
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Question on Byte Sizes
Hello, * I need information on the size of pg ARRAY[]'s : I did not find any info in the Docs on this. How many bytes does an array take on disk ? Is there a difference between an array of fixed size elements like integers, and an array of variable length elements like text ? is there a pointer table ? Or are the elements packed together ? Is there any advantage in using a smallint[] over an integer[] regarding size ? Does a smallint[] with 2 elements really take 12 bytes ? * On Alignment : The docs say fields are aligned on 4-bytes boundaries. Does this mean that several consecutive smallint fields will take 4 bytes each ? What about seleral consecutive "char" fields ? 4 bytes each too ? I ask this because I'll have a lot of columns with small values to store in a table, and would like it to be small and to fit in the cache. Thanks for any info. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
