Searching postgres soruces (was: Re: [GENERAL] array in a store procedure in C)
On Tue, Feb 3, 2015 at 4:20 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 2/3/15 7:03 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: On Tuesday, February 03, 2015 3:58 AM, Jim Nasby wrote: Note that the recursive grep starts at the current directory, so make sure you're actually in the pgsql source code when you use it. cat ~/bin/pg_grep #!/bin/sh grep -r $* * | grep -iv TAGS: | grep -v 'Binary file' | grep -v '.deps/' By the way, why not add a cd into the pgsql source tree to the script? That way you can't forget it when using the script... Because I have multiple checkouts, and I don't always use the full tree to search. It's a lot faster to only search the include directory, for example. Check out sublime text 3 with the C improved enhancement. It's a fantastic editor and it can jump you to the definition. I could never get ctags based editors to an acceptable state and my previous editor, source insight, never worked well on linux (although it had a fully integrated C parser!). merlin -- 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] VACUUM FULL pg_largeobject without (much) downtime?
On Tue, Feb 3, 2015 at 3:12 PM, Bill Moran wmo...@potentialtech.com wrote: On Tue, 3 Feb 2015 14:48:17 -0500 Adam Hooper a...@adamhooper.com wrote: It's doable for us to VACUUM FULL and add a notice to our website saying, you can't upload files for the next two hours. Maybe that's a better idea? It's really going to depend on what options you have available. Keep in mind that users won't be able to read large objects either, so you'll need to disable whatever features of the site view the files as well. Whether that's easier or harder depends on how much work it would be to disable those features of the site. For the record, this is what we went with. To those looking to use large objects (who, um, are already searching for how to VACUUM FULL pg_largeobject), my advice: if we were to do it all over again, we'd have used a separate database per bucket. For instance, imagine you store uploaded files and processed data in pg_largeobject. Then some day you migrate the processed data elsewhere. If uploaded files went in one database and processed data went into the second, then the uploaded-files database's pg_largeobject table would remain slim, and you could simply DROP DATABASE on the other after all clients stopped using it. There wouldn't be any downtime. My take-away, though, is to avoid the pg_largeobject table whenever possible. You can move BYTEA data with zero downtime using pg_repack, but the same can't be said for large objects. Enjoy life, Adam -- Adam Hooper +1-613-986-3339 http://adamhooper.com -- 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] How do I bump a row to the front of sort efficiently
I imagine your original would be at risk of LIMITing out the very row you seek to get at the top, since you don't have an ORDER BY to tell it which ones to keep during the outer LIMIT. Here is an old thread about combining ORDER BY with UNION: http://www.postgresql.org/message-id/16814.1280268...@sss.pgh.pa.us So I think this query would work: select * from topic where id = 1000 union all (select * from topic where id 1000 order by bumped_at desc limit 29) order by case when id = 1000 then 0 else 1 end, bumped_at desc ; I need to be able to offset and limit the union hack in a view, which is proving very tricky. Since this is sort of a parameterized view (which Postgres does not have) you are probably better off figuring out how to make the UNION query work with your ORM. What ORM is it? Maybe someone here can help you with that. Or maybe instead of a view you could write a set-returning function, e.g. as described here: http://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view Paul -- _ Pulchritudo splendor veritatis. -- 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] How do I bump a row to the front of sort efficiently
The union hack may be able to work, but what I want is slightly more complex
I want this to work efficiently, even without the case it chokes:
explain select * from testing order by id in (100,2,-1) desc, id limit 30;
QUERY PLAN
---
Limit (cost=4869.45..4869.52 rows=30 width=9)
- Sort (cost=4869.45..5119.45 rows=10 width=9)
Sort Key: ((id = ANY ('{100,2,-1}'::integer[]))), id
- Seq Scan on testing (cost=0.00..1916.00 rows=10 width=9)
(4 rows)
I need to be able to offset and limit the union hack in a view, which
is proving very tricky.
On Wed, Feb 4, 2015 at 9:15 PM, BladeOfLight16 bladeofligh...@gmail.com wrote:
On Tue, Feb 3, 2015 at 11:28 PM, Sam Saffron sam.saff...@gmail.com wrote:
Note: I still consider this a bug/missing feature of sorts since the
planner could do better here, and there is no real clean way of
structuring a query to perform efficiently here, which is why I
erroneously cross posted this to hacker initially:
No, it should not be considered a bug or a deficiency. You're telling the
system to use a computed value that uses an arbitrary logic construct for
sorting, and that's before you actually give it a filter to work with.
You're also trying to abuse ORDER BY to make LIMIT do filtering. How do you
expect that to go? I would expect the planner to do exactly what you told it
you wanted: sort the entire table by that computed value, and it would have
to do a sequential scan to compute the value for every row before it knew
which ones came first for the LIMIT.
CASE is well known to cause optimization problems; arbitrary conditional
logic isn't especially conducive to a planner optimizing things. In general,
the planner has no idea what the logic really means, so the planner would
have to have some kind of special logic trying to pick up on this case. Your
particular use case is uncommon; why should the planner code be junked up
with a thousand little optimizations for uncommon situations like this
(which would make it unmaintainable) when you already have a reasonable
alternative? PG is great for a reason: the devs have made a lot of fantastic
choices in designing it. Trying to keep the planner relatively simple is one
of them, if I recall correctly.
What you want is well represented by a UNION query: you want it to fetch one
particular row by ID, and then you want to tack on 29 other particular rows
based on a particular sort order and possibly an offset. These are two
completely disparate ways of fetching data; of course the most optimal way
is going to be to essentially write two queries and put the results
together. That's also going to be the clearest way of writing the query, so
the next person to work on it knows what you were doing.
And that aside, you don't even need to use CASE. You could've just used (id
= 1), which would give you a boolean result. You can most certainly sort by
a boolean. (I believe PG sorts TRUE first.) You should see if that gets
optimized at all. (If it doesn't, I won't be surprised and everything else I
said still holds.)
By the way, you can do better on your UNION query:
select * from topic
where id = 1000
union all
(select * from topic
where id 1000
order by bumped_at desc
limit 29)
I imagine your original would be at risk of LIMITing out the very row you
seek to get at the top, since you don't have an ORDER BY to tell it which
ones to keep during the outer LIMIT.
--
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] How do I bump a row to the front of sort efficiently
On Tue, Feb 3, 2015 at 11:28 PM, Sam Saffron sam.saff...@gmail.com wrote: Note: I still consider this a bug/missing feature of sorts since the planner could do better here, and there is no real clean way of structuring a query to perform efficiently here, which is why I erroneously cross posted this to hacker initially: No, it should not be considered a bug or a deficiency. You're telling the system to use a *computed value* that uses an *arbitrary logic construct *for *sorting*, and that's *before* you actually give it a filter to work with. You're also trying to abuse ORDER BY to make LIMIT do filtering. How do you expect that to go? I would expect the planner to do exactly what you told it you wanted: sort the entire table by that computed value, and it would have to do a sequential scan to compute the value for every row before it knew which ones came first for the LIMIT. CASE is well known to cause optimization problems; arbitrary conditional logic isn't especially conducive to a planner optimizing things. In general, the planner has *no* idea what the logic really means, so the planner would have to have some kind of special logic trying to pick up on this case. Your particular use case is uncommon; why should the planner code be junked up with a thousand little optimizations for uncommon situations like this (which would make it unmaintainable) when you already have a reasonable alternative? PG is great for a reason: the devs have made a lot of fantastic choices in designing it. Trying to keep the planner relatively simple is one of them, if I recall correctly. What you want is well represented by a UNION query: you want it to fetch one particular row by ID, and then you want to tack on 29 other particular rows based on a particular sort order and possibly an offset. These are two completely disparate ways of fetching data; of course the most optimal way is going to be to essentially write two queries and put the results together. That's also going to be the *clearest* way of writing the query, so the next person to work on it knows what you were doing. And that aside, you don't even need to use CASE. You could've just used (id = 1), which would give you a boolean result. You can most certainly sort by a boolean. (I believe PG sorts TRUE first.) You should see if that gets optimized at all. (If it doesn't, I won't be surprised and everything else I said still holds.) By the way, you can do better on your UNION query: select * from topic where id = 1000 union all (select * from topic where id 1000 order by bumped_at desc limit 29) I imagine your original would be at risk of LIMITing out the very row you seek to get at the top, since you don't have an ORDER BY to tell it which ones to keep during the outer LIMIT.
