Re: [GENERAL] eliminating records not in (select id ... so SLOW?
On Thu, 31 Jul 2008 21:37:39 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > I'm doing something like: > > delete from table1 where id not in (select id from table2). > > table1 contains ~1M record table2 contains ~ 600K record and id > > is unique. > That's going to pretty much suck unless you've got work_mem set > high enough to allow a "hashed subplan" plan --- which is likely > to require tens of MB for this case, I don't recall exactly what Thanks. > the per-row overhead is. Experiment until EXPLAIN tells you it'll > use a hashed subplan. explain delete from catalog_categoryitem where ItemID not in (select ItemID from catalog_items); Well I reached 3Gb of work_mem and still I got: "Seq Scan on catalog_categoryitem (cost=31747.84..4019284477.13 rows=475532 width=6)" " Filter: (NOT (subplan))" " SubPlan" "-> Materialize (cost=31747.84..38509.51 rows=676167 width=8)" " -> Seq Scan on catalog_items (cost=0.00..31071.67 rows=676167 width=8)" I've this too: alter table catalog_items cluster on catalog_items_pkey; should I drop it? This is just a dev box. I loaded the 2 tables with 2 not coherent set of data just to play with, before adding all the pk/fk I need. I could just truncate the tables and reload them from coherent sources. But what if I *really* had to execute that query? Any other magic I could play to speed it up? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Copy fails
Abraham, Danny wrote: > I am loading a huge file using C, STDIN Using C? Have you written a C program using libpq to load some data, which it reads from its stdin? Or do you mean COPY FROM STDIN ? Something else? Perhaps if you provided a clearer and more complete explanation of your problem you might get a more useful answer. > The program fails immediately on "canceling statement due to statement > timeout" Do you have a statement timeout set in postgresql.conf ? -- Craig Ringer -- 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] Cursor Error
Bob Pawley wrote:
> Is it allowed to declare a cursor in this manner??
>
> Declare
> procgraphic cursor for select p_id.p_id.process_id
> from p_id.p_id, processes_count
> where p_id.p_id.p_id_id = processes_count.p_id_id;
Using DECLARE instead of OPEN? Yes, but that won't somehow make a cursor
involving a join updatable. See:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html
DECLARE and OPEN do not have exactly the same meaning, as explained by
the above documentation. To use a cursor defined with DECLARE you must
use OPEN - see section 38.7.2.3 ("Opening a Bound Cursor") of the
documentation.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[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
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
>
Re: [GENERAL] Postgresql not using an index
Marc -- > 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 Perhaps jobid is not an int -- maybe a bigint ? In which case the types don't match and the index won't be used (newer versions might do ok but 7.4 won't IIRC). Perhaps we could see the table description from \d in the psql tool ? HTH, Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
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 false
:constvalue 4 [ -24 10 0 0 ]
}
)
}
)
Re: [GENERAL] Postgresql not using an index
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
Pavel Stehule
> 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
>>>:r
[GENERAL] function definition and "entity"
I've to refactor a bunch of functions. Some of them are called inside other functions. Most of them could be changed through create or replace so... calling function should still refer to the right newer function. But some had to be dropped because the change was in output param. Is there a way to automatically check if all references are to the correct existing function? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] eliminating records not in (select id ... so SLOW?
Ivan Sergio Borgonovo wrote: But what if I *really* had to execute that query? Any other magic I could play to speed it up? A trick that is sometimes spectacularly efficient is to rewrite the query to use an outer join instead of NOT IN. Try: DELETE FROM table1 WHERE id IN (SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL) Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] 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
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
)
Re: [GENERAL] Postgresql not using an index
2008/8/1 Marc Cuypers <[EMAIL PROTECTED]>:
> 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?
your statistics are absolutly out - planner expect 207K rows but
currently resault is one row - try to run ANALYZE statement or
increase your statistics
http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html
look ALTER TABLE SET STATISTICS
regards
Pavel
>
> --
> 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
>
Re: [GENERAL] pg crashing
Roberts, Jon wrote: >> Roberts, Jon wrote: Not having looked at the internals of db_link, I'd say it's > certainly possible that this is the reason for the failed restart. If db_link > is blocking something, the postmaster can't kill it off, and it'll > still >>> be sitting there holding a reference to the shared memory segment. That said, it shouldn't be the reason why it's crashing in the > first place - just the reason why it won't restart properly. >>> Is this a problem in Unix? We are about 1 - 2 weeks away from > moving >>> this database to Solaris. >> Not likely, but I'd test it anyway. If the issue is related to AV, > it's >> certainly fine - you won't be running AV on your Solaris. But more >> importantly, Unix has actual support for signals and not just the fake >> stuff we have on Win32, so it's likely that the postmaster will be >> capable of killing the child processes. >> > > Our AV program has been off for a while now and I haven't had a crash. > I think part of the problem is how we have PostgreSQL installed and how > eTrust is configured. We have the binaries installed on C:\program > files\PostgreSQL\8.3\ and the data is located on E:\PostgreSQL\data\. > We have eTrust excluding just E:\PostgreSQL\data\. > > I'm guessing the activity on the binaries causes some scanning which may > have slowed down the cleanup enough to cause the crash to happen. Yeah, that does seem like a reasonable explanation. Yet another reason not to use AV on your database server ;-) And if you absolutely have to, exclude the postgresql stuff. Since we do re-execute postgres.exe for every new connection, it's quite possible that the AV scanned it every single time, and it's a fairly large EXE... //Magnus -- 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] Clone a database to other machine
On Thu, July 31, 2008 10:07 am, Chris wrote: > Garg, Manjit wrote: > Check out slony (http://slony.info/) - it's a master->multiple slave > replication system and seems to work pretty well. You can also try SkyTools (http://pgfoundry.org/projects/skytools/) - it's far simpler to use and to manage (eg, when things go wrong [they do]). Regards Henry -- 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] eliminating records not in (select id ... so SLOW?
ok try this delete from catalog_categoryitem where not exists (select id from catalog_items where catalog_items.ItemID = catalog_categoryitem.ItemID); --- On Thu, 7/31/08, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > From: Ivan Sergio Borgonovo <[EMAIL PROTECTED]> > Subject: Re: [GENERAL] eliminating records not in (select id ... so SLOW? > To: > Cc: "PostgreSQL" > Date: Thursday, July 31, 2008, 11:01 PM > On Thu, 31 Jul 2008 14:59:29 -0700 (PDT) > Lennin Caro <[EMAIL PROTECTED]> wrote: > > > > The box is a 2x dual core Xeon (below 2GHz) with > 4Gb ram. > > > Default debian etch setup. > > > you recently run vacuum ? > > The tables are pretty stable. I think no more than 20 > records were > modified (update/insert/delete) during the whole history of > the 2 > tables. > > autovacuum is running regularly. > > The actual query running is: > > begin; > create index catalog_categoryitem_ItemsID_index on >catalog_categoryitem using btree (ItemID); > delete from catalog_categoryitem >where ItemID not in (select ItemID from catalog_items); > commit; > > That's what came back > Timing is on. > BEGIN > Time: 0.198 ms > CREATE INDEX > Time: 3987.991 ms > > The query is still running... > > As a reminder catalog_categoryitem should contain less than > 1M > record. > catalog_items should contain a bit more than 600K record > where > ItemID is unique (a pk actually). > PostgreSQL comes from the default install from Debian etch > (8.1.X). > It's configuration hasn't been modified. > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] use of column in COPY
what's the use of column in the COPY FROM syntax if I get: ERROR: extra data after last expected column I've read: http://bytes.com/forum/thread424089.html but then... is there any actual use? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] eliminating records not in (select id ... so SLOW?
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > Well I reached 3Gb of work_mem and still I got: > "Seq Scan on catalog_categoryitem (cost=31747.84..4019284477.13 > rows=475532 width=6)" > " Filter: (NOT (subplan))" > " SubPlan" > "-> Materialize (cost=31747.84..38509.51 rows=676167 width=8)" > " -> Seq Scan on catalog_items (cost=0.00..31071.67 > rows=676167 width=8)" Huh. The only way I can see for that to happen is if the datatypes involved aren't hashable. What's the datatypes of the two columns being compared, anyway? regards, tom lane -- 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] eliminating records not in (select id ... so SLOW?
On Fri, 01 Aug 2008 10:33:59 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > Well I reached 3Gb of work_mem and still I got: > > > "Seq Scan on catalog_categoryitem (cost=31747.84..4019284477.13 > > rows=475532 width=6)" > > " Filter: (NOT (subplan))" > > " SubPlan" > > "-> Materialize (cost=31747.84..38509.51 rows=676167 > > width=8)" " -> Seq Scan on catalog_items > > (cost=0.00..31071.67 rows=676167 width=8)" > > Huh. The only way I can see for that to happen is if the datatypes > involved aren't hashable. What's the datatypes of the two columns > being compared, anyway? That S in CS should mean sober! thanks to svn I'd say you're right... one column was int the other bigint. Among other things I was just fixing that kind of mistakes. If that could be the reason I'll report if things got better once I finish to normalise the DB. BTW does pg 8.3 save you from such kind of mistake being stricter with auto cast? Tom sorry for sending this just to your personal email. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] eliminating records not in (select id ... so SLOW?
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> Huh. The only way I can see for that to happen is if the datatypes >> involved aren't hashable. What's the datatypes of the two columns >> being compared, anyway? > thanks to svn I'd say you're right... one column was int the other > bigint. Ah. 8.3 can hash certain cross-type comparisons (including that one) but prior versions won't. regards, tom lane -- 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] use of column in COPY
On Fri, Aug 1, 2008 at 10:16 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > what's the use of column in the COPY FROM syntax if I get: > > ERROR: extra data after last expected column > > I've read: > http://bytes.com/forum/thread424089.html > > but then... is there any actual use? > I use COPY FROM extensively; the column specification allows for cases where the incoming file doesn't match the table's column ordering (or if the incoming file has *fewer* columns than the table). -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] savepoint problems
Hello, i have migrated from Maxdb to Postgresql recently and i am having a speed problem in large transactions over slow links because of autorollback on error postgresql feature, i create data in any tables with triggers in other tables and i do large inserts from the data created in this tables to any other postgresql servers (replication purposes), for this example maybe we can say 2 rows, i want do this in a transaction to make rollback on certain errors, but i use a fallback feature if a duplicated is found i relaunch the last insert data in a update to the existing row, so i have to set savepoint and release after the insert has been successful, so my traffic flow is anything like this. client server begin --> <- ok savepoint-> <- ok insert --> <- ok release savepoint---> <- ok insert --> <- error duplicated key update -> <- ok release savepoint---> <- ok 2 rows later commit -> <- ok obviously in a slow link this is slow as hell, i have posted this same email in spanish pgsql-es-ayuda where Alvaro Herrera has replied my with some solutions (thanks Alvaro for your great support in spanish mailing list!), mainly two: 1- create a function that uses EXCEPTION to save data traffic or the function like an upsert that can be located in the example 38-1 at http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html but this have the problem that i still have the savepoint overhead. 2- create a function that make a select locking the table before decide to do an insert or an update. Well i would like to know if every can help with any other idea or any notes on this problem? Other question i have it is how i could create a function without be sure the number of columns to insert/update. Thanks in advance. Best Regards, Miguel Angel. -- 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] savepoint problems
On Fri, Aug 01, 2008 at 06:30:36PM +0200, Linos wrote: > Hello, > i have migrated from Maxdb to Postgresql recently and i am having a > speed problem in large transactions over slow links because of > autorollback > on error postgresql feature, i create data in any tables with triggers in > other tables and i do large inserts from the data created in this tables to > any other postgresql servers (replication purposes), for this example maybe > we can say 2 rows, i want do this in a transaction to make rollback on > certain errors, but i use a fallback feature if a duplicated is found i > relaunch the last insert data in a update to the existing row, so i have to > set savepoint and release after the insert has been successful, so my > traffic flow is anything like this. If the goal is to reduce latency costs, the best way could be: 1. Use COPY to transfer all the data in one stream to the server into a temporary table. 2. Use an UPDATE and and INSERT to merge the table into the old one. SQL has a MERGE statement but postgresql doesn't support that, so you'll have to do it by hand. That would be a total of 5 round-trips, including transaction start/end. hope this helps, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] Savepoints and SELECT FOR UPDATE in 8.2
I have a client application that needs: SELECT a set of records from a table and lock them for potential updates. for each record make some updates to this record and some other records in other tables call some call a function that does some application logic that does not access the database if this function is successful commit the changes for this record release any locks on this record if the function fails rollback any changes for this record release any locks for this record It would not be too much of a problem if the locks for all the records were held until all these records were processed. It would probably not be too bad if all the changes were not committed until all the records were processed. It is important that all the records are processed even when some of iterations encounter errors. I was thinking of something like this: connect to DB BEGIN SELECT * FROM table_foo where foo_state = 'queued' FOR UPDATE; for each row do [ SAVEPOINT s; UPDATE foo_resource SET in_use = 1 WHERE ...; status = application_logic_code(foo_column1, foo_column2); IF status OK THEN ROLLBACK TO SAVEPOINT s; ELSE RELEASE SAVEPOINT s; ENDIF ] COMMIT; I found a caution in the documentation that says that SELECT FOR UPDATE and SAVEPOINTS is not implemented correctly in version 8.2: http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-FOR-U PDATE-SHARE Any suggestions? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GROUP BY hour
I have, what I imagine to be, a fairly simple question. I have a query that produces output for a line graph. Each row represents an interval on the graph. SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour FROM c_call WHERE start_time >= '2008-08-01 00:00:00' AND end_time <= '2008-08-01 23:59:59' GROUP BY hour This works great when there is data in each interval but when a given interval has no data the group is omitted. What is the best way to ensure that the result contains a row for each interval with the value field set to zero or null? The reporting tool is incapable of filling in the gaps. Thanks -- 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] GROUP BY hour
Nathan Thatcher wrote: I have, what I imagine to be, a fairly simple question. I have a query that produces output for a line graph. Each row represents an interval on the graph. SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour FROM c_call WHERE start_time >= '2008-08-01 00:00:00' AND end_time <= '2008-08-01 23:59:59' GROUP BY hour This works great when there is data in each interval but when a given interval has no data the group is omitted. What is the best way to ensure that the result contains a row for each interval with the value field set to zero or null? The reporting tool is incapable of filling in the gaps. Thanks Use generate_series as part of your query. You can get a listing of all the hours, which can be integrated with your other data in a variety of ways, using: select '2008-08-01 00:00:00'::timestamp+generate_series(0,23)*'1 hour'::interval as hour; hour - 2008-08-01 00:00:00 2008-08-01 01:00:00 ... 2008-08-01 23:00:00 -- 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] GROUP BY hour
Nathan Thatcher escreveu:
I have, what I imagine to be, a fairly simple question. I have a query
that produces output for a line graph. Each row represents an interval
on the graph.
SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
FROM c_call WHERE start_time >= '2008-08-01 00:00:00' AND end_time <=
'2008-08-01 23:59:59' GROUP BY hour
This works great when there is data in each interval but when a given
interval has no data the group is omitted. What is the best way to
ensure that the result contains a row for each interval with the value
field set to zero or null? The reporting tool is incapable of filling
in the gaps.
Try:
SELECT s.hour::int, coalesce(t.value,0) FROM generate_series(0,23) AS
s(hour)
LEFT OUTER JOIN
(SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
FROM c_call WHERE date_trunc('day',start_time) = '2008-08-01'
GROUP BY hour) AS t
ON s.hour = t.hour;
Osvaldo
--
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] GROUP BY hour
Brilliant! On Fri, Aug 1, 2008 at 12:18 PM, Steve Crawford <[EMAIL PROTECTED]> wrote: > Nathan Thatcher wrote: >> >> I have, what I imagine to be, a fairly simple question. I have a query >> that produces output for a line graph. Each row represents an interval >> on the graph. >> >> SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour >> FROM c_call WHERE start_time >= '2008-08-01 00:00:00' AND end_time <= >> '2008-08-01 23:59:59' GROUP BY hour >> >> >> This works great when there is data in each interval but when a given >> interval has no data the group is omitted. What is the best way to >> ensure that the result contains a row for each interval with the value >> field set to zero or null? The reporting tool is incapable of filling >> in the gaps. >> >> Thanks >> >> > > Use generate_series as part of your query. You can get a listing of all the > hours, which can be integrated with your other data in a variety of ways, > using: > > select '2008-08-01 00:00:00'::timestamp+generate_series(0,23)*'1 > hour'::interval as hour; > > hour > - > 2008-08-01 00:00:00 > 2008-08-01 01:00:00 > ... > 2008-08-01 23:00:00 > > -- 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] savepoint problems
Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > On Fri, Aug 01, 2008 at 06:30:36PM +0200, Linos wrote: >> i have migrated from Maxdb to Postgresql recently and i am having a >> speed problem in large transactions over slow links because of autorollback > If the goal is to reduce latency costs, the best way could be: > [ move it to the server side ] Or move the logic into a server-side function, if you prefer to stick with your existing procedural approach. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] non-WAL btree?
Guys, I was wondering if there is a btree indexing implementation that is not WAL-logged. I'm loading data in bulks, and index logging is an unnecessary overhead for me (easier to rebuild on crash). Thanks! best regards, Alex Vinogradovs -- 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] non-WAL btree?
On Fri, 01 Aug 2008 12:41:12 -0700 Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > Guys, > > I was wondering if there is a btree indexing implementation that > is not WAL-logged. I'm loading data in bulks, and index logging > is an unnecessary overhead for me (easier to rebuild on crash). Drop the index during load? > > Thanks! > > best regards, > Alex Vinogradovs > -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] non-WAL btree?
By loading in bulks, I mean I load some 40-50 thousand rows at once into a table that already has some millions. Index rebuild on that table after each 50k inserts will be even less efficient ;) Alex. On Fri, 2008-08-01 at 12:57 -0700, Joshua Drake wrote: > On Fri, 01 Aug 2008 12:41:12 -0700 > Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > > > Guys, > > > > I was wondering if there is a btree indexing implementation that > > is not WAL-logged. I'm loading data in bulks, and index logging > > is an unnecessary overhead for me (easier to rebuild on crash). > > Drop the index during load? > > > > > Thanks! > > > > best regards, > > Alex Vinogradovs > > > > -- 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] non-WAL btree?
On Fri, Aug 1, 2008 at 4:07 PM, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > By loading in bulks, I mean I load some 40-50 thousand > rows at once into a table that already has some millions. > Index rebuild on that table after each 50k inserts will > be even less efficient ;) How many indexes do you have on this...? I do this pretty regularly (actually, I do 4k batches with COPY, 4-10 concurrent batches every 10 seconds, for 2-3 days at a time) and, having testing dropping indices, nothing to do with the index has a particularly strong performance impact. That said, a significant increase in checkpoint segments was required to get good performance out of the above use case. If you haven't tried that, I'd say that's a good place to start. What makes you think it's specifically index WAL work, though? -- - David T. Wilson [EMAIL PROTECTED] -- 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] non-WAL btree?
On Fri, 01 Aug 2008 13:07:18 -0700 Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > By loading in bulks, I mean I load some 40-50 thousand > rows at once into a table that already has some millions. > Index rebuild on that table after each 50k inserts will > be even less efficient ;) Fair enough, to answer your question, "no". We don't have any non wal table tables or index types. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] non-WAL btree?
On Fri, Aug 1, 2008 at 4:16 PM, David Wilson <[EMAIL PROTECTED]> wrote: > On Fri, Aug 1, 2008 at 4:07 PM, Alex Vinogradovs > <[EMAIL PROTECTED]> wrote: >> By loading in bulks, I mean I load some 40-50 thousand >> rows at once into a table that already has some millions. >> Index rebuild on that table after each 50k inserts will >> be even less efficient ;) > > How many indexes do you have on this...? I do this pretty regularly > (actually, I do 4k batches with COPY, 4-10 concurrent batches every 10 > seconds, for 2-3 days at a time) and, having testing dropping indices, > nothing to do with the index has a particularly strong performance > impact. Sorry, as I hit send, I realized I should clarify this: I do my bulk loads with the indexes active; I don't rebuild them (as they're necessary during the batch calculations). Dropping the indexes and using test data didn't show a significant performance improvement over leaving the indexes enabled. -- - David T. Wilson [EMAIL PROTECTED] -- 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] non-WAL btree?
Isn't hash indexing implementation non-WAL ? Alex. On Fri, 2008-08-01 at 13:16 -0700, Joshua Drake wrote: > On Fri, 01 Aug 2008 13:07:18 -0700 > Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > > > By loading in bulks, I mean I load some 40-50 thousand > > rows at once into a table that already has some millions. > > Index rebuild on that table after each 50k inserts will > > be even less efficient ;) > > Fair enough, to answer your question, "no". We don't have any non wal > table tables or index types. > > Sincerely, > > Joshua D. Drake -- 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] non-WAL btree?
It's not that I expect a lot of improvement by having non-WAL indexing, it just sounds logical to me to have that, since index can be re-created fast enough during recovery, and it would reduce my IO to some extent. Alex. > Sorry, as I hit send, I realized I should clarify this: I do my bulk > loads with the indexes active; I don't rebuild them (as they're > necessary during the batch calculations). Dropping the indexes and > using test data didn't show a significant performance improvement over > leaving the indexes enabled. > -- 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] non-WAL btree?
On 4:36 pm 08/01/08 Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > It's not that I expect a lot of improvement by having non-WAL > indexing Have you tried using a fill factor less than 90%? That is on my list of things to test, but have not done yet.. In particular you need to find a balance where the speed gained justifies the increase in size of the index.. and the potential slowdowns because of larger indexes. -- 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] non-WAL btree?
On Fri, Aug 1, 2008 at 3:32 PM, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > Isn't hash indexing implementation non-WAL ? > yes, but that's because no one thinks is worth the effort of making them WAL logged while they keep slower than btree... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] non-WAL btree?
On Fri, Aug 1, 2008 at 3:36 PM, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > It's not that I expect a lot of improvement by having non-WAL > indexing, it just sounds logical to me to have that, since > index can be re-created fast enough during recovery, and why you think that? if they are non WAL logged the only way to re-create them after a recovery is with a REINDEX... dropping the index and create after the bulk is just the same, i think... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] non-WAL btree?
It's all about number of repetions. If say I load my table with 50k every minute, and run reindex every minute, how long do you think it would take by end of the day, when my table (it's daily partition actually) is at maximum capacity ? And database may actually never crash, and I won't have to run reindex at all ;) Btw, SELECT INTO is also a non-WAL operation when archiving is disabled, or am I missing something ? Alex. On Fri, 2008-08-01 at 16:43 -0500, Jaime Casanova wrote: > On Fri, Aug 1, 2008 at 3:36 PM, Alex Vinogradovs > <[EMAIL PROTECTED]> wrote: > > It's not that I expect a lot of improvement by having non-WAL > > indexing, it just sounds logical to me to have that, since > > index can be re-created fast enough during recovery, > > and why you think that? if they are non WAL logged the only way to > re-create them after a recovery is with a REINDEX... dropping the > index and create after the bulk is just the same, i think... > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there any reason why "edit PostgreSQL.conf should be on my menu"
Especially when I haven't edited anything yet? -- 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] savepoint problems
David Wilson escribió: On Fri, Aug 1, 2008 at 12:30 PM, Linos <[EMAIL PROTECTED]> wrote: Well i would like to know if every can help with any other idea or any notes on this problem? Other question i have it is how i could create a function without be sure the number of columns to insert/update. Thanks in advance. you could do: begin; create temporary table tmp (...); [insert (or better yet, COPY) into tmp table] [delete from real table where exists in temporary table]; insert into real_table select * from tmp; drop table tmp; commit; Your client <--> server communication should be extremely small. I think this is probably the better solution if i get the jdbc to use the copy command, but i still dont know how to make a function with a variable column number, maybe i simply can put all the columns and let the null columns insert/update with null. Regards, Miguel Angel. -- 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] savepoint problems
Tom Lane escribió: Martijn van Oosterhout <[EMAIL PROTECTED]> writes: On Fri, Aug 01, 2008 at 06:30:36PM +0200, Linos wrote: i have migrated from Maxdb to Postgresql recently and i am having a speed problem in large transactions over slow links because of autorollback If the goal is to reduce latency costs, the best way could be: [ move it to the server side ] Or move the logic into a server-side function, if you prefer to stick with your existing procedural approach. regards, tom lane when you say move the logic into a server-side function do you mean send the data in a copy command (or many inserts) to a temporary table and load from here with a server-side functions like David or Martijn or are you telling me other way to do it, could you elaborate this please? Thanks. Regards, Miguel Angel. -- 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] non-WAL btree?
Jaime Casanova wrote: > On Fri, Aug 1, 2008 at 3:36 PM, Alex Vinogradovs > <[EMAIL PROTECTED]> wrote: >> It's not that I expect a lot of improvement by having non-WAL >> indexing, it just sounds logical to me to have that, since >> index can be re-created fast enough during recovery, > > and why you think that? if they are non WAL logged the only way to > re-create them after a recovery is with a REINDEX... dropping the > index and create after the bulk is just the same, i think... They don't all have to be non-WAL, first off; it could be optional per index. Second, non-WAL would provide a benefit in the case the OP mentioned, and the only time it would be a detriment is in the event of a fault. Reindexing of non-WAL indexes could be automatic during recovery. Non-WAL indexing is an option I would almost certainly take advantage of if it existed. -Glen -- 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] [SQL] Savepoints and SELECT FOR UPDATE in 8.2
On Fri, Aug 1, 2008 at 11:02 AM, EXT-Rothermel, Peter M <[EMAIL PROTECTED]> wrote: > > I was thinking of something like this: > > connect to DB > > BEGIN > > SELECT * FROM table_foo where foo_state = 'queued' FOR UPDATE; > for each row > do [ > >SAVEPOINT s; >UPDATE foo_resource SET in_use = 1 WHERE ...; > >status = application_logic_code(foo_column1, foo_column2); > >IF status OK >THEN > ROLLBACK TO SAVEPOINT s; >ELSE > RELEASE SAVEPOINT s; >ENDIF > ] > > > COMMIT; > > I found a caution in the documentation that says that SELECT FOR UPDATE > and SAVEPOINTS is not implemented correctly in version 8.2: > > http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-FOR-U > PDATE-SHARE > > Any suggestions? Why not plain rollback? -- 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] hibernate nativequery and uuid
oops, forgot to cc the mailing list again... Andrew wrote: I know none of this relates directly to postgresql and on reflection is probably more appropriate for the hibernate forums. So apologies for having raised the topic here. Also, thanks for the suggestions that I have received on the topic. I have got the UNION working in a view via JPA. However, it was not straightforward and the approach that I have taken is not that efficient. So for those who are interested in some of the implementation details... Previously to get hibernate to use the postgresql uuid, I had to create a custom hibernate UserType as well as extend the JDBC postgresql driver. I then referenced the custom data type and a custom UUID generator that I had created using JPA/hibernate annotations in the entities. However, the hibernate validation of views appears to be different than with tables, so after creating the corresponding view entity, on restarting the application server, I was getting the "No Dialect mapping for JDBC type: " exception, despite the view entity being configured just like the table entities. So I had to change my META-INF/persistence.xml to reference my extended JDBC postgresql driver, rather than the original JDBC driver. For good measure, I also did the same to my hibernate-console.properties file as part of my eclipse environment. This addressed the exception. But then I was hit with a "javax.persistence.PersistenceException: org.hibernate.HibernateException: Missing table: my_view" exception, as there is an outstanding defect in hibernate related to how it treats views. See http://opensource.atlassian.com/projects/hibernate/browse/HHH-1329. Supposedly it was fixed in version 3.2.6, but I'm using version 3.2.6 GA and it is patently still an issue. So the only way around this at present is unfortunately to turn off validation with the hibernate.hbm2ddl.auto property in the META-INF/persistence.xml file. But after all of that, I can now use JPA entities in EJBQL's to use a view containing postgresql uuid's. Nothing like speaking gibberish with an overuse of acronyms :-) Cheers, Andy A.M. wrote: Could you use a view to hide the UNION? Cheers, M No virus found in this incoming message. Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database: 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why so many error when I load the data to database from a script which generated by pg_dump.
hi, all: I have a database to store the information about the html source of the web page. I wan't to move the data to another database, so I pg_dump the data to a file: /usr/local/pgsql/bin/pg_dump htmldb -Upostgres -p 5433 > /tmp/dump.sql now, I load the data into new database: /usr/local/pgsql/bin/psql -d newbd -Upostgres -p 5432 -f /tmp/dump.sql but, I got some error about the "column error", for example: psql:/export/chry.sql:1965134: ERROR: missing data for column "content" CONTEXT: COPY htmlcontent, line 312807: "1207327 \n\n\nhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove duplicate lines but save one of the lines?
A B wrote:
I have a table with rows like this
A 1
A 1
B 3
B 3
C 44
C 44
and so on.
and I want it to be
A 1
B 3
C 44
so how can I remove the all the duplicate lines but one?
Try with:
your table structure for example: create table yourtable(campo1 char,
num integer);
select * from yourtable;
sicodelico=# select * from yourtable ;
campo1 | num
+-
A | 1
A | 1
B | 3
B | 3
C | 44
C | 44
(6 filas)
sicodelico=#
1) create temp sequence foo_id_seq start with 1;
2) alter table yourtable add column id integer;
3) update yourtable set id = nextval('foo_id_seq');
look this:
sicodelico=# select * from yourtable ;
campo1 | num | id
+-+
A | 1 | 1
A | 1 | 2
B | 3 | 3
B | 3 | 4
C | 44 | 5
C | 44 | 6
(6 filas)
4) delete from yourtable where campo1 in (select y.campo1 from yourtable
y where yourtable.id > y.id);
sicodelico=# select * from yourtable;
campo1 | num | id
+-+
A | 1 | 1
B | 3 | 3
C | 44 | 5
(3 filas)
5) alter table yourtable drop column id;
sicodelico=# select * from yourtable;
campo1 | num
+-
A | 1
B | 3
C | 44
(3 filas)
have a lot of fun :)
--
Regards,
Julio Cesar Sánchez González.
--
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.
www.sistemasyconectividad.com.mxhttp://darkavngr.blogspot.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
