[GENERAL] Postgresql not using an index
Hi,
I'm using postgres 7.4 and bacula 1.38 on debian.
In the bacula database there is a table named 'file' which has about 2.5
million rows.
In this table there is a field 'jobid' which is indexed.
The index is created with the following command:
CREATE INDEX file_jobid_idx ON file USING btree (jobid);
The query:
SELECT * from file where jobid=2792
does a full scan and to my opinion it doesn't use the index.
I already did a VACUUM ANALYZE on the database.
Somebody an idea?
EXPLAIN tells the following:
Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=103215.145..161153.664 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 161154.734 ms
The Verbose Explain tells the following:
{SEQSCAN
:startup_cost 0.00
:total_cost 707683.30
:plan_rows 207562
:plan_width 110
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname fileid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname fileindex
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname jobid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname pathid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname filenameid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname markid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 7
:restype 25
:restypmod -1
:resname lstat
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 7
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 7
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 8
:restype 25
:restypmod -1
:resname md5
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 8
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 8
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
)
:qual (
{OPEXPR
:opno 96
:opfuncid 65
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ -24 10 0 0 ]
}
)
}
)
:lefttree <>
:righttree <>
:ini
Re: [GENERAL] Postgresql not using an index
Hi Pavel,
Isn't the text for the Verbose Explain analyze not enough?
Is not, how can i generate it?
--
Best regards,
Marc
Pavel Stehule schreef:
Hello
please, send EXPLAIN ANALYZE output.
regards
Pavel Stehule
2008/8/1 Marc Cuypers <[EMAIL PROTECTED]>:
Hi,
I'm using postgres 7.4 and bacula 1.38 on debian.
In the bacula database there is a table named 'file' which has about 2.5
million rows.
In this table there is a field 'jobid' which is indexed.
The index is created with the following command:
CREATE INDEX file_jobid_idx ON file USING btree (jobid);
The query:
SELECT * from file where jobid=2792
does a full scan and to my opinion it doesn't use the index.
I already did a VACUUM ANALYZE on the database.
Somebody an idea?
EXPLAIN tells the following:
Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=103215.145..161153.664 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 161154.734 ms
The Verbose Explain tells the following:
{SEQSCAN
:startup_cost 0.00
:total_cost 707683.30
:plan_rows 207562
:plan_width 110
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname fileid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname fileindex
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname jobid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname pathid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname filenameid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname markid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 7
:restype 25
:restypmod -1
:resname lstat
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 7
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 7
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 8
:restype 25
:restypmod -1
:resname md5
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 8
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 8
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
)
:qual (
{OPEXPR
:opno 96
:opfuncid 65
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull
Re: [GENERAL] Postgresql not using an index
Hi Pavel,
Pavel Stehule schreef:
2008/8/1 Marc Cuypers <[EMAIL PROTECTED]>:
Hi Pavel,
Isn't the text for the Verbose Explain analyze not enough?
Is not, how can i generate it?
--
no, I am missing statistics info
try
EXPLAIN ANALYZE SELECT .
regards
I entered the command in pgsql and got the following output:
# explain analyze select * from file where jobid=2792;
QUERY PLAN
--
Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=37738.780..90453.299 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 90453.419 ms
(3 rows)
Makes any sence?
--
Marc
Marc
Pavel Stehule schreef:
Hello
please, send EXPLAIN ANALYZE output.
regards
Pavel Stehule
2008/8/1 Marc Cuypers <[EMAIL PROTECTED]>:
Hi,
I'm using postgres 7.4 and bacula 1.38 on debian.
In the bacula database there is a table named 'file' which has about 2.5
million rows.
In this table there is a field 'jobid' which is indexed.
The index is created with the following command:
CREATE INDEX file_jobid_idx ON file USING btree (jobid);
The query:
SELECT * from file where jobid=2792
does a full scan and to my opinion it doesn't use the index.
I already did a VACUUM ANALYZE on the database.
Somebody an idea?
EXPLAIN tells the following:
Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=103215.145..161153.664 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 161154.734 ms
The Verbose Explain tells the following:
{SEQSCAN
:startup_cost 0.00
:total_cost 707683.30
:plan_rows 207562
:plan_width 110
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname fileid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname fileindex
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname jobid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname pathid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname filenameid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname markid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 7
:restype 25
:restypmod -1
:resname lstat
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 7
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 7
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 8
:restype 25
:restypmod -1
:resname md5
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 8
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 8
:vartype 25
:vartypmod -1
:varle
[GENERAL] upgrade from 7.4 to 8.3
Hi, I've just upgrade postgresql from 7.4 to 8.3 on a machine running debian. And i ran in trouble. I've searched the internet but didn't find an answer to my solution. Is it possible to upgrade from 7.4 to 8.3 at once? I upgraded as follows: when 7.4 was installed i dumped the data with: su postgres -c "pg_dumpall --create" > out when 8.3 was installed i imported the data with: su postgres -c "psql < out" Databases in 7.4 were encoded as utf-8. Now when importing postgresql gives the following error: ERROR: encoding UTF8 does not match server's locale en_US DETAIL: The server's LC_CTYPE setting requires encoding LATIN1. When creating a database from pgadmin (logged in as postgres) with: CREATE DATABASE "db" WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UNICODE' postgres replies with: ERROR: encoding UTF8 does not match server's locale en_US DETAIL: The server's LC_CTYPE setting requires encoding LATIN1. ** Error ** ERROR: encoding UTF8 does not match server's locale en_US SQL state: XX000 Detail: The server's LC_CTYPE setting requires encoding LATIN1. Can anybody tell me what's wrong? -- Best regards, Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] upgrade from 7.4 to 8.3
Thanks Tom, Tom Lane schreef: Marc Cuypers writes: Databases in 7.4 were encoded as utf-8. Now when importing postgresql gives the following error: ERROR: encoding UTF8 does not match server's locale en_US DETAIL: The server's LC_CTYPE setting requires encoding LATIN1. It looks like you need to run initdb with LANG set to en_US.utf8, not just en_US. I executed the command: su postgres -c "/usr/lib/postgresql/8.3/bin/initdb -E UNICODE --locale nl_BE.utf8 -D /home/pgsql/8" And now i can use utf-8. Only... One database was in LATIN9. When creating this database i got the same error. Command: CREATE DATABASE "hardsoft" WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'LATIN9'; Error: ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 SQL state: XX000 Detail: The server's LC_CTYPE setting requires encoding UTF8. Can i only use nl_BE and UTF-8 now? Why can't i use LATIN9 anymore? Is bacula 8.3 stricter in this respect to 7.4? -- best regards, Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SOLVED upgrade from 7.4 to 8.3
Thanks Clemens, Schwaighofer Clemens schreef: On Wed, Mar 11, 2009 at 19:01, Marc Cuypers wrote: Thanks Tom, Only... One database was in LATIN9. When creating this database i got the same error. Command: CREATE DATABASE "hardsoft" WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'LATIN9'; Error: ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 SQL state: XX000 Detail: The server's LC_CTYPE setting requires encoding UTF8. Can i only use nl_BE and UTF-8 now? Why can't i use LATIN9 anymore? Is bacula 8.3 stricter in this respect to 7.4? yes, you can no longer mix encodings in utf8. That has something to do with the sort (or so). Anyway, for the one DB that you have in Latin1, dump this extra, convert the whole file to utf8 with iconv, replace the Latin1 strings in it to UTF8 and your good to go. That's what i'll do. Thanks again. -- Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] upgrade from 7.4 to 8.3
Gregory Stark schreef: Marc Cuypers writes: Error: ERROR: encoding LATIN9 does not match server's locale nl_BE.utf8 SQL state: XX000 Detail: The server's LC_CTYPE setting requires encoding UTF8. Can i only use nl_BE and UTF-8 now? Why can't i use LATIN9 anymore? Is bacula 8.3 stricter in this respect to 7.4? 8.3 is stricter about checking that the configuration makes sense. But even under 7.4 you would have had problems, you just wouldn't have been forewarned so soon. You would still only be able to use nl_BE.utf8 collation but you would have been allowed to tell the server your data was encoded with latin9. So the collation results would have been nonsensical. Ie, comparisons like < and > would have given incorrect results. If this database is still under development and your schedule allows one option might be use 8.4dev from CVS. It should be released sometime in the next 3-6 months and will allow you to have a different encoding and locale for each database. I'd like to stay with the standard version of debian. So 8.4 is not an option. But i'll convert the database in 7.4 to UTF-8. So the problem will be solved. -- Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] compiling the examples
Hi, How do i make the examples in postgresql-7.3.3/src/test/examples/? It seems that just typing make doesn't link to the libpq library. Where should I start make? Just typing make gives the following output: # make gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/interfaces/libpq -I../../../src/include -Wl,-rpath,/usr/local/pgsql/lib testlibpq.c -o testlibpq /tmp/ccAN4Szw.o: In function `exit_nicely': /tmp/ccAN4Szw.o(.text+0xd): undefined reference to `PQfinish' /tmp/ccAN4Szw.o: In function `main': /tmp/ccAN4Szw.o(.text+0x3b): undefined reference to `PQsetdbLogin' /tmp/ccAN4Szw.o(.text+0x4a): undefined reference to `PQstatus' /tmp/ccAN4Szw.o(.text+0x75): undefined reference to `PQerrorMessage' /tmp/ccAN4Szw.o(.text+0xa9): undefined reference to `PQexec' /tmp/ccAN4Szw.o(.text+0xb4): undefined reference to `PQresultStatus' /tmp/ccAN4Szw.o(.text+0xd8): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0xf3): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0x104): undefined reference to `PQexec' /tmp/ccAN4Szw.o(.text+0x112): undefined reference to `PQresultStatus' /tmp/ccAN4Szw.o(.text+0x136): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0x151): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0x162): undefined reference to `PQexec' /tmp/ccAN4Szw.o(.text+0x170): undefined reference to `PQresultStatus' /tmp/ccAN4Szw.o(.text+0x194): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0x1af): undefined reference to `PQnfields' /tmp/ccAN4Szw.o(.text+0x1c9): undefined reference to `PQfname' /tmp/ccAN4Szw.o(.text+0x20c): undefined reference to `PQgetvalue' /tmp/ccAN4Szw.o(.text+0x23c): undefined reference to `PQntuples' /tmp/ccAN4Szw.o(.text+0x24c): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0x25d): undefined reference to `PQexec' /tmp/ccAN4Szw.o(.text+0x26b): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0x27c): undefined reference to `PQexec' /tmp/ccAN4Szw.o(.text+0x28a): undefined reference to `PQclear' /tmp/ccAN4Szw.o(.text+0x296): undefined reference to `PQfinish' collect2: ld returned 1 exit status make: *** [testlibpq] Error 1 -- Best regards, Marc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] compiling the examples
Tom Lane wrote: Marc Cuypers <[EMAIL PROTECTED]> writes: How do i make the examples in postgresql-7.3.3/src/test/examples/? "make" works for me, assuming that I'm doing it in a built directory tree. [ looks at 7.3 branch... ] Hm, it looks like there's a mistake in the Makefile in that directory in 7.3: try changing LIBS += $(libpq) to LDFLAGS += $(libpq) This works. Thanks a lot. -- Best regards, Marc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
